From: V i s h a l Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
To: Jaime Casanova <el_vigia_ec@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: case stement when null
Date: Sat, 19 Jun 2004 15:50:05 +0530
Message-ID: <40D41355.6010803@sancharnet.in> (raw)
In-Reply-To: <BAY8-F104oxRI8gcXIi0003bc86@hotmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sank89@sancharnet.in, el_vigia_ec@hotmail.com
Subject: Re: case stement when null
In-Reply-To: <40D41355.6010803@sancharnet.in>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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