locked
T-SQL Query output RRS feed

  • Question

  • Hi,

    In the following query i want to see the prcessorcount output value is divided/2. Lets say here processor count is 80, where i want to see 40

    This is the query:--

     

    select
    case
    WHEN (ipaddress like '10.4.%' or ipaddress like '10.4.%') and appname like '%WL%' THEN 'DEV WEBLOGIC'
    WHEN appname like '%WL%' and ipaddress like '10.4.%' THEN 'PROD WEBLOGIC'
    WHEN ipaddress like '10.4.%' or ipaddress like '10.4.%' THEN 'DEV' 
    WHEN ipaddress like '10.1.%' or ipaddress like'10.4.%' THEN 'PROD'

    end as Project
    , o.hostname 
    , o.ipaddress
    , o.appname
    , o.processorcount
    , o.memorysize
    ,   o.is_virtual
    , i.instancename

    from oraclehosts o
    left join oracleinstances i on o.hostname = i.hostname
    left join hostwarranty w on w.hostname = o.hostname 
    Collate SQL_Latin1_General_CP1_CI_AS 
    left join puppetdb_certname_facts p 
    on p.certname = o.hostname where  
    p.fact = 'oracle_license' and p.fact = 'oracle_license' AND  p.value = 'true'

    group by 
    i.instanceName
    , o.hostname
    , o.ipaddress
    , o.processorcount
    , o.memorysize
    , o.is_virtual
    , i.is_ebs
    , o.appname

    Query output:--

        

    Project hostname ipaddress            appname processorcount memorysize is_virtual instancename
    PROD xxx    10.1.                   EBS PROJECT 80                  251.53 GB false         GOLDEBS         
    DEV         xyz         10.4.               EBS PROJECT 10                  125.57 GB false  LDRUAT     


    Zahid


    • Edited by Zahid2017 Wednesday, December 13, 2017 8:53 PM
    Wednesday, December 13, 2017 8:52 PM

Answers

  • select
    case
    WHEN (ipaddress like '10.4.%' or ipaddress like '10.4.%') and appname like '%WL%' THEN 'DEV WEBLOGIC'
    WHEN appname like '%WL%' and ipaddress like '10.4.%' THEN 'PROD WEBLOGIC'
    WHEN ipaddress like '10.4.%' or ipaddress like '10.4.%' THEN 'DEV' 
    WHEN ipaddress like '10.1.%' or ipaddress like'10.4.%' THEN 'PROD'

    end as Project
    , o.hostname 
    , o.ipaddress
    , o.appname
    , CASE o.is_virtual WHEN 'False' THEN o.processorcount/2 ELSE o.processorcount END AS processorcount
    , o.memorysize
    ,   o.is_virtual
    , i.instancename

    from oraclehosts o
    left join oracleinstances i on o.hostname = i.hostname
    left join hostwarranty w on w.hostname = o.hostname 
    Collate SQL_Latin1_General_CP1_CI_AS 
    left join puppetdb_certname_facts p 
    on p.certname = o.hostname where  
    p.fact = 'oracle_license' and p.fact = 'oracle_license' AND  p.value = 'true'

    group by 
    i.instanceName
    , o.hostname
    , o.ipaddress
    , o.processorcount
    , o.memorysize
    , o.is_virtual
    , i.is_ebs
    , o.appname

    Please mark as answered, If you feel happy with this answer.

    • Proposed as answer by Xi Jin Thursday, December 14, 2017 5:56 AM
    • Marked as answer by Zahid2017 Thursday, December 14, 2017 2:22 PM
    Wednesday, December 13, 2017 9:20 PM

All replies

  • Hi Zahid,

    If the processorcount column is an integer, you can just divide normally with processorcount/2

    select
    case
    WHEN (ipaddress like '10.4.%' or ipaddress like '10.4.%') and appname like '%WL%' THEN 'DEV WEBLOGIC'
    WHEN appname like '%WL%' and ipaddress like '10.4.%' THEN 'PROD WEBLOGIC'
    WHEN ipaddress like '10.4.%' or ipaddress like '10.4.%' THEN 'DEV' 
    WHEN ipaddress like '10.1.%' or ipaddress like'10.4.%' THEN 'PROD'

    end as Project
    , o.hostname 
    , o.ipaddress
    , o.appname
    , o.processorcount/2 AS processorcount
    , o.memorysize
    ,   o.is_virtual
    , i.instancename

    from oraclehosts o
    left join oracleinstances i on o.hostname = i.hostname
    left join hostwarranty w on w.hostname = o.hostname 
    Collate SQL_Latin1_General_CP1_CI_AS 
    left join puppetdb_certname_facts p 
    on p.certname = o.hostname where  
    p.fact = 'oracle_license' and p.fact = 'oracle_license' AND  p.value = 'true'

    group by 
    i.instanceName
    , o.hostname
    , o.ipaddress
    , o.processorcount
    , o.memorysize
    , o.is_virtual
    , i.is_ebs
    , o.appname


    Please mark as answered, If you feel happy with this answer.

    Wednesday, December 13, 2017 9:08 PM
  • Thanks for reply! Sorry forgot to mention that processor count/2 will be based on the condition like if is_virtual false then processorcount value will be divided/2

    Please help


    Zahid

    Wednesday, December 13, 2017 9:15 PM
  • select
    case
    WHEN (ipaddress like '10.4.%' or ipaddress like '10.4.%') and appname like '%WL%' THEN 'DEV WEBLOGIC'
    WHEN appname like '%WL%' and ipaddress like '10.4.%' THEN 'PROD WEBLOGIC'
    WHEN ipaddress like '10.4.%' or ipaddress like '10.4.%' THEN 'DEV' 
    WHEN ipaddress like '10.1.%' or ipaddress like'10.4.%' THEN 'PROD'

    end as Project
    , o.hostname 
    , o.ipaddress
    , o.appname
    , CASE o.is_virtual WHEN 'False' THEN o.processorcount/2 ELSE o.processorcount END AS processorcount
    , o.memorysize
    ,   o.is_virtual
    , i.instancename

    from oraclehosts o
    left join oracleinstances i on o.hostname = i.hostname
    left join hostwarranty w on w.hostname = o.hostname 
    Collate SQL_Latin1_General_CP1_CI_AS 
    left join puppetdb_certname_facts p 
    on p.certname = o.hostname where  
    p.fact = 'oracle_license' and p.fact = 'oracle_license' AND  p.value = 'true'

    group by 
    i.instanceName
    , o.hostname
    , o.ipaddress
    , o.processorcount
    , o.memorysize
    , o.is_virtual
    , i.is_ebs
    , o.appname

    Please mark as answered, If you feel happy with this answer.

    • Proposed as answer by Xi Jin Thursday, December 14, 2017 5:56 AM
    • Marked as answer by Zahid2017 Thursday, December 14, 2017 2:22 PM
    Wednesday, December 13, 2017 9:20 PM
  • Hi Zahid,

    If the condition is just when is_virtual false then processorcount value will be divided/2. You can use CASE WHEN statement to achieve this. And Ousama's solution should work for you.

    If not, please elaborate your logic and share us some sample data which can help us make some test.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 14, 2017 5:59 AM
  • Appreciate your help. 

    Thank you so much!


    Zahid

    Thursday, December 14, 2017 2:23 PM