• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 833
  • Last Modified:

CASE Statement, IS NOT NULL. SQL Server 2008

Hello Experts,

I'm writing an SQL statement to create a temporary table for me to run a further query on. My objective is to create an owner id column. Now it is pulling in all the ID's required from the joined tables, but also many rows with NULL to the temporary table.

Could I get some assistance with only populating the table where the column IS NOT NULL, Id's only?

use conv

DECLARE @OwnerLookup table (
OwnerID Char(50)
)

insert into @OwnerLookup (OwnerID)

select
CASE
	WHEN doc_res_vers_Ptcl._webrUnique_ID IS NOT NULL THEN doc_res_vers_Ptcl._webrUnique_ID
	WHEN doc_res_vers_Site._webrUnique_ID IS NOT NULL THEN doc_res_vers_Site._webrUnique_ID
	WHEN doc_res_vers_Cmpy.ID IS NOT NULL THEN doc_res_vers_Cmpy.ID
	WHEN doc_res_vers_Person.ID IS NOT NULL THEN doc_res_vers_Person.ID
	--WHEN doc_res_vers_LegAct.ID IS NOT NULL THEN doc_res_vers_LegAct.ID
END as Owning_ID

from	_Document doc

		left outer join	_Resource as doc_res 
		on				doc.oid = doc_res.oid

		left outer join _VersionedResource as doc_res_vers 
		on				doc_res.oid = doc_res_vers.oid

		left outer join _Person as doc_res_person
		on				doc_res.owner = doc_res_person.oid

		left outer join __Protocol as doc_res_vers_Ptcl 
		on				doc_res_vers.owningEntity = doc_res_vers_Ptcl.oid

		left outer join __Site as doc_res_vers_Site
		on				doc_res_vers.owningEntity = doc_res_vers_site.oid

		left outer join _Company as doc_res_vers_cmpy
		on				doc_res_vers.owningEntity = doc_res_vers_cmpy.oid
		
		left outer join __LegacyActivity as doc_res_vers_LegAct
		on				doc_res_vers.owningEntity = doc_res_vers_LegAct.oid
		
		left outer join _Person as doc_res_vers_Person
		on				doc_res_vers.owningEntity = doc_res_vers_Person.oid

Open in new window

0
BIT_BOPPER
Asked:
BIT_BOPPER
  • 2
  • 2
1 Solution
 
mankowitzCommented:
can you put a line at the end of the query

HAVING Owning_ID IS NOT NULL;

Alternatively, maybe you need to tweak all those outer joins to exclude the blank ID's
0
 
BIT_BOPPERAuthor Commented:
Hi mankowits,

Invalid column name 'Owning_ID'.

I have tried this with the where clause, though I will look into the joins
0
 
Bhavesh ShahLead AnalysistCommented:
try with this....

Where doc_res.oid IS NULL

Open in new window

0
 
Bhavesh ShahLead AnalysistCommented:
above didnt worked then try this.
this will surely works...

--use conv

DECLARE @OwnerLookup table (
OwnerID Char(50)
)

insert into @OwnerLookup (OwnerID)

Select Owning_ID
From
(
select
CASE
	WHEN doc_res_vers_Ptcl._webrUnique_ID IS NOT NULL THEN doc_res_vers_Ptcl._webrUnique_ID
	WHEN doc_res_vers_Site._webrUnique_ID IS NOT NULL THEN doc_res_vers_Site._webrUnique_ID
	WHEN doc_res_vers_Cmpy.ID IS NOT NULL THEN doc_res_vers_Cmpy.ID
	WHEN doc_res_vers_Person.ID IS NOT NULL THEN doc_res_vers_Person.ID
	--WHEN doc_res_vers_LegAct.ID IS NOT NULL THEN doc_res_vers_LegAct.ID
END as Owning_ID

from	_Document doc

		left outer join	_Resource as doc_res 
		on				doc.oid = doc_res.oid

		left outer join _VersionedResource as doc_res_vers 
		on				doc_res.oid = doc_res_vers.oid

		left outer join _Person as doc_res_person
		on				doc_res.owner = doc_res_person.oid

		left outer join __Protocol as doc_res_vers_Ptcl 
		on				doc_res_vers.owningEntity = doc_res_vers_Ptcl.oid

		left outer join __Site as doc_res_vers_Site
		on				doc_res_vers.owningEntity = doc_res_vers_site.oid

		left outer join _Company as doc_res_vers_cmpy
		on				doc_res_vers.owningEntity = doc_res_vers_cmpy.oid
		
		left outer join __LegacyActivity as doc_res_vers_LegAct
		on				doc_res_vers.owningEntity = doc_res_vers_LegAct.oid
		
		left outer join _Person as doc_res_vers_Person
		on				doc_res_vers.owningEntity = doc_res_vers_Person.oid
		
) A
WHERE Owning_ID IS NOT NULL

Open in new window

0
 
BIT_BOPPERAuthor Commented:
Worked perfectly, thank you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now