T-SQL query

Hi,

I need a help to get the data separately  from this below query. Output is attached.

select fact, value from puppetdb_certname_facts where fact='oracle_patch'

Thanks

Zahid
Output-1.txt
Zahid AhamedDatabase AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
it is not clear to me what you are trying to get. I have seen the output but what do you need exactly?
Zahid AhamedDatabase AdministratorAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
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+'%'

Open in new window

Do we need to strip out left and right brackets so "(JAN2018)" becomes "JAN2018"
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Zahid AhamedDatabase AdministratorAuthor Commented:
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
Éric MoreauSenior .Net ConsultantCommented:
all of a sudden, nothing looks like the original question!
Mark WillsTopic AdvisorCommented:
Do you want to modify the Function - or happy to do manipulations with the select ?
Zahid AhamedDatabase AdministratorAuthor Commented:
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'.
Mark WillsTopic AdvisorCommented:
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 ?
Zahid AhamedDatabase AdministratorAuthor Commented:
Sure, Thanks.  correct.
Mark WillsTopic AdvisorCommented:
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|%'

Open 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

Open in new window

Zahid AhamedDatabase AdministratorAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
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...
Zahid AhamedDatabase AdministratorAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
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

Open 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

Open 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

Open in new window

Zahid AhamedDatabase AdministratorAuthor Commented:
Here i have attached the first query output.

Thanks

Zahid
Output--2-.txt
Zahid AhamedDatabase AdministratorAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
Here's the updated function :
alter 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+1,charindex(char(32),@val + char(32),@pipepos+4) - @pipepos)),'(',''),')','')
                else 'No Patch' end)  
 end
 go

Open in new window

