T-SQL query

Hi,

In the following query i would like to get the output like this

instancename      controlfiledate      hostname      ipaddress      is_ebs      Orcale_PSU      version      hardware_servicetag      is_virtual      operatingsystem      lsbdistdescription      productname      uptime      processorcount      memorysize      lastbackup      reportdate
CCGDEV                2014-10-13      xxx.net      10.4.x.x      Non-EBS  No Patch ccgdev        No-Backup      Tue Feb  6 05:48:26 PST 2018 in the oracle_psu column

but in the following query when i run then i get in oracle_psu column

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

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.', '10.7.', '10.7.')
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


How do i get the record from oracle_psu column

, ISNULL(q.value, '') as Orcale_PSU is it correct?
Zahid AhamedDatabase AdministratorAsked:
Who is Participating?
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.

Mark WillsTopic AdvisorCommented:
Well q.value is coming from a left join puppetdb_certname_facts q

So, it could well be NULL and so, "ISNULL(q.value, '') as Orcale_PSU" is a legitimate statement - despite the spelling :).

I cannot tell the content of q.value, and have to assume it is the appropriate column

But, the extra conditions on that join are a bit of a concern and (guessing) lies at the root of your unexpected results.

For a start, you have on the next line "and h.hostname" but the alias is q
Secondly those IPaddresses seem to be incomplete "ipaddress not in ('10.4.', '10.7.', '10.7.')"   the sample shows 10.4.x.x

It seems that you are missing a WHERE clause after the "left join puppetdb_certname_facts q ... "
left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'oracle_patch' 

WHERE 1=1     -- or something more relevant
 
and h.hostname like '%-db-%' and ipaddress not in ('10.4.', '10.7.', '10.7.')
and h.hostname not in (select p.hostname  from oracleProductionHosts p) 

Open in new window


Could that be your problem ?
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Thanks for replying.

i would like to get one value from oracle psu column and it should be match based on the instance name. How can i separate them

instancename            Orcale_PSU            
GRCDEV                       No Patch grcdev      

Where as i am getting the following values
instancename            Orcale_PSU
No Patch 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      12.1.0.2.0                                              3ST0K02         false      OracleLinux      Oracle Linux Server release 6.9      PowerEdge R720xd      88 days      20      252.23 GB      No-Backup      Tue Feb  6 07:48:32 PST 2018
0
Zahid AhamedDatabase AdministratorAuthor Commented:
I want something like this

instancename hostname oraclepatch

agldev  xxx agldev | No Patch

dw1dev xxx dw1dev | No Patch
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Mark WillsTopic AdvisorCommented:
OK then fix your query.

You keep saying what you want, but I believe your query is wrong and needs work.

As you have posted,
 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.', '10.7.', '10.7.')
and h.hostname not in (select p.hostname  from oracleProductionHosts p) 

Open in new window

Does not make sense. It really does look like you are missing a WHERE clause otherwise you are adding additional conditions to the last table you are joining....Could you be missing a WHERE clause ?
 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' 
WHERE h.hostname like '%-db-%' and ipaddress not in ('10.4.', '10.7.', '10.7.')
and h.hostname not in (select p.hostname  from oracleProductionHosts p) 

Open in new window

Because what you have posted as your code is definitely not giving you what you want...
0
Mark WillsTopic AdvisorCommented:
You also have a lot of columns, and grouping by all of them, so can only assume what you are showing the output is only a partial highly selective output and would help if you included column headers and put results in a code block.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Here is the output attached in the body as an xml file. Thank you
0
Zahid AhamedDatabase AdministratorAuthor Commented:
So, the Oracle_PSU column carries a long string value with multiple instance name. I need to separate the value as instance name with the information regarding patch, no patch or date separator.
0
Mark WillsTopic AdvisorCommented:
Sorry, nothing attached....
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Please find the attached output. In the Oracle_PSU column i need the instance name Patch with date or No Patch . Here is the following query

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
order by instancename
output.xlsx
0
Zahid AhamedDatabase AdministratorAuthor Commented:
f you see colsely the output 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 something like that

Thanks
0
Mark WillsTopic AdvisorCommented:
OK, is there anything different if you do

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' 
where h.hostname not in (select p.hostname  from oracleProductionHosts p) 
group by   instancename,q.value
order by instancename

Open in new window

Please post the results.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Attached. in this time 51 rows are affected. in the first line i am expecting AGLDEV No Patch, just one value
output.xlsx
0
Mark WillsTopic AdvisorCommented:
OK, seem to be getting somewhere....

The join "left join puppetdb_certname_facts q on q.certname = o.hostname" doesnt seem to be targeting the instance

Is there a seperate column in puppetdb_certname_facts denoting the Instance name independently  ?

At the moment it is returning a pipe delimited result of all instances within that hostname

While we can "see" the corresponding instancename, the delimiters are not segregating the instances by instance name...

e.g. it would appear the an instance entry in the table is of the format "instancename | status"  e.g. "agldev  |  No Patch"

While we can readily identify agldev, it is hard to isolate the status when part of a string
 "... | No Patch grcdev  |  (JAN2018) agltst  |  No Patch agldev  |  No Patch agluat  | ..."

