Обсуждение: How to pickup null values in SQL Language?

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.

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.?

Tahira Aslam

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   <kleptog@svana.org>   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

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tahira Aslam
Sent: Thursday, May 30, 2002 7:42 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to pickup null values in SQL Language?

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.

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.? 

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@svana.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

On Thu, 30 May 2002, Tahira Aslam wrote:

>  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;

If you want a part of the string to become NONE if null, you need
to coalesce each part (I'm dropping the casts for now):
select coalesce(MyView."Document_ID", 'None') || ',' ||
 coalesce(MyView."First_Name", 'None') || ...

thanks Mr.Stephen your solution solved my problem. But now I want to use COALASCE function in a function which uses 'plpgsql' language.(function needs to perform same functionality as it was in SQL language)

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

But it gives error "Bad date external representation 'NONE' "....

is it some syntax mistake or what ?

  Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:

On Thu, 30 May 2002, Tahira Aslam wrote:

> 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;

If you want a part of the string to become NONE if null, you need
to coalesce each part (I'm dropping the casts for now):
select coalesce(MyView."Document_ID", 'None') || ',' ||
coalesce(MyView."First_Name", 'None') || ...

On Thu, 30 May 2002, Tahira Aslam wrote:

>
>  thanks Mr.Stephen your solution solved my problem. But now I want to
> use COALASCE function in a function which uses 'plpgsql'
> language.(function needs to perform same functionality as it was in
> SQL language)

> Select cast(MyView."Document_ID" as
>  Text)||','||cast(MyView."First_Name" as
>  Text)||','||cast(MyView."Last_Name" as
>  Text)||','||Coalesce(cast(MyView."Sent_Date" as Text),'NONE') From "MyView"
>  Where "Document_ID"=$1;
> Language 'plpgsql';
>
> But it gives error "Bad date external representation 'NONE' "....

Well, the above isn't legal plpgsql really (you'd need to be using
two quotes around NONE and there's a missing begin/end, I assume
you're just not showing those).

Hmm, on current sources similar structures with coalesce seem to work as
long as the cast is done.

Can you give enough of the schema to recreate the specifics of your
situation (table structure, view structure and the function).

 
> Select cast(MyView."Document_ID" as
> Text)||','||cast(MyView."First_Name" as
> Text)||','||cast(MyView."Last_Name" as
> Text)||','||Coalesce(cast(MyView."Sent_Date" as Text),'NONE') From "MyView"
> Where "Document_ID"=$1;
> Language 'plpgsql';
>
> But it gives error "Bad date external representation 'NONE' "....

Well, the above isn't legal plpgsql really (you'd need to be using
two quotes around NONE and there's a missing begin/end, I assume
you're just not showing those).

:)  Actually i didn't feel the need to write whole function. This function worked fine until there came a record with one empty field, and it returned me nothing.I feel the need to replace the Null fields with 'NONE' value...so that concatenated string should return me record. (It returns nothing if there is a field with no value inside it ..I mean empty field.)