T-SQL query output
Question
All replies
-
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
"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]- Edited by Ousama EL HOR Tuesday, February 6, 2018 4:05 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_PSUOusama EL HOR
[If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click
[User Page] [MSDN Page] [Blog] [Linkedin] -
;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
[User Page] [MSDN Page] [Blog] [Linkedin]- Edited by Ousama EL HOR Tuesday, February 6, 2018 4:43 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
[User Page] [MSDN Page] [Blog] [Linkedin]
- Edited by Ousama EL HOR Tuesday, February 6, 2018 4:47 PM