So, what columns are available from puppetdb_certname_facts ?

Whatever / however that table is being populated, is it possible to include the pipe delimiter for each instance ?

E.g. "... | No Patch  |  grcdev  |  (JAN2018)  |  agltst  |  No Patch  |  agldev  |  No Patch  |  agluat  | ..."
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Will, Thanks for your time. Basically, all data stored in a flat file and there is a SSIS process someone worked before i joined my current work. This SSIS process has several steps and it dumps into a SQL Database table. From there SSRS generates reports. I can give you some hints, there is a step in the SSIS Package, he wrote a similar type of requirement for column name controlfiledate. Here is the following query for your better approach...


declare @toparse nvarchar(max)
declare @strlength int
declare @separator_location int
declare @instance nvarchar(10)
declare @instance_startdate nvarchar(20)
declare @hostname nvarchar(50)
declare @strIn nvarchar(2000)
declare @separator nchar(2) = ' '
declare @inner_separator nchar(1) = '|'

DECLARE @item nvarchar(1000)  -- assume maximun item length 1000 char
DECLARE @len int, @sepPos1 int,  @sepPos2 int

-- truncate table oracleinstances
declare @truncate varchar(200)
set @truncate = 'truncate table oracleInstances'
exec (@truncate)

-- get oracle instances to load
declare  oracleinstance_csr cursor for
select certname
      , value
from puppetdb_certname_facts
where fact like 'oracleinstancelist'
   and value IS NOT NULL
   and value !=''
  and value != '""'

open oracleinstance_csr

fetch next from oracleinstance_csr into @hostname, @strIn

--select @hostname, @strIn

while @@FETCH_STATUS = 0
begin
 
  SET @strIn = REPLACE(@strIn, char(10), @separator)  -- Replace all the LF (char(10)) from the string and replace with defined separator [ the code for CR is char(13)]
  Set @sepPos1 = 1
  set @sepPos2 = charindex(@separator, @strIn)

  SET @strin = @strIn + @separator

  IF @sepPos2 = 0  -- nothing to parse here
  BEGIN
     SET @item = @strIn
     SET @toparse =  ( RTRIM(LTRIM(@item)))
     
       select @strlength = LEN(@toparse)
       select @separator_location = charindex(@inner_separator, @toparse)

      
       select @instance = LEFT(@toparse,(@separator_location-1))
       select @instance_startdate = RIGHT(@toparse, (@strlength-@separator_location))

        insert into oracleInstances ( instanceName, hostname, controlfiledate)
        values (@instance, @hostname, @instance_startdate)
       -- select @instance, @hostname, @instance_startdate
      
  END
ELSE
  BEGIN
    WHILE @sepPos2 > 0
    BEGIN
        -- get parse through the values
      SET @item = substring(@strIn, @sepPos1,@sepPos2-1)
      SET @toparse =  ( RTRIM(LTRIM(@item)))
     
        select @strlength = LEN(@toparse)
        select @separator_location = charindex(@inner_separator, @toparse)

        select @instance = LEFT(@toparse,(@separator_location-1))
        select @instance_startdate = RIGHT(@toparse, (@strlength-@separator_location))

        insert into oracleInstances ( instanceName, hostname, controlfiledate)
        values (@instance, @hostname, @instance_startdate)
        -- select @instance, @hostname, @instance_startdate

      SET @sepPos1 = @sepPos1 + @sepPos2
      set @sepPos2 = charindex(@separator, substring(@strIn, @sepPos1,100))
    END
 

  END

  fetch next from oracleinstance_csr into @hostname, @strIn
           
END

close oracleinstance_csr
deallocate oracleinstance_csr


The table name oracleinstances i run this query it works for the controlfile date column. Now management decided to get oracle patch column. Linux team dumpped that value the same way they did before for the controlfile date column using the above query.


Now, my intension is how do i separate without creating a step in the ssis existing package. There is a another way we can try creating function, we can call function inside the query.

Thanks

Zahid
0
Mark WillsTopic AdvisorCommented:
Well, thats good. Looks like it is LF delimited - which I cannot see in the extracts so far - probably because they are excel

So, run your query again, this time, save results to TEXT  which is CTRL+SHIFT+T in SSMS just before you run the query, or, once the query has run, right click on top left of results and you can 'save as' then include the results file as an attachment
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' 
where h.hostname not in (select p.hostname  from oracleProductionHosts p) 
group by   instancename,q.value
order by instancename

Open in new window

0
Zahid AhamedDatabase AdministratorAuthor Commented:
Wills,

Here is the output attached.

Thanks
Output.txt
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Hi Wills,

Here i am attaching another two outputs, because i tried using this following query. I guess you will see the difference the two outputs:--

Query-1:
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'
where h.hostname not in (select p.hostname  from oracleProductionHosts p)
group by   instancename, q.value
order by q.value desc

