Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: help with join
On Wed, 28 Jul 2004 19:35:07 GMT, "Root" <root_at_aol.com> wrote:
>
>"Dave" <recneps.w.divad_at_elcaro.moc> wrote in message
>news:RqQNc.37$tg.34_at_news.oracle.com...
>>
>>
>> Root wrote:
>> > How can I do this in a single select statement?
>> >
>> > Table A
>> >
>> > Name ID
>> > Eric 1
>> > Jeff 2
>> > Ted 3
>> > Tad 4
>> >
>> > Table B
>> >
>> > Id sales
>> > 1 10,000
>> > 1 12,000
>> > 3 10,000
>> >
>> > Result
>> >
>> > Name Id Sales
>> > Eric 1 10,000
>> > Eric 1 12,000
>> > Ted 3 10,000
>> > Jeff 2 null
>> > Tad 4 null
>> >
>> > Anyone know how to perform a query like this in a single select
>statement?
>>
>>
>> Assuming the horizontal spacing is unimportant, I get:
>>
>> select name, a.id, nvl(to_char(sales,'99,999'),'null') "Sales"
>> from a,b where a.id = b.id(+) order by a.id*sales;
>>
>> NAME ID Sales
>> ---- ---------- -------
>> Eric 1 10,000
>> Eric 1 12,000
>> Ted 3 10,000
>> Jeff 2 null
>> Tad 4 null
>>
>> Dave.
>
>nvl takes two arguments, returns the first if they are not equal?
>
the nvl algorithm is
if first argument is null
then
return <second arg>
else
return <first arg>
end if
-- Sybrand Bakker, Senior Oracle DBAReceived on Wed Jul 28 2004 - 15:13:42 CDT