Sybrand Bakker wrote:
> 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
But also look at the NVL2 function and how it works.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Wed Jul 28 2004 - 23:23:28 CDT
