Re: Reports3.0 deriving a column

Why don't you just use an outer join, and set the NULL VALUE to "Phone Unknown" ?? You don't need to make a function to make that lookup.

Regards
Peter H. Larsen (petlars_at_pip.dknet.dk) Oracle Consultant (not affiliated with Oracle)

On Fri, 30 Jan 1998 20:09:43 -0500, "Derek Licciardi" <kressilac_at_unidial.com> wrote:

>I am running a report and I would like to add a derived column from another
>table. I need to add a phone number to the report based on the store_id.
>The phone number comes from the storedet table. If the phone number is not
>found then I need to replace it with 'Phone Unknown'. Oh yeah and finally,
>the phone number is a composite field of
>'('||areacode||')'||phonenum||'x0000'. I keep getting a REP-1401 Fatal
>PL*SQL error when I try to run the form. I created a formula column to
>accomplish this and added the following code to the PL*SQL formula:
>
>function CF_telephone_nbrFormula return Char is
> v_store_phone char(18);
>begin
> select '('||areacode||')'||phonenum||'x0000' into v_store_phone
> from toolkit20.storedet
> where storenum = :store_id;
> if SQL%NOTFOUND then
> v_store_phone := 'Phone Unknown';
> end if;
> return (v_store_phone);
>end;
>
>Can someone please tell me where I am going wrong. Can't seem to get rid of
>the error.
>
>Derek Licciardi
>dlicciardi_at_tcipro.com
>
>
Received on Sat Jan 31 1998 - 00:00:00 CET