Handling NULL values

Dev Shed Forums Sponsor:

January 23rd, 2013, 10:02 AM

fee1975

Contributing User

Join Date: Feb 2008

Posts: 76

Time spent in forums: 1 Day 1 h 42 m 42 sec
Reputation Power: 6

Handling NULL values

I have 2 tables, AFF_TEMP and COUNTY
AFF_TEMP has the following columns FNAME, LNAME, EMAIL and COUNTY
COUNTY has 2 columns COUNTY_ID and CNAME

Both tables have the following test data
AFF_TEMP
Joe, Bloggs, joe@gmail.com, ''
Ann, Bloggs, anne@gmail.com,Donegal

and COUNTY column in AFF_TEMP can contain a NULL value

County table has the following Test data,
1, Dublin
2, Donegal
3, Tipperary,
4, Galway

I am trying to select the following from both tables FNAME, LNAME, EMAIL, COUNTY_ID.
Tried the following queries
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A LEFT OUTER JOIN COUNTY C ON A.COUNTY=C.CNAME
OR (A.COUNTY IS NULL)

select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A, COUNTY C
WHERE C.CNAME IN (SELECT UPPER(A.COUNTY) FROM TEMP_AFF A)
or A.COUNTY IS null
but cant get it to work....
This should be my output
Joe, Bloggs, joe@gmail.com, null
Ann, Bloggs, anne@gmail.com,6

Appreciate any help on this!

Last edited by fee1975 : January 23rd, 2013 at 10:18 AM.

January 23rd, 2013, 03:40 PM

spacebar208

Contributing User

Join Date: Apr 2012

Location: spaceBAR Central

Posts: 203

Time spent in forums: 2 Days 16 h 10 sec
Reputation Power: 41

Post the results of these queries:

Code:

select *
 from  temp_aff

select *
 from  county

select a.fname, a.lname, a.email, nvl( c.county_id, '** No Match found' ) as county_id
 from  temp_aff a left outer join county c on a.county  =  c.cname

select a.fname, a.lname, a.email, nvl( c.county_id, '** No Match found' ) as county_id
 from  temp_aff a left outer join county c on trim( lower( a.county ) )  =  trim( lower( c.cname ) )

January 24th, 2013, 03:45 AM

fee1975

Contributing User

Join Date: Feb 2008

Posts: 76

Time spent in forums: 1 Day 1 h 42 m 42 sec
Reputation Power: 6

Results from first Query

Code:

Corcoran	Sean	sean@dit.ie 	095-742842 HP			
Fallon	 Declan	declan.fallon@hotmail.co.uk	091-742842	HP			
Douglas	Lorna	lorna.douglas@gmail.com	091-787904				Limerick

Results from second query

Code:

1	TIPPERARY
2	LOUTH
3	CLARE
4	DUBLIN
5	GALWAY
6	LIMERICK

Results form 3rd query

Code:

Declan	Fallon Declan.fallon@hotmail.co.uk	** No Match found
Sean	Corcoran	sean@dit.ie	** No Match found
Lorna	Douglas	lorna.douglas@gmail.com	** No Match found

And 4th query results

Code:

Lorna	Douglas	lorna.douglas@gmail.com	6
Declan	Fallon	declan.fallon@hotmail.co.uk	** No Match found
Sean	Corcoran	sean@dit.ie	** No Match found

January 24th, 2013, 06:04 AM

fee1975

Contributing User

Join Date: Feb 2008

Posts: 76

Time spent in forums: 1 Day 1 h 42 m 42 sec
Reputation Power: 6

Got it working thanks

Handling NULL values

Developer Shed Advertisers and Affiliates