pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcase stement when null
3+ messages / 3 participants
[nested] [flat]
* case stement when null
@ 2004-06-18 21:38 Jaime Casanova <el_vigia_ec@hotmail.com>
2004-06-19 06:21 ` Re: case stement when null Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-06-19 10:20 ` Re: case stement when null V i s h a l Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
0 siblings, 2 replies; 3+ messages in thread
From: Jaime Casanova @ 2004-06-18 21:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: case stement when null
2004-06-18 21:38 case stement when null Jaime Casanova <el_vigia_ec@hotmail.com>
@ 2004-06-19 06:21 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Riccardo G. Facchini @ 2004-06-19 06:21 UTC (permalink / raw)
To: pgsql-sql
--- 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
where ent_codigo = $1 and
sec_codigo = $2 and
ani_codigo = $3)
else cic_codigo = $4
end
[]
regards,
=====
Riccardo G. Facchini
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: case stement when null
2004-06-18 21:38 case stement when null Jaime Casanova <el_vigia_ec@hotmail.com>
@ 2004-06-19 10:20 ` V i s h a l Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
1 sibling, 0 replies; 3+ messages in thread
From: V i s h a l Kashyap @ [Sai Hertz And Control Systems] @ 2004-06-19 10:20 UTC (permalink / raw)
To: Jaime Casanova <el_vigia_ec@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-06-19 10:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-18 21:38 case stement when null Jaime Casanova <el_vigia_ec@hotmail.com>
2004-06-19 06:21 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-06-19 10:20 ` V i s h a l Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox