Date: Sat, 19 Jun 2004 15:50:05 +0530
From: "V i s h a l Kashyap @ [Sai Hertz And Control Systems]"
	<sank89@sancharnet.in>
Subject: Re: case stement when null
In-reply-to: <BAY8-F104oxRI8gcXIi0003bc86@hotmail.com>
To: Jaime Casanova <el_vigia_ec@hotmail.com>
Cc: pgsql-sql@postgresql.org
Reply-To: aspire420@hotpop.com
Message-id: <40D41355.6010803@sancharnet.in>
Organization: Sai Hertz And Control Systems Pvt Ltd
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
User-Agent: Mozilla Thunderbird 0.7 (Windows/20040616)
References: <BAY8-F104oxRI8gcXIi0003bc86@hotmail.com>

Dear Jaime ,

> CREATE OR REPLACE FUNCTION 
> "public"."rec_f_consultarplanificacionrubros" (smallint, smallint, 
> char(9), smallint, smallint, smallint, char(2)) RETURNS SETOF 
> "public"."rec_td_rubrosplanificados" AS '
>    select rub_codigo, cpa_valor, cpa_fechavencimiento
>         from rec_m_cuadropagosciclo
>         where ent_codigo = $1
>           and sec_codigo = $2
>           and ani_codigo = $3
>           and case $4
>                   when null then cic_codigo IN (select cic_codigo from 
> aca_m_cicloaniolectivo
>                                                        where 
> ent_codigo = $1
>                                                          and 
> sec_codigo = $2
>                                                          and 
> ani_codigo = $3)
>                   else cic_codigo = $4
>              end
> ' LANGUAGE 'sql'
>
> but when $4 is null the function executes the else part and what i 
> want to do is to execute the when null. Can you help me???
>
How about  this

coalesce($4,l  (select cic_codigo from aca_m_cicloaniolectivo
                                                       where ent_codigo 
= $1
                                                         and sec_codigo 
= $2
                                                         and ani_codigo 
= $3) )

It was a coincidence that I was also working on same kinda query thus I 
am also in R&D phase
more could be found at
http://www.varlena.com/varlena/GeneralBits/22.html

PostgreSQL documentation talks very less about this i.e with out any 
example.

I would submit a request along with example.

-- 
Regards,
Vishal Kashyap
Director / Lead Software Developer,
Sai Hertz And Control Systems Pvt Ltd,
http://saihertz.rediffblogs.com
Yahoo  IM: mailforvishal[ a t ]yahoo.com