X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 05864D1B187
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 19 Jun 2004 03:21:49 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 97085-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 19 Jun 2004 06:21:41 +0000 (GMT)
Received: from web13904.mail.yahoo.com (web13904.mail.yahoo.com
	[216.136.175.67])
	by svr1.postgresql.org (Postfix) with SMTP id 60EE6D1B173
	for <pgsql-sql@postgresql.org>; Sat, 19 Jun 2004 03:21:38 -0300 (ADT)
Message-ID: <20040619062138.5995.qmail@web13904.mail.yahoo.com>
Received: from [217.76.159.49] by web13904.mail.yahoo.com via HTTP;
	Fri, 18 Jun 2004 23:21:38 PDT
Date: Fri, 18 Jun 2004 23:21:38 -0700 (PDT)
From: "Riccardo G. Facchini" <abief_ag_-postgresql@yahoo.com>
Subject: Re: case stement when null 
To: pgsql-sql@postgresql.org
In-Reply-To: <BAY8-F104oxRI8gcXIi0003bc86@hotmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200406/162
X-Sequence-Number: 18107

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