Re: newbie's question about 'decode'

biffled <wlhansonNOwlSPAM_at_powersurfr.com.invalid> wrote in message news:23478eac.2627bd6a_at_usw-ex0103-019.remarq.com...
> Hi, I have Oracle 7.2 loaded on my PC for a course I'm taking,
> and I have questions about using DECODE within a select query
> used to generate reports. I get the concept that DECODE acts as
> a conditional function (i.e., an if-then-else statement) that
> can be used to format column results for a report. If a column
> contains a null value, you can use DECODE to change the values
> to say 'No Value' for, say, col_test, in the report. And that
> works for me. But I have a situation where I have to display
> different output for col_test depending on what's contained in,
> say, col_type (not a primary key, by the way). In other words,
> for a table, all the values in col_test may be null but if the
> value in col_type is 'A', the report should state 'No Value' for
> the value in col_test or display a 0 (zero) for a col-type
> of 'B'. Can you nest DECODE's within a select statement as a way
> to test for the values in col_type (i.e., decode(col_test, decode
> (col_type, NULL....)))? I've had unpredictable results so far
> with all the results in col_test changed and even results in
> col_type changed. Any insights would be greatly appreciated, and
> thanks.

Use the nvl function to assign a value if null.

Try:

decode(col_type

     ,'A',nvl(col_test,'No Value')
     ,'B',nvl(col_test,0)
     ,col_test)

although I think that all the return expressions in a decode statement need to be the same type so you might need to do something like:

decode(col_type

     ,'A',nvl(to_char(col_test),'No Value')
     ,'B',nvl(to_char(col_test),'0')
     ,to_char(col_test))

cheers,
John. Received on Mon Jun 19 2000 - 00:00:00 CDT