Home » SQL & PL/SQL » SQL & PL/SQL » DISPLAY VALUES IF NULL ALSO (important)
DISPLAY VALUES IF NULL ALSO (important) [message #194258] Thu, 21 September 2006 05:56 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear Experts,

I have a sql statement like this

select a.cno no,a.cdate date,b.cardid id,c.coeid coe,c.coevalue value from cthd a,car b,coe c where a.ref=b.ref and b.coeid=c.coeid.

In ths above I am able to display only if the coeid is in coe table if not I am unable to display the records. I need your expert help in this regard. Even though if the coeid is null i want to null or 0 in the column provided.

My output can be like this

cno cdate cardid coeid coevalue
1 10-apr-03 10 105 10000
2 10-apr-03 20
3 12-may-03 30
4 14-jun-03 40 100 12000

Can any expert help me in this regard?
Re: DISPLAY VALUES IF NULL ALSO (important) [message #194259 is a reply to message #194258] Thu, 21 September 2006 06:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not really an expert level question I'm afraid.
You just need to Outer Join to COE.

SELECT a.cno no
       ,a.cdate date
       ,b.cardid id
       ,c.coeid coe
       ,c.coevalue value 
FROM    cthd a
       ,car b
       ,coe c 
WHERE   a.ref=b.ref 
AND     b.coeid=c.coeid(+)
Re: DISPLAY VALUES IF NULL ALSO (important) [message #194260 is a reply to message #194258] Thu, 21 September 2006 06:01 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Try this

select a.cno no,a.cdate date,b.cardid id,c.coeid coe,c.coevalue value from cthd a,car b,coe c where a.ref=b.ref and b.coeid(+)=c.coeid


Sandy

Re: DISPLAY VALUES IF NULL ALSO (important) [message #194266 is a reply to message #194260] Thu, 21 September 2006 06:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good grief - an echo.
Cool
Re: DISPLAY VALUES IF NULL ALSO (important) [message #194267 is a reply to message #194266] Thu, 21 September 2006 06:16 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

I had opened to reply and before i submits you posted...

but in your query
Quote:

SELECT a.cno no
,a.cdate date
,b.cardid id
,c.coeid coe
,c.coevalue value
FROM cthd a
,car b
,coe c
WHERE a.ref=b.ref
AND b.coeid=c.coeid(+)


it will take all values from coe while he needs from car.
is it so?? Will this will be the o/p

Quote:

cno cdate cardid coeid coevalue
1 10-apr-03 10 105 10000
2 10-apr-03 20
3 12-may-03 30
4 14-jun-03 40 100 12000



Sandy Rolling Eyes

[Updated on: Thu, 21 September 2006 06:19]

Report message to a moderator

Re: DISPLAY VALUES IF NULL ALSO (important) [message #194271 is a reply to message #194267] Thu, 21 September 2006 06:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good point - I missed that difference. Embarassed

I think I've got it right (but then I would, wouldn't I Cool )

The results posted in the op show missing data from table COE, so I reckon we need to be outer joining to that, not outer joining to CAR.

Hopefully the OP will let us know which he wanted.
Re: DISPLAY VALUES IF NULL ALSO (important) [message #194272 is a reply to message #194271] Thu, 21 September 2006 06:26 Go to previous message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

yeah, but i was guessing from his post that what he wants.

Sandy Smile

[Updated on: Thu, 21 September 2006 06:27]

Report message to a moderator

Previous Topic: GROUP BY
Next Topic: count of each data for column
Goto Forum:
  


Current Time: Fri Dec 09 13:52:52 CST 2016

Total time taken to generate the page: 0.12725 seconds