Still investigating the inclusion of ccgprod - doesnt make sense. analysing the hex dump

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
just loaded up the full output--2-.txt file and results using above procedure look good....
INSTANCENAME	Orcale_PSU	VALUE
ACLPROD         	No Patch	 oem|No Patch soapd|No Patch bpelprod|No Patch epprod10|No Patch vertexpd|No Patch rmanprod|No Patch aclprod|No Patch aglprod|No Patch
AGLDEV          	No Patch	 swalk|No Patch dw1tst|No Patch agldev|No Patch soadev|No Patch oatdev|No Patch vertextest|No Patch dw1dev|No Patch agltst|No Patch
AGLPROD         	No Patch	 oem|No Patch soapd|No Patch bpelprod|No Patch epprod10|No Patch vertexpd|No Patch rmanprod|No Patch aclprod|No Patch aglprod|No Patch
AGLTST          	No Patch	 swalk|No Patch dw1tst|No Patch agldev|No Patch soadev|No Patch oatdev|No Patch vertextest|No Patch dw1dev|No Patch agltst|No Patch
BPELPROD        	No Patch	 oem|No Patch soapd|No Patch bpelprod|No Patch epprod10|No Patch vertexpd|No Patch rmanprod|No Patch aclprod|No Patch aglprod|No Patch
CCGPROD         	JAN2018 	 grcprod|(JAN2018) ccgprod|(JAN2018)
DEMDEV          	No Patch	 demdev|No Patch demtest|No Patch
DEMPROD         	No Patch	 demprod|No Patch
DEMTEST         	No Patch	 demdev|No Patch demtest|No Patch
DEV               	No Patch	 dev|No Patch
DW1               	JAN2018	         dw1|(JAN2018)
DW1               	No Patch	 dw1|No Patch
DW1DEV          	No Patch	 swalk|No Patch dw1tst|No Patch agldev|No Patch soadev|No Patch oatdev|No Patch vertextest|No Patch dw1dev|No Patch agltst|No Patch
DW1PS6          	No Patch	 dw1ps6|No Patch soa11tst|No Patch dw1ps9|(JAN2018)
DW1PS9          	JAN2018 	 dw1ps6|No Patch soa11tst|No Patch dw1ps9|(JAN2018)
DW1TST          	No Patch	 swalk|No Patch dw1tst|No Patch agldev|No Patch soadev|No Patch oatdev|No Patch vertextest|No Patch dw1dev|No Patch agltst|No Patch
EPPROD10        	No Patch	 oem|No Patch soapd|No Patch bpelprod|No Patch epprod10|No Patch vertexpd|No Patch rmanprod|No Patch aclprod|No Patch aglprod|No Patch
GISDEV           	JAN2018 	 gisdev|(JAN2018)
GISTEST         	JAN2017 	 gistest|(JAN2017)
GRCPROD         	JAN2018 	 grcprod|(JAN2018) ccgprod|(JAN2018)
HYPDEV          	No Patch	 hypdev|No Patch
MEPTEST         	JAN2018 	 meptest|(JAN2018)
MEPUA2RP        	JAN2018 	 mepua2rp|(JAN2018)
MEPUAT          	JAN2017 	 mepuat|(JAN2017)
MEPUAT2         	JAN2018 	 mepuat2|(JAN2018)
MEPUATRP        	No Patch	 mepuatrp|No Patch
MTHEND          	JAN2017 	 mthend|(JAN2017)
MTHENDRP        	No Patch	 mthendrp|No Patch
O606MCK3        	JAN2017 	 o606mck3|(JAN2017)
O606PTT3        	JAN2017 	 o606ptt3|(JAN2017)
O606SUP         	JAN2017 	 o606sup|(JAN2017)
OATDEV          	No Patch	 swalk|No Patch dw1tst|No Patch agldev|No Patch soadev|No Patch oatdev|No Patch vertextest|No Patch dw1dev|No Patch agltst|No Patch
OEM               	No Patch	 oem|No Patch soapd|No Patch bpelprod|No Patch epprod10|No Patch vertexpd|No Patch rmanprod|No Patch aclprod|No Patch aglprod|No Patch
PATEST           	JAN2018 	 patest|(JAN2018)
PATESTRP        	No Patch	 patestrp|No Patch
PROD             	JAN2017 	 prod|(JAN2017)
QAUAT           	No Patch	 qauat|No Patch
R606MCK4        	No Patch	 r606mck4|No Patch
R606PTT2        	No Patch	 r606ptt2|No Patch
R606PTT3        	JAN2018 	 r606ptt3|(JAN2018)
R606SUP         	No Patch	 r606sup|No Patch
REVPROD         	No Patch	 revprod|No Patch
RMANPROD        	No Patch	 oem|No Patch soapd|No Patch bpelprod|No Patch epprod10|No Patch vertexpd|No Patch rmanprod|No Patch aclprod|No Patch aglprod|No Patch
SECDEV          	JAN2018 	 secdev|(JAN2018)
SECDEVRP        	JAN2018 	 secdevrp|(JAN2018)
SECUAT          	JAN2018 	 secuat|(JAN2018)
SECUATRP        	JAN2018 	 secuatrp|(JAN2018)
SKTDEV          	JAN2017 	 sktdev|(JAN2017)
SOA11TST        	No Patch	 dw1ps6|No Patch soa11tst|No Patch dw1ps9|(JAN2018)
SOADEV          	No Patch	 swalk|No Patch dw1tst|No Patch agldev|No Patch soadev|No Patch oatdev|No Patch vertextest|No Patch dw1dev|No Patch agltst|No Patch
SOAPD           	No Patch	 oem|No Patch soapd|No Patch bpelprod|No Patch epprod10|No Patch vertexpd|No Patch rmanprod|No Patch aclprod|No Patch aglprod|No Patch
SWALK            	No Patch	 swalk|No Patch dw1tst|No Patch agldev|No Patch soadev|No Patch oatdev|No Patch vertextest|No Patch dw1dev|No Patch agltst|No Patch
TEST              	JAN2018 	 test|(JAN2018)
VCPDEV          	No Patch	 vcpdev|No Patch
VERTEXPD        	No Patch	 oem|No Patch soapd|No Patch bpelprod|No Patch epprod10|No Patch vertexpd|No Patch rmanprod|No Patch aclprod|No Patch aglprod|No Patch
VERTEXTEST      	No Patch	 swalk|No Patch dw1tst|No Patch agldev|No Patch soadev|No Patch oatdev|No Patch vertextest|No Patch dw1dev|No Patch agltst|No Patch

Open in new window

Zahid AhamedDatabase AdministratorAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
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 ?
Zahid AhamedDatabase AdministratorAuthor Commented:
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
Zahid AhamedDatabase AdministratorAuthor Commented:
Thank you so much!
Mark WillsTopic AdvisorCommented:
Linux guys can be soooo .... umm .... err ... helpful :)

Would love to see what is stopping it - ah well.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
T-SQL

From novice to tech pro — start learning today.