Re: An Interesting Question ....

Ronak Bhatt wrote:
>
> Hi,
>
> I have a table as follows
>
> dept_code dept_name
> ------ --------
> 010 Default
> 020 Acct
> 030 Purchase
>
> I want dept_name from this table for a dept_code.
>
> If that dept_ccode doesn't exist in table, it should return dept_name
> of default
> deptcd i.e. '010'.
>
> Can U try ??
>
> Thanks in advance, Ronak
>
> PS : I don't need pl/sql. Only SQL statement.
>
> --
> Greatest Source Of Energy Is To Take Pride In What You Are Doing !!!!

An outer join will do the trick if dept_code is coming from another table, but it ends up ignoring your "default" entry in the table.
i.e.

     select ... (assuming your table is named dept_code)
            nvl(dept_codes.dept_code,'010') dept_code,
            nvl(dept_codes.dept_name,'Default') dept_name
     from other_tab,dept_codes
     where other_tab.dept_code = dept_codes.dept_code(+)

or in a more cumbersome manor if you don't want your "default" entry wasted.

     select dept_code,dept_name
     from dept_codes
     where dept_code = <the code you supply> (or the table join)
        or (dept_code = '010' AND
            not exists (select null from dept_code
                        where dept_code = <the code you supply>))

Ugggh.

Cheers,
Terry Received on Fri Sep 05 1997 - 00:00:00 CDT