locked
T-SQL query output RRS feed

  • Question

  • In the following query in the , ISNULL(q.value, '') as Orcale_PSU column how to get the output something like this

     No Patch ccgdev 

    instead of getting many values. In a word how can i separate this value

    |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch

    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,ISNULL(o.controlfiledate, '  ') as controlfiledate
    ,h.hostname
    ,h.ipaddress 
    ,case when o.is_ebs = 1 then 'EBS' when o.is_ebs <> 1 then 'Non-EBS' 
    when o.instancename IS NULL then 'OPEN'
    when o.is_ebs is null  then 'Non-EBS' 
    end as is_ebs
    , ISNULL(q.value, '') as Orcale_PSU
    ,ISNULL(o.[version], ' ') as [version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,CASE h.is_virtual WHEN 'False' THEN h.processorcount/2 ELSE h.processorcount END AS processorcount
    ,h.memorysize 
    ,ISNULL(o.lastbackup, ' ') as lastbackup
    ,max(p.value) as reportdate from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' 
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname like '%-db-%' and ipaddress not in ('10.4.129.97', '10.7.131.110', '10.7.131.164')
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,o.controlfiledate
    ,h.hostname
    ,q.value
    ,h.ipaddress 
    ,o.is_ebs
    ,o.[version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,h.processorcount 
    ,h.memorysize 
    ,o.lastbackup
    order by instancename, hostname

    Please help!

    Thanks

    Tuesday, February 6, 2018 3:38 PM

All replies

  • you need to use a string parsing udf for that

    like this

    ;With CTE
    AS
    (
    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,ISNULL(o.controlfiledate, '  ') as controlfiledate
    ,h.hostname
    ,h.ipaddress 
    ,case when o.is_ebs = 1 then 'EBS' when o.is_ebs <> 1 then 'Non-EBS' 
    when o.instancename IS NULL then 'OPEN'
    when o.is_ebs is null  then 'Non-EBS' 
    end as is_ebs
    , ISNULL(q.value, '') as Oracle_PSU
    ,ISNULL(o.[version], ' ') as [version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,CASE h.is_virtual WHEN 'False' THEN h.processorcount/2 ELSE h.processorcount END AS processorcount
    ,h.memorysize 
    ,ISNULL(o.lastbackup, ' ') as lastbackup
    ,max(p.value) as reportdate from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' 
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname like '%-db-%' and ipaddress not in ('10.4.129.97', '10.7.131.110', '10.7.131.164')
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,o.controlfiledate
    ,h.hostname
    ,q.value
    ,h.ipaddress 
    ,o.is_ebs
    ,o.[version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,h.processorcount 
    ,h.memorysize 
    ,o.lastbackup
    )
    
    
    SELECT *
    FROM CTE c
    CROSS APPLY dbo.ParseValues( c.Oracle_PSU,'|') f
    WHERE f.Val > ''
    order by c.instancename, c.hostname

    ParseValues UDF is here

    https://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

    If you've SQL 2017 you dont need this UDF

    you can use system function String_SPlit for this purpose

    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

    And it will look like

    ;With CTE
    AS
    (
    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,ISNULL(o.controlfiledate, '  ') as controlfiledate
    ,h.hostname
    ,h.ipaddress 
    ,case when o.is_ebs = 1 then 'EBS' when o.is_ebs <> 1 then 'Non-EBS' 
    when o.instancename IS NULL then 'OPEN'
    when o.is_ebs is null  then 'Non-EBS' 
    end as is_ebs
    , ISNULL(q.value, '') as Oracle_PSU
    ,ISNULL(o.[version], ' ') as [version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,CASE h.is_virtual WHEN 'False' THEN h.processorcount/2 ELSE h.processorcount END AS processorcount
    ,h.memorysize 
    ,ISNULL(o.lastbackup, ' ') as lastbackup
    ,max(p.value) as reportdate from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' 
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname like '%-db-%' and ipaddress not in ('10.4.129.97', '10.7.131.110', '10.7.131.164')
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,o.controlfiledate
    ,h.hostname
    ,q.value
    ,h.ipaddress 
    ,o.is_ebs
    ,o.[version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,h.processorcount 
    ,h.memorysize 
    ,o.lastbackup
    )
    
    
    SELECT *
    FROM CTE c
    CROSS APPLY String_Split( c.Oracle_PSU,'|') f
    WHERE f.Value > ''
    order by c.instancename, c.hostname
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Tuesday, February 6, 2018 3:47 PM
    Tuesday, February 6, 2018 3:46 PM
  • Thanks for replying. This whole query generates reports. Should i add 

    SELECT *
    FROM CTE c
    CROSS APPLY String_Split( c.Oracle_PSU,'|') f
    WHERE f.Value > ''
    order by c.instancename, c.hostname

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'String_Split'.


    • Edited by Zahid18 Tuesday, February 6, 2018 3:55 PM na
    Tuesday, February 6, 2018 3:54 PM
  • Thanks for replying.

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'String_Split'.

    As I suggested that will only work if you're on SQL 2017 or above

    otherwise you've to use the other suggestion


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 6, 2018 3:55 PM
  • Hi,

    Try with this function :

    CREATE FUNCTION SplitString
    (     
          @Input NVARCHAR(MAX),
          @Character CHAR(1)
    )
    RETURNS @Output TABLE (
          Item NVARCHAR(1000)
    )
    AS
    BEGIN
          DECLARE @StartIndex INT, @EndIndex INT
     
          SET @StartIndex = 1
          IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
          BEGIN
                SET @Input = @Input + @Character
          END
     
          WHILE CHARINDEX(@Character, @Input) > 0
          BEGIN
                SET @EndIndex = CHARINDEX(@Character, @Input)
                
                INSERT INTO @Output(Item)
                SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
                
                SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
          END
     
          RETURN
    END
    GO

    ;With CTE
    AS
    (
    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,ISNULL(o.controlfiledate, ' ') as controlfiledate
    ,h.hostname
    ,h.ipaddress 
    ,case when o.is_ebs = 1 then 'EBS' when o.is_ebs <> 1 then 'Non-EBS' 
    when o.instancename IS NULL then 'OPEN'
    when o.is_ebs is null  then 'Non-EBS' 
    end as is_ebs
    , ISNULL(q.value, '') as Oracle_PSU
    ,ISNULL(o.[version], ' ') as [version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,CASE h.is_virtual WHEN 'False' THEN h.processorcount/2 ELSE h.processorcount END AS processorcount
    ,h.memorysize 
    ,ISNULL(o.lastbackup, ' ') as lastbackup
    ,max(p.value) as reportdate from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' 
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname like '%-db-%' and ipaddress not in ('10.4.129.97', '10.7.131.110', '10.7.131.164')
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,o.controlfiledate
    ,h.hostname
    ,q.value
    ,h.ipaddress 
    ,o.is_ebs
    ,o.[version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,h.processorcount 
    ,h.memorysize 
    ,o.lastbackup
    )
    
    
    SELECT *
    FROM CTE c
    CROSS APPLY dbo.SplitString( c.Oracle_PSU,'|') f
    WHERE f.Value > ''
    order by c.instancename, c.hostname


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    Tuesday, February 6, 2018 4:00 PM
  • Hey Visakh,

    Could you please write-up the whole code for me:) I am using SQL Server 2012

    Appreciated.


    • Edited by Zahid18 Tuesday, February 6, 2018 4:05 PM na
    Tuesday, February 6, 2018 4:01 PM
  • Hey Visakh,

    Could you please write-up the whole code for me:)

    Appreciated.

    what do you mean ?

    I already gave the whole suggestion

    first create the udf as in the link

    CREATE FUNCTION ParseValues
    (@String varchar(8000), @Delimiter varchar(10) )
    RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(8000))
    AS
    BEGIN
    DECLARE @Value varchar(100)
    WHILE @String is not null
    BEGIN
    SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
    INSERT INTO @RESULTS (Val)
    SELECT @Value
    END
    RETURN
    END
    

    then use it like below

    ;With CTE
    AS
    (
    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,ISNULL(o.controlfiledate, '  ') as controlfiledate
    ,h.hostname
    ,h.ipaddress 
    ,case when o.is_ebs = 1 then 'EBS' when o.is_ebs <> 1 then 'Non-EBS' 
    when o.instancename IS NULL then 'OPEN'
    when o.is_ebs is null  then 'Non-EBS' 
    end as is_ebs
    , ISNULL(q.value, '') as Oracle_PSU
    ,ISNULL(o.[version], ' ') as [version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,CASE h.is_virtual WHEN 'False' THEN h.processorcount/2 ELSE h.processorcount END AS processorcount
    ,h.memorysize 
    ,ISNULL(o.lastbackup, ' ') as lastbackup
    ,max(p.value) as reportdate from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' 
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname like '%-db-%' and ipaddress not in ('10.4.129.97', '10.7.131.110', '10.7.131.164')
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,o.controlfiledate
    ,h.hostname
    ,q.value
    ,h.ipaddress 
    ,o.is_ebs
    ,o.[version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,h.processorcount 
    ,h.memorysize 
    ,o.lastbackup
    )
    
    
    SELECT *
    FROM CTE c
    CROSS APPLY dbo.ParseValues( c.Oracle_PSU,'|') f
    WHERE f.Val > ''
    order by c.instancename, c.hostname


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 6, 2018 4:07 PM
  • it worked but still i am getting multiple values

    dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch

    instead of just one value No Patch swalk

    Tuesday, February 6, 2018 4:16 PM
  • it worked but still i am getting multiple values

    dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch

    instead of just one value No Patch swalk

    what do you mean multiple values instead of one value

    what is your expected output? Just one out of the above values? Or all the above values coming as different rows with one value per row?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 6, 2018 4:27 PM
  • it worked but still i am getting multiple values

    dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch

    instead of just one value No Patch swalk


    Replace ISNULL(q.value, '') as Oracle_PSU with f.Val as Oracle_PSU

    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, February 6, 2018 4:28 PM
  • ;With CTE AS ( select distinct o.instancename --ISNULL(o.instancename, '') as instancename ,ISNULL(o.controlfiledate, ' ') as controlfiledate ,h.hostname ,h.ipaddress ,case when o.is_ebs = 1 then 'EBS' when o.is_ebs <> 1 then 'Non-EBS' when o.instancename IS NULL then 'OPEN' when o.is_ebs is null then 'Non-EBS' end as is_ebs , , ISNULL(q.value, '') as Oracle_PSU ,ISNULL(o.[version], ' ') as [version] ,h.hardware_servicetag ,h.is_virtual ,h.operatingsystem ,h.lsbdistdescription ,h.productname ,h.uptime ,CASE h.is_virtual WHEN 'False' THEN h.processorcount/2 ELSE h.processorcount END AS processorcount ,h.memorysize ,ISNULL(o.lastbackup, ' ') as lastbackup ,max(p.value) as reportdate from oraclehosts h left join oracleinstances o on h.hostname = o.hostname left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' and h.hostname like '%-db-%' and ipaddress not in ('10.4.129.97', '10.7.131.110', '10.7.131.164') and h.hostname not in (select p.hostname from oracleProductionHosts p) group by instancename ,o.controlfiledate ,h.hostname ,q.value ,h.ipaddress ,o.is_ebs ,o.[version] ,h.hardware_servicetag ,h.is_virtual ,h.operatingsystem ,h.lsbdistdescription ,h.productname ,h.uptime ,h.processorcount ,h.memorysize ,o.lastbackup ) SELECT c.*,

    f.Val AS Oracle_PSU FROM CTE c CROSS APPLY dbo.ParseValues( c.Oracle_PSU,'|') f WHERE f.Val > '' order by c.instancename, c.hostname



    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    Tuesday, February 6, 2018 4:29 PM
  • Msg 4104, Level 16, State 1, Line 13
    The multi-part identifier "f.Val" could not be bound.

    because if you look closely. I need to grab the info from this table

    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch'
    Tuesday, February 6, 2018 4:35 PM
  • May be you're still looking at old column 

    make it like this and see

    ;With CTE
    AS
    (
    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,ISNULL(o.controlfiledate, '  ') as controlfiledate
    ,h.hostname
    ,h.ipaddress 
    ,case when o.is_ebs = 1 then 'EBS' when o.is_ebs <> 1 then 'Non-EBS' 
    when o.instancename IS NULL then 'OPEN'
    when o.is_ebs is null  then 'Non-EBS' 
    end as is_ebs
    , ISNULL(q.value, '') as Oracle_PSU
    ,ISNULL(o.[version], ' ') as [version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,CASE h.is_virtual WHEN 'False' THEN h.processorcount/2 ELSE h.processorcount END AS processorcount
    ,h.memorysize 
    ,ISNULL(o.lastbackup, ' ') as lastbackup
    ,max(p.value) as reportdate from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' 
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname like '%-db-%' and ipaddress not in ('10.4.129.97', '10.7.131.110', '10.7.131.164')
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,o.controlfiledate
    ,h.hostname
    ,q.value
    ,h.ipaddress 
    ,o.is_ebs
    ,o.[version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,h.processorcount 
    ,h.memorysize 
    ,o.lastbackup
    )
    
    
    SELECT instancename
    ,controlfiledate
    ,hostname
    ,ipaddress 
    ,is_ebs
    , f.Val as Oracle_PSU
    ,[version]
    ,hardware_servicetag
    ,is_virtual 
    ,operatingsystem 
    ,lsbdistdescription 
    ,productname 
    ,uptime 
    ,processorcount
    ,memorysize 
    ,lastbackup
    ,reportdate 
    FROM CTE c
    CROSS APPLY dbo.ParseValues( c.Oracle_PSU,'|') f
    WHERE f.Val > ''
    order by c.instancename, c.hostname


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 6, 2018 4:38 PM
  • Msg 4104, Level 16, State 1, Line 13
    The multi-part identifier "f.Val" could not be bound.

    because if you look closely. I need to grab the info from this table

    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch'

    Sorry, I mean with this :

    ;With CTE
    AS
    (
    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,ISNULL(o.controlfiledate, '  ') as controlfiledate
    ,h.hostname
    ,h.ipaddress 
    ,case when o.is_ebs = 1 then 'EBS' when o.is_ebs <> 1 then 'Non-EBS' 
    when o.instancename IS NULL then 'OPEN'
    when o.is_ebs is null  then 'Non-EBS' 
    end as is_ebs
    
    , ISNULL(q.value, '') as Oracle_PSU
    
    
    ,ISNULL(o.[version], ' ') as [version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,CASE h.is_virtual WHEN 'False' THEN h.processorcount/2 ELSE h.processorcount END AS processorcount
    ,h.memorysize 
    ,ISNULL(o.lastbackup, ' ') as lastbackup
    ,max(p.value) as reportdate from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' 
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname like '%-db-%' and ipaddress not in ('10.4.129.97', '10.7.131.110', '10.7.131.164')
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,o.controlfiledate
    ,h.hostname
    ,q.value
    ,h.ipaddress 
    ,o.is_ebs
    ,o.[version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,h.processorcount 
    ,h.memorysize 
    ,o.lastbackup
    )
    
    
    SELECT c.*,
    f.Val AS Oracle_PSU
    FROM CTE c
    CROSS APPLY dbo.ParseValues( c.Oracle_PSU,'|') f
    WHERE f.Val > ''
    order by c.instancename, c.hostname



    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]



    Tuesday, February 6, 2018 4:44 PM
  • Now it is working but it is repeating for each instance name

    instancename  oraclepatch

    BPELTEST           (JAN2018) agltst  
    BPELTEST           No Patch agldev  
    BPELTEST           No Patch aglpgc  
    BPELTEST           No Patch agluat  
    BPELTEST           No Patch aglupg  
    BPELTEST           No Patch aglupg2  
    BPELTEST           No Patch ccgdev  
    BPELTEST           No Patch dw1dev  
    BPELTEST           No Patch grcdev  
    BPELTEST           No Patch oatdev  
    BPELTEST           No Patch oemtst  
    BPELTEST           No Patch oemupg  
    BPELTEST           No Patch rmandev  
    BPELTEST           No Patch soa11dev  
    BPELTEST           No Patch swalk  
    BPELTEST           No Patch vertextest  
    BPELTEST           No Patch
    BPELTEST         dw1tst  
    DW1TST             (JAN2018) agltst  
    DW1TST             No Patch agldev  
    DW1TST             No Patch aglpgc  
    DW1TST             No Patch agluat  
    DW1TST             No Patch aglupg  
    DW1TST             No Patch aglupg2  
    DW1TST             No Patch ccgdev  
    DW1TST             No Patch dw1dev  
    DW1TST             No Patch grcdev  
    DW1TST             No Patch oatdev  
    DW1TST             No Patch oemtst  
    DW1TST             No Patch oemupg  
    DW1TST             No Patch rmandev  
    DW1TST             No Patch soa11dev  
    DW1TST             No Patch swalk  
    DW1TST             No Patch vertextest  
    DW1TST             No Patch
    DW1TST           dw1tst  

    Tuesday, February 6, 2018 5:06 PM
  • Now it is working but it is repeating for each instance name

    instancename  oraclepatch

    BPELTEST           (JAN2018) agltst  
    BPELTEST           No Patch agldev  
    BPELTEST           No Patch aglpgc  
    BPELTEST           No Patch agluat  
    BPELTEST           No Patch aglupg  
    BPELTEST           No Patch aglupg2  
    BPELTEST           No Patch ccgdev  
    BPELTEST           No Patch dw1dev  
    BPELTEST           No Patch grcdev  
    BPELTEST           No Patch oatdev  
    BPELTEST           No Patch oemtst  
    BPELTEST           No Patch oemupg  
    BPELTEST           No Patch rmandev  
    BPELTEST           No Patch soa11dev  
    BPELTEST           No Patch swalk  
    BPELTEST           No Patch vertextest  
    BPELTEST           No Patch
    BPELTEST         dw1tst  
    DW1TST             (JAN2018) agltst  
    DW1TST             No Patch agldev  
    DW1TST             No Patch aglpgc  
    DW1TST             No Patch agluat  
    DW1TST             No Patch aglupg  
    DW1TST             No Patch aglupg2  
    DW1TST             No Patch ccgdev  
    DW1TST             No Patch dw1dev  
    DW1TST             No Patch grcdev  
    DW1TST             No Patch oatdev  
    DW1TST             No Patch oemtst  
    DW1TST             No Patch oemupg  
    DW1TST             No Patch rmandev  
    DW1TST             No Patch soa11dev  
    DW1TST             No Patch swalk  
    DW1TST             No Patch vertextest  
    DW1TST             No Patch
    DW1TST           dw1tst  

    it will because you're now splitting the multiple values into different rows, so other columns would have to repeat

    If this is not what you're looking for, then provide some sample data and explain how you want output to come for them


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 6, 2018 5:08 PM
  • Thanks visakh,

    Hostname fact value
    xxx oracel_patch
    dw1tst | No Patch
    swalk | No Patch
    aglpgc | No Patch
    ccgdev | No Patch
    rmandev | No Patch
    oemtst | No Patch
    oemupg | No Patch
    aglupg | No Patch
    oatdev | No Patch
    vertextest | No Patch
    grcdev | (JAN2018)
    agltst | No Patch
    agldev | No Patch
    agluat | No Patch
    dw1dev | No Patch
    soa11dev | No Patch
    aglupg2 | No Patch
    XXY oracel_patch

    Value

    dw1ps4 | No Patch
    dw1ps2 | No Patch
    soa11tst | No Patch
    dw1ps8 | No Patch

    dw1ps9 | (JAN2018)



    Tuesday, February 6, 2018 5:22 PM
  • I want something like this

    instancename hostname oraclepatch

    agldev  xxx agldev | No Patch

    dw1dev xxx dw1dev | No Patch

    Tuesday, February 6, 2018 5:24 PM
  • I want something like this

    instancename hostname oraclepatch

    agldev  xxx agldev | No Patch

    dw1dev xxx dw1dev | No Patch

    so what happened to rest of the patch names? Are you telling you want one value per instance hostname?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 6, 2018 5:28 PM
  • Yes, i want one value for per instance and hostname
    Tuesday, February 6, 2018 5:31 PM
  • Yes, i want one value for per instance and hostname

    then try this

    ;With CTE
    AS
    (
    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,ISNULL(o.controlfiledate, '  ') as controlfiledate
    ,h.hostname
    ,h.ipaddress 
    ,case when o.is_ebs = 1 then 'EBS' when o.is_ebs <> 1 then 'Non-EBS' 
    when o.instancename IS NULL then 'OPEN'
    when o.is_ebs is null  then 'Non-EBS' 
    end as is_ebs
    , ISNULL(q.value, '') as Oracle_PSU
    ,ISNULL(o.[version], ' ') as [version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,CASE h.is_virtual WHEN 'False' THEN h.processorcount/2 ELSE h.processorcount END AS processorcount
    ,h.memorysize 
    ,ISNULL(o.lastbackup, ' ') as lastbackup
    ,max(p.value) as reportdate from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' 
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname like '%-db-%' and ipaddress not in ('10.4.129.97', '10.7.131.110', '10.7.131.164')
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,o.controlfiledate
    ,h.hostname
    ,q.value
    ,h.ipaddress 
    ,o.is_ebs
    ,o.[version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,h.processorcount 
    ,h.memorysize 
    ,o.lastbackup
    )
    
    
    SELECT instancename
    ,controlfiledate
    ,hostname
    ,ipaddress 
    ,is_ebs
    , f.Val as Oracle_PSU
    ,[version]
    ,hardware_servicetag
    ,is_virtual 
    ,operatingsystem 
    ,lsbdistdescription 
    ,productname 
    ,uptime 
    ,processorcount
    ,memorysize 
    ,lastbackup
    ,reportdate 
    FROM CTE c
    CROSS APPLY dbo.ParseValues( c.Oracle_PSU,'|') f
    WHERE f.Val > ''
    AND f.ID = 2
    order by c.instancename, c.hostname


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 6, 2018 5:43 PM
  • Visakh,

    Now my team is asking me to see only no patch instead of repeating rmandev | No Patch

    Thanks

    Tuesday, February 6, 2018 7:15 PM
  • Visakh,

    Now my team is asking me to see only no patch instead of repeating rmandev | No Patch

    Thanks

    Hi Zahid18,

    Thanks for your reply.

    Based on your reply, could you please provide the sample data and desired output again? Without these information, I couldn't provide more particular solution.

    In addition, please notice the format of the data, or you'd better post the data like this:

    1) CREATE TABLE statements for the table(s) involved. 
    2) INSERT statements with sample data.
    3) The desired result given the sample, or through shown in screenshot.

    Best Regards,

    Will


    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.

    Wednesday, February 7, 2018 6:59 AM
  • Visakh,

    Now my team is asking me to see only no patch instead of repeating rmandev | No Patch

    Thanks

    Again unless you give us some sample output, we cant understand what you're talking about

    I have no idea what you mean by repeating rmandev so please show with output what exactly you're looking at

    May be this is what you meant !

    ;With CTE
    AS
    (
    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,ISNULL(o.controlfiledate, '  ') as controlfiledate
    ,h.hostname
    ,h.ipaddress 
    ,case when o.is_ebs = 1 then 'EBS' when o.is_ebs <> 1 then 'Non-EBS' 
    when o.instancename IS NULL then 'OPEN'
    when o.is_ebs is null  then 'Non-EBS' 
    end as is_ebs
    , ISNULL(q.value, '') as Oracle_PSU
    ,ISNULL(o.[version], ' ') as [version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,CASE h.is_virtual WHEN 'False' THEN h.processorcount/2 ELSE h.processorcount END AS processorcount
    ,h.memorysize 
    ,ISNULL(o.lastbackup, ' ') as lastbackup
    ,max(p.value) as reportdate from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' 
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname like '%-db-%' and ipaddress not in ('10.4.129.97', '10.7.131.110', '10.7.131.164')
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,o.controlfiledate
    ,h.hostname
    ,q.value
    ,h.ipaddress 
    ,o.is_ebs
    ,o.[version]
    ,h.hardware_servicetag
    ,h.is_virtual 
    ,h.operatingsystem 
    ,h.lsbdistdescription 
    ,h.productname 
    ,h.uptime 
    ,h.processorcount 
    ,h.memorysize 
    ,o.lastbackup
    )
    
    
    SELECT instancename
    ,controlfiledate
    ,hostname
    ,ipaddress 
    ,is_ebs
    , RTRIM(STUFF(f.Val,1,CHARINDEX('|',f.Val),''))  as Oracle_PSU
    ,[version]
    ,hardware_servicetag
    ,is_virtual 
    ,operatingsystem 
    ,lsbdistdescription 
    ,productname 
    ,uptime 
    ,processorcount
    ,memorysize 
    ,lastbackup
    ,reportdate 
    FROM CTE c
    CROSS APPLY dbo.ParseValues( c.Oracle_PSU,'|') f
    WHERE f.Val > ''
    AND f.ID = 2
    order by c.instancename, c.hostname


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 7, 2018 7:18 AM
  • Thanks again. Here is the output attached. As well as i have minimized the main query for better purposes.

    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,q.value as Orcale_PSU from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,q.value
    order by instancename

    Why can't i attached the output though.

    Wednesday, February 7, 2018 3:30 PM
  • Here is the sample data, i need to know whether instance is patch or no patch

    AGLDEV           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    AGLPGC           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    AGLPROD          NULL
    AGLTST           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    AGLUAT           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    AGLUPG           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    AGLUPG2          dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    BPELPROD         NULL
    CCGDEV           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    CCGPROD          NULL
    DEMDEV           demdev  |  No Patch demtest  |  No Patch
    DEMPROD          NULL
    DEMTEST          demdev  |  No Patch demtest  |  No Patch
    DEV              dev  |  (JAN2018)
    DW1              NULL
    DW1              dw1  |  No Patch
    DW1DEV           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    DW1PS2           dw1ps6  |  No Patch dw1ps4  |  No Patch dw1ps2  |  No Patch soa11tst  |  No Patch dw1ps8  |  No Patch dw1ps9  |  (JAN2018)
    DW1PS4           dw1ps6  |  No Patch dw1ps4  |  No Patch dw1ps2  |  No Patch soa11tst  |  No Patch dw1ps8  |  No Patch dw1ps9  |  (JAN2018)
    DW1PS6           dw1ps6  |  No Patch dw1ps4  |  No Patch dw1ps2  |  No Patch soa11tst  |  No Patch dw1ps8  |  No Patch dw1ps9  |  (JAN2018)
    DW1PS8           dw1ps6  |  No Patch dw1ps4  |  No Patch dw1ps2  |  No Patch soa11tst  |  No Patch dw1ps8  |  No Patch dw1ps9  |  (JAN2018)
    DW1PS9           dw1ps6  |  No Patch dw1ps4  |  No Patch dw1ps2  |  No Patch soa11tst  |  No Patch dw1ps8  |  No Patch dw1ps9  |  (JAN2018)
    DW1TST           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    EPPROD10         NULL
    FVPROD17         fvprod17  |  No Patch
    GISDEV           gisdev  |  (JAN2018)
    GISTEST          gistest  |  (JAN2017)
    GRCDEV           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    GRCPROD          NULL
    HYPDEV           hypdev  |  No Patch
    MARCGTPD         NULL
    MEPDEV           mepdev  |  (JAN2017)
    MEPTEST          meptest  |  No Patch
    MEPUAT           mepuat  |  (JAN2017)
    MEPUATRP         mepuatrp  |  No Patch
    MTHEND           mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend   (JAN2017)  | mthend  
    O606MCK3         o606mck3  |  (JAN2017)
    O606PTT          NULL
    O606SUP          o606sup  |  (JAN2017)
    OATDEV           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    OEM              NULL
    OEMTST           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    OEMUPG           dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    PATEST           patest  |  (JAN2018)
    PATESTRP         patestrp  |  (JAN2018)
    PROD             NULL
    QAUAT            qauat  |  No Patch
    R606CMOD         NULL
    R606MCK4         r606mck4  |  No Patch
    R606PTT          r606ptt  |  No Patch
    R606PTT2         r606ptt2  |  No Patch
    R606SUP          r606sup  |  No Patch
    REVPROD          NULL
    RMANDEV          dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    RMANPROD         NULL
    SKTDEV           sktdev  |  (JAN2017)
    SOA11DEV         dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    SOA11TST         dw1ps6  |  No Patch dw1ps4  |  No Patch dw1ps2  |  No Patch soa11tst  |  No Patch dw1ps8  |  No Patch dw1ps9  |  (JAN2018)
    SOAPD            NULL
    SWALK            dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    TEST             test  |  (JAN2018)
    VERTEXPD         NULL
    VERTEXTEST       dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch
    WMSDEV           wmsdev  |  (JAN2017)

    • Edited by Zahid18 Wednesday, February 7, 2018 3:37 PM
    Wednesday, February 7, 2018 3:34 PM
  • If you see colsely few instances got patched for an example from the left side

    grcdev | (JAN2008) display will be grcdev JAN2018

    I need this info where instance name is grcdev 

    Thanks

    Wednesday, February 7, 2018 3:40 PM
  • If you see colsely few instances got patched for an example from the left side

    grcdev | (JAN2008) display will be grcdev JAN2018

    I need this info where instance name is grcdev 

    Thanks

    Hi Zahid18,

    Do you mean to want this?

    create table dev_patch
    (
     instance_name varchar(64),
     v_value varchar(max)
    )
    
    insert into dev_patch values
    ('AGLDEV','dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch'),
    ('AGLPGC','dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch'),
    ('AGLPROD',NULL ),
    ('AGLTST','dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch'), 
    ('AGLUAT','dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch'),
    ('AGLUPG','dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch'), 
    ('AGLUPG2','dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch'), 
    ('BPELPROD', NULL),
    ('CCGDEV','dw1tst  |  No Patch swalk  |  No Patch aglpgc  |  No Patch ccgdev  |  No Patch rmandev  |  No Patch oemtst  |  No Patch oemupg  |  No Patch aglupg  |  No Patch oatdev  |  No Patch vertextest  |  No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  |  No Patch dw1dev  |  No Patch soa11dev  |  No Patch aglupg2  |  No Patch'), 
    ('CCGPROD',NULL),
    ('DEMDEV','demdev  |  No Patch demtest  |  No Patch'), 
    ('DEMPROD',NULL), 
    ('DEMTEST','demdev  |  No Patch demtest  |  No Patch'),
    ('DEV','dev  |  (JAN2018)'), 
    ('DW1',NULL), 
    ('DW1','dw1  |  No Patch') 
    
    select  instance_name,v_value,
    case when CHARINDEX('|',isnull(v_value,''))=0 then null else  rtrim(SUBSTRING(isnull(v_value,''),1,CHARINDEX('|',isnull(v_value,''))-1)) end as get_value from  dev_patch

    Best Regards,

    Will


    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.

    Friday, February 9, 2018 9:30 AM
  • If you see colsely few instances got patched for an example from the left side

    grcdev | (JAN2008) display will be grcdev JAN2018

    I need this info where instance name is grcdev 

    Thanks

    Again you didn't give us the output you're expecting so we can only guess

    Please understand that as long as you didn't give us proper info, this will keep on going back and forth.

    Anyways as per your latest explanation, it may be this (again another guess and this will be my last!)

    select distinct o.instancename --ISNULL(o.instancename, '') as instancename
    ,q.value as Orcale_PSU
    ,CASE WHEN q.value LIKE '%No Patch%' THEN 'Not Patched' ELSE 'Patched' END AS PatchStatus 
    from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename
    ,q.value
    order by instancename

    If this is still not giving what you're asking for then you've to post proper data with your expected output. Until then I'll refrain from offering any more help!


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, February 9, 2018 9:45 AM
  • Will,

    Thanks for your time. You are almost close what i was expecting. Before that here is my little explanation what i have done based on your suggestion:--

    create table oracle_patch
    (
     certname varchar(64),
     value varchar(max)
    )

    insert into oracle_patch select certname, value from puppetdb_certname_facts where fact ='oracle_patch'

    (Because puppetdb_certname_facts is a main table where it gets data every hour from falt file source, and flat file has this string value)

    If i run this below query then i am getting accurate data.

    select  certname as instancename,value,
    case when CHARINDEX('|',isnull(value,''))=0 then null else  rtrim(SUBSTRING(isnull(value,''),1,CHARINDEX('|',isnull(value,''))-1)) end as oracle_patch from  oracle_patch

    Output example:--

    instancename value oracle_patch
    am-app-262.xxxx.net xe  |  No Patch xe
    am-app-263.xxx.net xe  |  No Patch xe

    Now in the oracle_patch column i am getting value xe, i need the value only No Patch.

    After that in the following query 

    select distinct o.instancename --ISNULL(o.instancename, '') as instancename, 
    ,o.hostname
    ,q.value 
    from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename, o.hostname
    ,q.value
    order by hostname

    how do i pass the following query. Then we are done!

    select  certname as instancename,value,
    case when CHARINDEX('|',isnull(value,''))=0 then null else  rtrim(SUBSTRING(isnull(value,''),1,CHARINDEX('|',isnull(value,''))-1)) end as oracle_patch from  oracle_patch

    Thanks


    Friday, February 9, 2018 4:40 PM
  • Now in the oracle_patch column i am getting value xe, i need the value only No Patch.

    For that you need to change the query like this

    select  certname as instancename,value,
    LTRIM(STUFF(value,1,CHARINDEX('|',value + '|'),'')) as oracle_patch from  oracle_patch

    for the second question, merge the queries like this

    select m.instancename,
    m.hostname,
    m.value,
    n.oracle_patch
    from
    (
    select distinct o.instancename 
    ,o.hostname
    ,q.value 
    from oraclehosts h 
    left join oracleinstances o on h.hostname = o.hostname
    left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 
    and h.hostname not in (select p.hostname  from oracleProductionHosts p) 
    group by   instancename, o.hostname
    ,q.value
    order by hostname
    )m
    join
    (
    select  certname as instancename,value,
    ltrim(stuff(value,1,charindex('|',value + '|'),'')) as oracle_patch from  oracle_patch
    )n
    on n.instancename = m.instancename

    Atleast in future post proper sample data and required output like what you did here to make sure you get quick and accurate solutions


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, February 9, 2018 6:03 PM