Re: How to pickup null values in SQL Language?

I tried coalesce function ....But as u see I am using concatenated string,so it didn't worked .I wrote
CREATE FUNCTION MyFunction() RETURNS SETOF Text AS '

Select Coalesce(cast(MyView."Document_ID" as Text)||','||cast(MyView."First_Name" as Text)||','||cast(MyView."Last_Name" as Text)||','||cast(MyView."Sent_Date" as Text),'NONE') From "MyView" Where "Document_ID"=$1;

' LANGUAGE 'SQL'

When it found an empty field It replaced the whole concatenated string with only "NONE".

Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote: On Thu, May 30, 2002 at 04:42:14AM -0700, Tahira Aslam wrote:
>
> I have written this function in SQL Language in postgresql 7.2.
>
> CREATE FUNCTION MyFunction() RETURNS SETOF Text AS '
>
> Select MyView."Document_ID"||','||MyView."First_Name"||','||MyView."Last_Name"||','||MyView."Sent_Date" "From MyView" Where "Document_ID"=$1;
>
> ' LANGUAGE 'SQL';
>
> It worked fine...returning me multiple records of concatenated
> string,until there came a record where "Sent_Date" field was Null.It
> returned nothing then.

Lookup the coalesce function.

> I want to put a check to set null values equal to zero(namely to set
> "sent_Date"=0 If it is Null) , but looks like SQL does not support "IF
> THEN ELSE".plz let me know how can i put that check in this function.?

Lookup CASE/WHEN/THEN/ELSE/END.

HTH,
--
Martijn van Oosterhout http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America

---------------------------------
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup