Thread: Help with case in select

Hello, I have the following sp, I need to return a 'Flag' if the ID of the
row is in the prior select, I tryed with the following code:

create or replace function sp_getadvertisers(ag integer) returns record as
$main$
declare
 alladv record;
 retrec record;
begin
 -- Primero buscamos todas las campanas de este advertiser q sean hijos
 select into alladv userid from advertiser where agenciaid=ag and adv_parent
is not null;
 select into retrec userid, case when userid in alladv then 'P' when
adv_parent is not null then 'S' else null end from advertiser where
agenciaid=ag;
 return retrec;
end;
$main$
language plpgsql;

But it returns me:
ERROR:  syntax error at or near "$1" at character 38
QUERY:  SELECT  userid, case when userid in  $1  then 'P' when adv_parent is
not null then 'S' else null end from advertiser where agenciaid= $2
CONTEXT:  PL/pgSQL function "sp_getadvertisers" line 7 at select into
variables
LINE 1: SELECT  userid, case when userid in  $1  then 'P' when adv_p...

Any Idea how could I change it to work? ideas???

Thanks a lot...

Cristian Prieto wrote:
> Hello, I have the following sp, I need to return a 'Flag' if the ID of
> the row is in the prior select, I tryed with the following code:
>
> create or replace function sp_getadvertisers(ag integer) returns record as
> $main$
> declare
> alladv record;
> retrec record;
> begin
> -- Primero buscamos todas las campanas de este advertiser q sean hijos
> select into alladv userid from advertiser where agenciaid=ag and
> adv_parent is not null;
> select into retrec userid, case when userid in alladv then 'P' when
> adv_parent is not null then 'S' else null end from advertiser where
> agenciaid=ag;
> return retrec;
> end;
> $main$
> language plpgsql;
>
> But it returns me:
> ERROR:  syntax error at or near "$1" at character 38
> QUERY:  SELECT  userid, case when userid in  $1  then 'P' when
> adv_parent is not null then 'S' else null end from advertiser where
> agenciaid= $2
> CONTEXT:  PL/pgSQL function "sp_getadvertisers" line 7 at select into
> variables
> LINE 1: SELECT  userid, case when userid in  $1  then 'P' when adv_p...
>
> Any Idea how could I change it to work? ideas???

Some things to check:
1. Do you have a column "ag" in either of those tables?
2. You should make "alladv" an integer (or whatever type "userid" is)
rather than a record.
3. "userid in alladv" - why not "userid = alladv"?

--
   Richard Huxton
   Archonet Ltd