[SQL] default value for select?
I want to update a column in myTable. The value this column is set to depends on a nested select statement which sometimes returns 0 rows instead of 1. This is a problem since the column I'm trying to update is set to refuse nulls. Here's a sample: update myTable set myColumn = (Select altColumn from altTable where altColumn != 'XXX' limit 1) where myColumn = 'XXX'; MyColumn cannot accept nulls, but sometimes "Select altColumn ..." returns 0 rows, and thus, the query fails. Is there a way to set a default value to be inserted into myColumn if and when "select altColumn ..." returns zero rows? Mark begin:vcard fn:Mark Fenbers n:Fenbers;Mark org:DoC/NOAA/NWS/OHRFC adr:;;1901 South SR 134;Wilmington;OH;45177-9708;USA email;internet:[EMAIL PROTECTED] title:Sr. HAS Meteorologist tel;work:937-383-0430 x246 x-mozilla-html:TRUE url:http://weather.gov/ohrfc version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] default value for select?
On 5/9/05, Mark Fenbers <[EMAIL PROTECTED]> wrote: > I want to update a column in myTable. The value this column is set to > depends on a nested select statement which sometimes returns 0 rows instead > of 1. This is a problem since the column I'm trying to update is set to > refuse nulls. Here's a sample: > > update myTable set myColumn = (Select altColumn from altTable where > altColumn != 'XXX' limit 1) where myColumn = 'XXX'; > > MyColumn cannot accept nulls, but sometimes "Select altColumn ..." returns > 0 rows, and thus, the query fails. > > Is there a way to set a default value to be inserted into myColumn if and > when "select altColumn ..." returns zero rows? > > Mark Mark, You can work around this by using a CASE statement. In this case, test for a NULL from your subquery. This is not elegant at all, but it should do what you are wanting. update myTable set myColumn = (CASE WHEN (Select altColumn from altTable where altColumn != 'XXX' limit 1) IS NULL THEN 'some default value' ELSE (Select altColumn from altTable where altColumn != 'XXX' limit 1) END) where myColumn = 'XXX'; Hope this helps... Tony ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] default value for select?
On Mon, 09 May 2005 12:57:41 -0400, Mark Fenbers wrote > I want to update a column in myTable. The value this column is set > todepends on a nested select statement which sometimes returns 0 > rowsinstead of 1. This is a problem since the column I'm trying to > updateis set to refuse nulls. Here's a sample: > > update myTable set myColumn = (Select altColumn from altTable > wherealtColumn != 'XXX' limit 1) where myColumn = 'XXX'; > > MyColumn cannot accept nulls, but sometimes "Select altColumn > ..."returns 0 rows, and thus, the query fails. > > Is there a way to set a default value to be inserted into myColumn > ifand when "select altColumn ..." returns zero rows? > > Mark Mark, I do not know if it will work but I would try the COALESCE function. http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html Kind Regards, Keith ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] default value for select?
I want to update a column in myTable. The value this column is set to depends on a nested select statement which sometimes returns 0 rows instead of 1. This is a problem since the column I'm trying to update is set to refuse nulls. Here's a sample: update myTable set myColumn = (Select altColumn from altTable where altColumn != 'XXX' limit 1) where myColumn = 'XXX'; MyColumn cannot accept nulls, but sometimes "Select altColumn ..." returns 0 rows, and thus, the query fails. Is there a way to set a default value to be inserted into myColumn if and when "select altColumn ..." returns zero rows? COALESCE(value [, ...]) The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. This is often useful to substitute a default value for null values when data is retrieved for display, for example: SELECT COALESCE(description, short_description, '(none)') ... Like a CASE expression, COALESCE will not evaluate arguments that are not needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] diff value retuns, debug mode and play mode
it is about null problem
declare
res numeric(15,2);
_pida integer[4]; < Problem is here must be ---> pida
integer[4]='{}';
_pid integer;
_rec record;
..
"ALÝ ÇELÝK" <[EMAIL PROTECTED]>, haber iletisinde þunlarý
yazdý:[EMAIL PROTECTED]
>
> I have a postgresql function at the bottom , when i run at EMS POSTGRESQL
> debug mode with F8 step by step. Its work great but when i use with play
> button it returns null what is the different about debug mode and run
> mode.
>
> BEST REGARDS.
>
>
> CREATE OR REPLACE FUNCTION "public"."allocatedamount" (varchar, integer)
> RETURNS numeric AS
> $body$
> /* allocatedamount (varchar,integer)
> raporlarda kullan?lmak ?zere belirli bir sold un nekadar?n?n allocate
> edildi?ini bulmak i?in
> $1: Varchar: Tablo ismi.(SOCIAL,ACCOMODATION,vs)
> $2: Integer: Soldid.
> */
> declare
> res numeric(15,2);
> _pida integer[4]; _pid integer;
> _rec record;
> begin
> if $1 = 'REGISTRATION' then begin
> _pid:=1;
> for _rec in select personaldetails.id from personaldetails where
> personaldetails.id=$2 or personaldetails.masterid=$2 loop
> _pida[_pid]:=_rec.id;
> _pid:=_pid+1;
> end loop;
>
> select into res sum(allocatedpayments.amount) from allocatedpayments
> where allocatedpayments.tablename='REGISTRATION' and
> allocatedpayments.tableid = ANY (_pida::integer[]);
> res=888;
> end;
> ELSIF $1 = 'MASTER-REGISTRATION' then
> select into res sum(allocatedpayments.amount) from allocatedpayments
> where allocatedpayments.tablename='REGISTRATION' and
> allocatedpayments.tableid=$2;
>
>
> ELSIF $1 = 'ACC-REGISTRATION' then
> select into res sum(allocatedpayments.amount) from allocatedpayments
> where allocatedpayments.tablename='REGISTRATION' and
> allocatedpayments.tableid=$2;
>
>
> else
> select into res sum(allocatedpayments.amount) from allocatedpayments
> where allocatedpayments.tablename=$1 and allocatedpayments.tableid=$2;
>
> end if;
>
> if res is null then
> res:=0;
> end if;
>
>
> return res;
>
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
