Re: Oracle For Fun

"VC" <boston103_at_hotmail.com> wrote in message news:<jSLTb.208700$I06.2315548_at_attbi_s01>...
> "Tony" <andrewst_at_onetel.net.uk> wrote in message
> news:c0e3f26e.0402030234.2ba855b4_at_posting.google.com...
> > "VC" <boston103_at_hotmail.com> wrote in message
> news:<YBCTb.205971$I06.2284013_at_attbi_s01>...
> > Well, to be pedantic, it doesn't say that NULL is not valid, it says
> > its validity is unknown (which is correct for a NULL):
>
> The previous poster said 'does not handle null'. If he said that the
> function returns NULL, I'd most certainly not argue with that.
>
> >
> > 1 declare
> > 2 l_str varchar2(20);
> > 3 b boolean;
> > 4 begin
> > 5 for l in (select x from t1) loop
> > 6 b := f1(l.x);
> > 7 if b then l_str := 'valid';
> > 8 elsif not b then l_str := 'not valid';
> > 9 else l_str := 'unknown'; end if;
> > 10 dbms_output.put_line('<'||l.x||'>'||l_str);
> > 11 end loop;
> > 12* end;
> > tandrews_at_IDEV
> > SQL> /
> > <123-456-7890>valid
> > <123-456-78j0>not valid
> > <>unknown
> >
> > PL/SQL procedure successfully completed.
> >
> > My version (translate3) with the NVL makes NULL invalid.
>
> In my opinion, it's an unnecessary step since the function application as
> "if b then l_str := 'valid'; else l_str := 'invalid'; end if;' does the
> same.
> Besides, what if one wanted to check if the column being checked does
> contain a NULL ? In your implmentation, you've lost this information by
> merging NULL and invalid.

I'm not arguing mine is better, just different. Which is better depends on what you WANT.

>
> >This could
> > also be construed as correct, since in Oracle NULL and '' are
> > indistinguishable, and '' is certainly not of the correct form.
>
> Regarding 'correct', I disagree completetly: there is no logical connection
> between "since in Oracle NULL and '' are
> indistinguishable" and "certainly not of the correct form". Hint, you
> have first to define the "correct form".

I thought the correct form was '###-###-####' where # is any digit. The empty string isn't of THAT form is it? Received on Tue Feb 03 2004 - 08:09:15 CST