T-SQL query

it is not clear to me what you are trying to get. I have seen the output but what do you need exactly?

Basically, I want to use this following query where i see the output something like this...

select distinct o.instancename --ISNULL(o.instancename, '') as instancename
,q.value as Orcale_Patch 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

Instancename      oraclepatch
MEPUAT                mepuat|(JAN2017)
Output-2.txt

I remember this - it was an epic question before - ended up having to create a function to break up 'facts" per  instance to retrieve value

Looks like we now have a table like structure for oracle_patch

in which case, we now need to check where [value] like instance+'%'

we still need to strip out some characters, something like  :

select fact, value, stuff([value],1,charindex('|',[value] )+ 1,'')as Orcale_PSU
 from puppetdb_certname_facts where fact='oracle_patch'  and [value] like instance+'%'

Select allOpen in new window

Do we need to strip out left and right brackets so "(JAN2018)" becomes "JAN2018"

Yes i need that and also want to see  No Patch which intances are not patched.

There was a below function last time you created.

USE [DBInfor]
GO

/****** Object:  UserDefinedFunction [dbo].[udfGetPatchDetails]    Script Date: 3/27/2018 3:52:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[udfGetPatchDetails] (@instance nvarchar (100), @value nvarchar(1000))
returns varchar(100)
as
begin

  declare @ins nvarchar(100)  = rtrim(ltrim(@instance)) COLLATE SQL_Latin1_General_CP850_Bin
  declare @val nvarchar(1000) = rtrim(ltrim(@value)) COLLATE SQL_Latin1_General_CP850_Bin

  return (case when charindex(@ins,@val) > 0
               then replace(replace(ltrim(
                    substring(@val
                   ,charindex('|',@val,charindex(@ins,@val)+1)+1   -- start position
                   ,charindex(char(10),@val + char(10),charindex(@ins,@val)) - (charindex('|',@val,charindex(@ins,@val)+1)+1)))  -- length
                   ,'(',''),')','')   -- replace brackets
               else '' end) -- as Orcale_PSU
 end
GO

all of a sudden, nothing looks like the original question!

Do you want to modify the Function - or happy to do manipulations with the select ?

I tried with that

select fact, value, stuff([value],1,charindex('|',[value] + 1,'') as Orcale_PSU
 from puppetdb_certname_facts where fact='oracle_patch'  and [value] like instance+'%'

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'as'.

was missing a bracket.... let me type it up and test - think I still have test data - except now we have a new table format for
 puppetdb_certname_facts where fact='oracle_patch' correct ?

Sure, Thanks.  correct.

OK, please try

select fact, [value], isnull(replace(replace(stuff([value],1,charindex('|',[value] ),''),'(',''),')',''),'') as Orcale_PSU 
from puppetdb_certname_facts where fact='oracle_patch'  and [value] like 'mepuat|%'

Select allOpen in new window

In original context

select distinct o.instancename
,q.value as Orcale_Patch 
,isnull(replace(replace(stuff(q.[value],1,charindex('|',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 q.value like o.instancename+'|%' 
order by instancename

Select allOpen in new window

Hi,

Frist query output is below:--
fact                 value                                            Orcale_PSU
oracle_patch      mepuat|(JAN2017)      JAN2017

Here is the output attached but did not get any output.
Output-2.txt

okay, must have some control characters in there because it seems the  "and q.value like o.instancename+'|%'" has failed....

we can try : and q.value like rtrim(o.instancename)+'%'

Might need to do a hex dump so I can check the content of [value] and instancename, ot we have collation mismatch again...

Hi Mark,

I have ran this following query and the output looks like better but some are not

select distinct o.instancename --ISNULL(o.instancename, '') as instancename
,isnull(replace(replace(stuff([value],1,charindex('|',[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 [value] like '%|%'
--and h.hostname not in (select p.hostname  from oracleProductionHosts p)
group by   instancename, q.value
order by instancename

Thanks

Zahid
Output-2.txt

Well, that is confusing. I thought we had a new file to search from. But it looks like we have some of the patch details still with multiple entries.

So, how is this different from last time ? What has changed with the list in [value] ?

I had thought that the "team" finally got its act together and had unpacked that column, but not if we look at :

AGLDEV          No Patch dw1tst|No Patch agldev|No Patch soadev|No Patch oatdev|No Patch vertextest|No Patch dw1dev|No Patch agltst|No Patch

Doing a hex dump, it seems that the very first character of [value] is char(09) - the tab character so searching for anything other than %instance% wont give you much at all. And not all rows have a '|'

So, let's go back a step and just do:

select distinct o.instancename --ISNULL(o.instancename, '') as instancename
,[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, q.value
order by instancename

Select allOpen in new window

I am pretty sure we will be back to using a modified function, but lets get the above output first

Function would be something like :

create function dbo.udfGetPatchDetails_new (@instance nvarchar (100), @value nvarchar(1000))
returns varchar(100)
as 
begin

   declare @ins nvarchar(100)  = rtrim(ltrim(@instance)) COLLATE SQL_Latin1_General_CP850_Bin
   declare @val nvarchar(1000) = rtrim(ltrim(@value)) COLLATE SQL_Latin1_General_CP850_Bin

   declare @inspos int = charindex(@ins,@val)
   declare @inslen int = len(@ins) + 1
   declare @pipepos int = charindex('|',@val,@inspos)

   return (case when charindex(@ins+'|No Patch',@val) > 0 then 'No Patch'
                when @inspos > 0 then replace(replace(ltrim(substring(@val ,@pipepos,charindex(char(32),@val + char(32),@pipepos+4) - @pipepos)),'(',''),')','')
                else 'No Patch' end)  
 end
 go

Select allOpen in new window

and to use

select distinct o.instancename --ISNULL(o.instancename, '') as instancename
,[value] 
,dbo.udfGetPatchDetails_new(o.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

Select allOpen in new window

Here i have attached the first query output.

Thanks

Zahid
Output--2-.txt

After using the function looks like you made this and almost done. Here i am going to attach the output.

Only the | sign is coming first.

GISTEST               |JAN2017
GRCPROD               |JAN2018 ccgprod|JAN2018

I am validating data. I will get back to you soon.

Thank You
Output-1.txt

That is correct. But still i need to check with team if they export data in a flat file different format then it will be more complex to get the from SQL side.

I guess ccgprod has something wrong format in the flat file source.

Also i see there is a new value added DEMUPG                JAN2018 vcpdev|No  which has the same problem. Please see the attached file.

Just wanted to make sure, after that i will close this.

Thanks

Zahid
Output-1.txt

I cant explain those two entries - CRGPROD works for me - and I dont have a DEMUPG. I agree they appear to have the same problem.

It would have to be something like a non-space character after the (jan2018) and before the next instancename.

Doing a hex dump on the output examples does have a space. Hence, it works for me. I really wish it didnt so I could track it down - could be as easy as another replace().

Maybe a raw dump from your side as a ZIP would help ?

Hi Mark,

In the flat file source there are some confidential data. I wish i could share with you. But i spoke with Linux team, they told me about they will fix in their puppet db so that it can dump in the windows file system. I have SSIS  job that basically process all these data and import into my SQL Database.  This is for reporting purposes. Anyway i appreciate your help. It worked for me.

Thanks

Zahid

Thank you so much!

Linux guys can be soooo .... umm .... err ... helpful :)

Would love to see what is stopping it - ah well.