T-SQL query

Hi,

In the following query i am getting valid value as well as invalid values. Basically i wanted to see kernelrelease data. This column is needed to display for reporting purposes.

select CASE WHEN ipaddress like '%10.4.%' or ipaddress like '%10.4.%' or ipaddress like '%10.4.%' or ipaddress like '%10.4.139%'  or ipaddress like '%10.%' THEN 'DEV'
WHEN ipaddress not like '%10.%' or ipaddress not like '%10.4%' or ipaddress not like '%10.4.%' or ipaddress not like '%10.4%'  or ipaddress not like '%10.%' THEN 'PROD'

END AS is_Prod_or_Dev

,   o.hostname
,      o.ipaddress
,      CASE WHEN o.hardware_servicetag like '%000%' THEN '' ELSE o.hardware_servicetag END as hardware_servicetag
,      o.oracle_sizeofd02
,      o.oracle_spacedused_d02
,      o.is_virtual
,      o.lsbdistdescription
,      o.uptime
,      o.productname
,   q.value as kernelrelease
,CASE o.is_virtual WHEN
'False' THEN o.processorcount/2 ELSE o.processorcount END AS processorcount
,      o.memorysize
,      o.appname
,   max(w.warrantyEndDate) as LeaseEndDate
,   m.value as crowdstrike
,   n.value as tibslastlogin
,   p.value as reportdate

from oraclehosts o
left join hostwarranty w on w.hostname = o.hostname Collate SQL_Latin1_General_CP1_CI_AS  
left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate'
left join puppetdb_certname_facts m on m.certname = o.hostname AND m.fact = 'crowdstrike'
left join puppetdb_certname_facts n on n.certname = o.hostname AND n.fact = 'tibslastlogin'
left join puppetdb_certname_facts q on n.certname = o.hostname AND q.fact = 'kernelrelease'

group by
    o.hostname
,      o.ipaddress
,      o.operatingsystem

,      o.hardware_servicetag
,      o.oracle_sizeofd02
,      o.oracle_spacedused_d02
,      o.is_virtual
,      o.lsbdistdescription
,      o.uptime
,      o.productname
,      o.processorcount
,      o.memorysize
,      o.appname
,m.value
,n.value
,p.value
,q.value


order by reportdate desc

Before add this kernelrelease i had 256 records now i am getting 8000 above records,


This is actual table, when i run then i get one single records.
select * from [dbo].[puppetdb_certname_facts] where fact = 'kernelrelease' and certname ='xxxxxx'

How can i calculate to get a single record. Please help!
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.

ste5anSenior DeveloperCommented:
Well, JOINs create products of rows, thus is not unexpected that this can happen. Depending on your data model this could be correct or indicating an error in your JOIN. So, without knowledge about your model this is hard to tell.

But there is an alias name error in your query (copy'n'paste I guess), so this may return the correct amount of rows:
 
SELECT   CASE WHEN    ipaddress LIKE '%10.4.%'
                   OR ipaddress LIKE '%10.4.%'
                   OR ipaddress LIKE '%10.4.%'
                   OR ipaddress LIKE '%10.4.139%'
                   OR ipaddress LIKE '%10.%' THEN 'DEV'
              WHEN    ipaddress NOT LIKE '%10.%'
                   OR ipaddress NOT LIKE '%10.4%'
                   OR ipaddress NOT LIKE '%10.4.%'
                   OR ipaddress NOT LIKE '%10.4%'
                   OR ipaddress NOT LIKE '%10.%' THEN 'PROD'
         END AS is_Prod_or_Dev ,
         o.hostname ,
         o.ipaddress ,
         CASE WHEN o.hardware_servicetag LIKE '%000%' THEN ''
              ELSE o.hardware_servicetag
         END AS hardware_servicetag ,
         o.oracle_sizeofd02 ,
         o.oracle_spacedused_d02 ,
         o.is_virtual ,
         o.lsbdistdescription ,
         o.uptime ,
         o.productname ,
         q.value AS kernelrelease ,
         CASE o.is_virtual
              WHEN 'False' THEN o.processorcount / 2
              ELSE o.processorcount
         END AS processorcount ,
         o.memorysize ,
         o.appname ,
         MAX(w.warrantyEndDate) AS LeaseEndDate ,
         m.value AS crowdstrike ,
         n.value AS tibslastlogin ,
         p.value AS reportdate
FROM     oraclehosts o
         LEFT JOIN hostwarranty w ON w.hostname = o.hostname COLLATE SQL_Latin1_General_CP1_CI_AS
         LEFT JOIN puppetdb_certname_facts p ON p.certname = o.hostname
                                                AND p.fact = 'reportdate'
         LEFT JOIN puppetdb_certname_facts m ON m.certname = o.hostname
                                                AND m.fact = 'crowdstrike'
         LEFT JOIN puppetdb_certname_facts n ON n.certname = o.hostname
                                                AND n.fact = 'tibslastlogin'
         LEFT JOIN puppetdb_certname_facts q ON q.certname = o.hostname
                                                AND q.fact = 'kernelrelease'
GROUP BY o.hostname ,
         o.ipaddress ,
         o.operatingsystem ,
         o.hardware_servicetag ,
         o.oracle_sizeofd02 ,
         o.oracle_spacedused_d02 ,
         o.is_virtual ,
         o.lsbdistdescription ,
         o.uptime ,
         o.productname ,
         o.processorcount ,
         o.memorysize ,
         o.appname ,
         m.value ,
         n.value ,
         p.value ,
         q.value;

Open in new window

You had  n.certname = o.hostname AND q.fact = 'kernelrelease' instead of q.certname = o.hostname AND q.fact = 'kernelrelease',
0

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
Zahid AhamedDatabase AdministratorAuthor Commented:
I getting record but if i use max(q.value) as kernelrelease then i get 256 records. i dont need max record. How could i achieve this value

