Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: help with join

Re: help with join

From: Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl>
Date: Wed, 28 Jul 2004 22:13:42 +0200
Message-ID: <642gg0dive51g5tlpu3979di2eot7s1sqq@4ax.com>


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 DBA
Received on Wed Jul 28 2004 - 15:13:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US