Thread: case stement when null

hi all,

Well i rethink my design 'cause the other was too slow. but all solutions 
involves new problems... now my problem is with a function of this type:


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


Thanx in advance,

Jaime Casanova

_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE* 
http://join.msn.com/?page=features/junkmail

--- Jaime Casanova <__> wrote:
> hi all,
> 
> Well i rethink my design 'cause the other was too slow. but all
> solutions 
> involves new problems... now my problem is with a function of this
> type:
> 
> 
> 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???

try the form:

case when $4 is null   then cic_codigo IN        (select cic_codigo           from aca_m_cicloaniolectivo
whereent_codigo = $1 and                sec_codigo = $2 and                ani_codigo = $3)   else cic_codigo = $4
 
end

[]

regards,



=====
Riccardo G. Facchini

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