The exact record should be

Hostname      fact                                              value
usd-vv-276.ccccc.net      kernelrelease      4.1.12-94.5.7.el7uek.x86_64
0
Mark WillsTopic AdvisorCommented:
Just a comment on the case... Should be
SELECT   CASE WHEN    ipaddress LIKE '%10.4.%'
                   OR ipaddress LIKE '%10.4.%'
                   OR ipaddress LIKE '%10.4.%'
                   OR ipaddress LIKE '%10.4.139%'
                   OR ipaddress LIKE '%10.%' THEN 'DEV'
         ELSE 'PROD' 
         END AS is_Prod_or_Dev ,

Open in new window


I think ste5an is on top of it, so will leave the rest to him....
He identified the incorrect alias and that would have returned all 'q' rows with  q.fact = 'kernelrelease'
0
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:
This is exact query i call a report everyday.... now management wanted to see the kernerrelease column data

select CASE WHEN ipaddress like '%10.4%' or ipaddress like '%10.4%' or ipaddress like '%10.4%' or ipaddress like '%10.4%'  or ipaddress like '%10.4.%' THEN 'DEV'
WHEN ipaddress not like '%10.4%' or ipaddress not like '%10.4%' or ipaddress not like '%10.4%' or ipaddress not like '%10.4%'  or ipaddress not like '%10.4%' THEN 'PROD'

END AS is_Prod_or_Dev

,   o.hostname
,      o.ipaddress
,      CASE WHEN o.hardware_servicetag like '%000%' THEN '' ELSE o.hardware_servicetag END as hardware_servicetag
,      o.oracle_sizeofd02
,      o.oracle_spacedused_d02
,      o.is_virtual
,      o.lsbdistdescription
,      o.uptime
,      o.productname
---,   q.value as kernelrelease
,   CASE o.is_virtual WHEN 'False' THEN o.processorcount/2 ELSE o.processorcount END AS processorcount
,       o.memorysize
,      o.appname
,   max(w.warrantyEndDate) as LeaseEndDate
,   m.value as crowdstrike
,   n.value as tibslastlogin
,   p.value as reportdate

from oraclehosts o
left join hostwarranty w on w.hostname = o.hostname Collate SQL_Latin1_General_CP1_CI_AS  
left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate'
left join puppetdb_certname_facts m on m.certname = o.hostname AND m.fact = 'crowdstrike'
left join puppetdb_certname_facts n on n.certname = o.hostname AND n.fact = 'tibslastlogin'
---left join puppetdb_certname_facts q on n.certname = o.hostname AND q.fact = 'kernelrelease'


group by
       o.hostname
,      o.ipaddress
,      o.operatingsystem
,      o.hardware_servicetag
,      o.oracle_sizeofd02
,      o.oracle_spacedused_d02
,      o.is_virtual
,      o.lsbdistdescription
,      o.uptime
,      o.productname
,      o.processorcount
,      o.memorysize
,      o.appname
,      m.value
,      n.value
,      p.value
--,    q.value


order by reportdate desc
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Problem is now if add q.value as kernelrelease then i am getting that exact data as well as unreleated data.



certname
usd--xxx..net      kernelrelease  
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease

value
kernelrelease
2.6.18-238.el5
2.6.18-274.el5
2.6.18-348.el5
2.6.32-358.14.1.el6.x86_64
2.6.32-431.11.2.el6.x86_64
2.6.32-504.30.3.el6.x86_64
2.6.32-642.13.1.el6.x86_64
2.6.32-642.4.2.el6.x86_64
2.6.32-696.13.2.el6.x86_64
2.6.32-696.6.3.el6.x86_64
2.6.39-200.24.1.el6uek.x86_64
2.6.39-400.215.10.el6uek.x86_64
2.6.39-400.215.3.el6uek.x86_64
2.6.39-400.250.11.el6uek.x86_64
3.10.0-327.18.2.el7.x86_64
3.10.0-327.el7.x86_64
3.10.0-693.11.1.el7.x86_64
3.10.0-693.2.2.el7.x86_64
3.10.0-693.5.2.el7.x86_64
3.8.13-118.10.2.el7uek.x86_64
3.8.13-118.14.2.el6uek.x86_64
3.8.13-118.4.1.el7uek.x86_64
4.1.12-103.6.1.el7uek.x86_64
4.1.12-103.7.3.el6uek.x86_64
4.1.12-103.7.3.el7uek.x86_64
4.1.12-61.1.17.el6uek.x86_64
4.1.12-61.40.1.el6uek.x86_64
4.1.12-94.1.8.el6uek.x86_64
4.1.12-94.1.8.el7uek.x86_64
4.1.12-94.3.9.el7uek.x86_64
4.1.12-94.5.7.el6uek.x86_64
4.1.12-94.5.7.el7uek.x86_64
4.1.12-94.6.4.el6uek.x86_64
4.9.0-3-amd64
4.9.41-v7+


i should get this 4.1.12-94.5.7.el7uek.x86_64

certname      fact      value
usd--xxx..net      kernelrelease      4.1.12-94.5.7.el7uek.x86_64

i guess it is clear now.
0
Mark WillsTopic AdvisorCommented:
Yep,  Look at the bold/underlined table alias...

instead of :
left join puppetdb_certname_facts q on n.certname = o.hostname AND q.fact = 'kernelrelease'


use :
left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'kernelrelease'
1
Zahid AhamedDatabase AdministratorAuthor Commented:
Thanks a lot Mark, It worked for me.
0
ste5anSenior DeveloperCommented:
=8)
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
T-SQL

From novice to tech pro — start learning today.