Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 19th, 2009, 04:26 PM
Registered User
 
Join Date: Dec 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default query pulling null records

Good afternoon,
i tried using having and this query still pulls records that have all nulls and not just records that have person_id in EPPSGlobalProd.dbo.person and in one of the 7 tables in the CASE statement. Does anyone know what I am doing wrong?




SELECT
DISTINCT p.person_id,
p.last_name,
p.first_name,
o.name as office_name, g.BU,
case
when

count(c.clean_cut_experience_id)> 0
then'X'
end clean_cut_experience,
case
when
count(r.cri_experience_id)> 0
then'X'
end cri_experience,
case
when

count(d. desorption_tech_experience_id)> 0
then'X'
end desorption_tech_experience,
case
when
count(w.dwm_wellsite_experience_id)>0
then'X'
end dwm_wellsite_experience,
case
when
count( f.fluids_experience_id)> 0
then'X'
end fluids_experience,
case
when
count(pressure_control_equipment_experience_id)> 0
then'X'
end pressure_control_equipment_experience,
case
when
count(rdf_experience_id)> 0
then'X'
end rdf_experience,
case
when
count(speedwell_experience_id)> 0
then'X'
end speedwell_experience,
case
when
count(completion_procedures_experience_id)> 0
then'X'
end completion_procedures_experience
FROM global_business_unit g
join LogITProd.dbo.office o on g.office_name = o.name
join LogITProd.dbo.security_user_office_assignment u on u.office_id = o.office_id
join EPPSGlobalProd.dbo.person p on p.ad_guid = u.ad_guid
leftouterJOIN clean_cut_experience c ON p.person_id = c.person_id
leftouterJOIN cri_experience r ON p.person_id = r.person_id
leftouterJOIN desorption_tech_experience d ON p.person_id = d.person_id
leftouterJOIN dwm_wellsite_experience w ON p.person_id = w.person_id
leftouterJOIN fluids_experience f ON p.person_id = f.person_id
leftouterJOIN pressure_control_equipment_experience s ON p.person_id = s.person_id
leftouterJOIN rdf_experience x ON p.person_id = x.person_id
leftouterJOIN speedwell_experience n ON p.person_id = n.person_id
leftouterJOIN completion_procedures_experience e ON p.person_id = e.person_id
--where o.name ='Aberdeen'
groupby p.person_id, p.last_name, p.first_name,o.name, g.BU



HAVING
Count(c.clean_cut_experience_id)
+ Count(r.cri_experience_id)
+ Count(d.desorption_tech_experience_id)
+ Count(w.dwm_wellsite_experience_id)
+ Count(f.fluids_experience_id)
+ Count(pressure_control_equipment_experience_id)
+ Count(rdf_experience_id)
+ Count(speedwell_experience_id)
+ Count(e.completion_procedures_experience_id)
IS NOT NULL
orderby o.name
 
Old March 20th, 2009, 06:19 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

I Think your query is ok, but count never return null, it returns 0 instead of null, so change "having" portion as follows

HAVINGCount(c.clean_cut_experience_id)
+ Count(r.cri_experience_id)
+ Count(d.desorption_tech_experience_id)
+ Count(w.dwm_wellsite_experience_id)
+ Count(f.fluids_experience_id)
+ Count(pressure_control_equipment_experience_id)
+ Count(rdf_experience_id)
+ Count(speedwell_experience_id)
+ Count(e.completion_procedures_experience_id) >0
__________________
urt

Help yourself by helping someone.

Last edited by urtrivedi; March 20th, 2009 at 06:23 AM..
 
Old March 20th, 2009, 09:10 AM
Registered User
 
Join Date: Dec 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default thank you

This is PERFECT!!!
thank you




Similar Threads
Thread Thread Starter Forum Replies Last Post
Pulling Records from Friday istcomnet ASP.NET 1.0 and 1.1 Basics 6 July 18th, 2008 04:18 PM
delete null records stealthdevil Access VBA 3 January 23rd, 2007 04:48 PM
Pulling records from the database debjanib ASP.NET 1.0 and 1.1 Professional 2 April 17th, 2006 09:03 AM
Date query pulling records from yesterday Justine Access 5 June 27th, 2004 03:45 PM
Hide NULL records chiraagb ADO.NET 2 May 29th, 2004 12:28 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.