Re: Help with dynamic case select?

kimberly.shaffer_at_gmail.com wrote:

> select ta.field_name1,
> CASE ta.fieldname2
> select tb.fieldname2, tb.fieldname3 from TBL_b tb
> when ta.fieldname2 = tb.fieldname2 then tb.fieldname3
> else 'unknown, please look up'
> end as "Field_Name"
> from
> TBL_a ta
> ;
>
> I am trying to pull a case select statement without having to type a
> long huge when...else statement. Thanks!
> Is this possible? Can someone point me towards what to look for?

Your CASE is hard to understand, but there's probably a shortcut for it, COALESCE:
COALESCE((select tb.fieldname3 from TBL_b tb

           where ta.fieldname2 = tb.fieldname2)
         , 'unknown, please look up') as "Field_Name"

or write an Outer Join:
select

   ta.field_name1,
   coalesce(tb.fieldname3, 'unknown, please look up') as "Field_Name" from TBL_a ta left join TBL_b tb
on ta.fieldname2 = tb.fieldname2

Dieter Received on Tue Oct 23 2007 - 02:29:09 CDT