Query-2:--
select o.instancename --ISNULL(o.instancename, '') as instancename,
,case when CHARINDEX('|',isnull(q.value,''))=0 then null else  ltrim(SUBSTRING(isnull(q.value,''),1,CHARINDEX('|',isnull(q.value,''))-1)) end as oracle_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 q.value desc
Query-1.txt
Query-2.txt
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Wills,

If you look closely the following sample output how i wanted to see the display.

Thank You.
sample.txt
0
Mark WillsTopic AdvisorCommented:
That output in #a42466184 iis exactly what I want. If you double click on it you will see line-feeds - we can use that.

We can use charindex  char(10)  as the start of string.

I will dig deeper to make very sure, and I understand what you are after....
1
Mark WillsTopic AdvisorCommented:
OK,

Would you mind trying :
select distinct o.[instancename] --ISNULL(o.instancename, '') as instancename
,case when charindex(o.instancename,q.[value]) > 0 
      then replace(replace(ltrim(
           substring(q.[value]
          ,charindex('|',q.[value],charindex(o.instancename,q.[value])+1)+1   -- start position
          ,charindex(char(10),q.[value] + char(10),charindex(o.instancename,q.[value])) - (charindex('|',q.[value],charindex(o.instancename,q.[value])+1)+1)))  -- length
          ,'(',''),')','')   -- replace brackets
      else '' end as Orcale_PSU 
--,q.[value] as Orcale_PSU_source                     -- take out comments to see bigger source string, and start pos of instancename 
--,case when CHARINDEX(o.instancename,q.[value]) > 0 then CHARINDEX(o.instancename,q.[value]) else 0 end as instancename_pos 
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' 
where h.hostname not in (select p.hostname  from oracleProductionHosts p) 
group by o.instancename, q.[value]
order by o.instancename

Open in new window

0
Zahid AhamedDatabase AdministratorAuthor Commented:
I tired but don't see any output in the oracle_psu column.

Thank You.
output.txt
0
Mark WillsTopic AdvisorCommented:
That's odd, time to remove those comments in front of :

  ,q.[value] as Orcale_PSU_source
  ,case when charindex(o.instancename,q.[value]) > 0 then charindex(o.instancename,q.[value]) else 0 end as instancename_pos


If some of that data is coming from elsewhere via SSIS, could be collation mis-match

Might need to write an inline function for you.... We can tell if instancename_pos always comes back as zero - means it cannot match on character sets (need to use COLLATE - but that would be 'tricky' in the query itself with all the charindex work.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Yes, instancename_pos is coming back as zero
0
Mark WillsTopic AdvisorCommented:
That is really disappointing. It works so well on my machine. Although, I imported your output.txt file rather than generated by the Linux guys...

So, lets try making it into a function...
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

Open in new window

And now we can use it...
select distinct o.[instancename] --ISNULL(o.instancename, '') as instancename
  ,dbo.udfGetPatchDetails(o.instancename,q.[value]) as Orcale_PSU
--,q.[value] as Orcale_PSU_source                     -- take out comments to see bigger source string, and start pos of instancename 
--,case when CHARINDEX(o.instancename,q.[value]) > 0 then CHARINDEX(o.instancename,q.[value]) else 0 end as instancename_pos 
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' 
where h.hostname not in (select p.hostname  from oracleProductionHosts p) 
group by o.instancename, q.[value]
order by o.instancename

Open in new window


Please let me know it works for you :) Not sure I want to convert the SP used in SSIS to an inline function...
1

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:
Oh, I had commented out AND q.fact = 'oracle_patch' before - that might have had something to do with no results - did you notice that ?

(I didnt have a q.fact, so was easier for me to comment out -should have picked that up earlier and corrected above)
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Excellant! Thank you very much for your hard working.

Now results are ok. May be i have to tell linux guy to dump the value perfect way for this. Except this following one everything is ok.

MTHEND           mthend   JAN2017  | mthend   JAN2017  | mthend   JAN2017  | mthend   JAN2017  | mthend   JAN2017  |

But i noticed one thing O606PTT should pull up JAN2017 but it is blank in the oracle_psu column

This R606CMOD value is null that is fine.
Output.txt
1
Mark WillsTopic AdvisorCommented:
>> O606PTT  it was NULL in the output.txt file I got from you to load in my machine - hopefully it is those pesky Linux guys :)

>> mthend is unusual the function isnt searching for multiple instances... so must not have LF - the function does put one at the end, so its picking up the first instancename and the LF at the end...  if you look at the output.txt in #a42466184 you will see no LF

So very happy we got it working - thanks for your patience.

cheers,
Mark
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Thanks for you big help! Appreciated.
0
Mark WillsTopic AdvisorCommented:
You accepted your own "thank you" as best answer. It really isnt an answer though. May I suggest you pick another ?
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Sorry my bad! I am going to contact with support team to give you best solution and points
0
Zahid AhamedDatabase AdministratorAuthor Commented:
I was rushing that's why I did mistake. Sorry(:
0
Mark WillsTopic AdvisorCommented:
Easy enough to do, especially in a rush. Absolutely no need to apologise - it is your prerogative.

And thanks again.... very much appreciated :)

I actually enjoyed the challenge :)
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Thanks again Mark.
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.