pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInserting an IF statement in the middle of a SELECT in pl/pgSQL code
5+ messages / 4 participants
[nested] [flat]
* Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
@ 2007-07-18 02:42 Norm Garand <indianlakesolutions@rogers.com>
2007-07-18 04:46 ` Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Rodrigo De León <rdeleonp@gmail.com>
2007-07-18 04:50 ` Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Jaime Casanova <systemguards@gmail.com>
2007-07-18 12:46 ` Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 3 replies; 5+ messages in thread
From: Norm Garand @ 2007-07-18 02:42 UTC (permalink / raw)
To: pgsql-sql
Hi:
I can't seem to resolve this issue. I have a fair sized SELECT statement that runs properly in a
stored procedure using pl/pgSQL, but I have certain parts of the query that can filter by a NULL
value, or a character string.
What I can't seem to do is insert a conditional IF statement in my code.
Here is how it starts:
DECLARE
r zrec_dd_holder%rowtype;
BEGIN
FOR r IN
SELECT DISTINCT(pi.serial_number) AS "Incident ID",
to_char(pi.incident_date,'Mon-dd-yyyy') AS "Incident date",
to_char(pi.date_created,'Mon-dd-yyyy') AS "Report Date",
CASE
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 1 THEN 1
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 2 THEN 1
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 3 THEN 1
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 4 THEN 2
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 5 THEN 2
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 6 THEN 2
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 7 THEN 3
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 8 THEN 3
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 9 THEN 3
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 10 THEN 4
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 11 THEN 4
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 12 THEN 4
END AS "Quarter",
pf.name AS "Facility",
pl.name AS "General Location",
f.long_desc AS "Specific Location",
......
The user can choose to allow the default (in this case NULL) or supply a predetermined value for
"Specific Location".
In the WHERE portion of the SELECT statment, I'd like to insert the IF statement shown below. I've
tried single and double quotes and the pipeline for concatenation, but nothing seems to work. Any
suggestions or resolutions would be greatly appreciated. Please feel free to contact me directly.
AND pi.id = pid.id
AND ( pid.incident_type_cid BETWEEN 117 AND 123 )
/---------------
IF $7 IS NOT NULL THEN
AND f.id = pid.specific_location_cid AND f.long_desc = $7
END IF
---------------/
AND ( pi.location_id = pl.id )
AND pf.id = pl.facility_id
AND pi.person_status_code_id = b.id
regards,
Norm
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
2007-07-18 02:42 Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Norm Garand <indianlakesolutions@rogers.com>
@ 2007-07-18 04:46 ` Rodrigo De León <rdeleonp@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Rodrigo De León @ 2007-07-18 04:46 UTC (permalink / raw)
To: Norm Garand <indianlakesolutions@rogers.com>; +Cc: pgsql-sql
On 7/17/07, Norm Garand <indianlakesolutions@rogers.com> wrote:
> /---------------
> IF $7 IS NOT NULL THEN
> AND f.id = pid.specific_location_cid AND f.long_desc = $7
> END IF
> ---------------/
How about:
AND f.ID = pid.specific_location_cid
AND f.long_desc = COALESCE ($7, f.long_desc)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
2007-07-18 02:42 Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Norm Garand <indianlakesolutions@rogers.com>
@ 2007-07-18 04:50 ` Jaime Casanova <systemguards@gmail.com>
2007-07-19 02:40 ` Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Jaime Casanova <systemguards@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Jaime Casanova @ 2007-07-18 04:50 UTC (permalink / raw)
To: Norm Garand <indianlakesolutions@rogers.com>; +Cc: pgsql-sql
On 7/17/07, Norm Garand <indianlakesolutions@rogers.com> wrote:
> In the WHERE portion of the SELECT statment, I'd like to insert the IF statement shown below. I've
> tried single and double quotes and the pipeline for concatenation, but nothing seems to work. Any
> suggestions or resolutions would be greatly appreciated. Please feel free to contact me directly.
>
use CASE
baan=# create function t(con_stock boolean) returns setof ttdinv001214 as $$
baan$# select * from ttdinv001214
baan$# where substring(trim(t_item), 1, 3) = 'DPP'
baan$# and case $1 when true then t_stoc > 0 else t_stoc = 0 end;
baan$# $$ language sql;
CREATE FUNCTION
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
2007-07-18 02:42 Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Norm Garand <indianlakesolutions@rogers.com>
2007-07-18 04:50 ` Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Jaime Casanova <systemguards@gmail.com>
@ 2007-07-19 02:40 ` Jaime Casanova <systemguards@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jaime Casanova @ 2007-07-19 02:40 UTC (permalink / raw)
To: Norm Garand <indianlakesolutions@rogers.com>; +Cc: pgsql-sql
sorry, i resubmit to the list
On 7/18/07, Jaime Casanova <systemguards@gmail.com> wrote:
> On 7/18/07, Norm Garand <indianlakesolutions@rogers.com> wrote:
> > Thanks Jaime:
> >
> > I tried using the CASE function and it still didn't work.
> >
> > What I need to do is set a conditional, so that if the parameter is NULL, then the this line is
> > ignored completely and of course the opposite applies, if the value is "something", then this line
> > is included in the SELECT. I've just reworded and resubmitted my question and of course, again, I
> > didn't include the facts above. Thanks, for the response, anything is appreciated.
> >
>
> ok, i will do your work...
>
> CREATE OR REPLACE FUNCTION t(con_stock boolean)
> RETURNS SETOF ttdinv001214 AS
> $BODY$
> select * from ttdinv001214
> where substring(trim(t_item), 1, 3) = 'DPP'
> and case $1 when true then t_stoc > 0 else 1 = 1 end;
> $BODY$
> LANGUAGE 'sql' VOLATILE;
>
>
> note the "else 1=1" part, i could be used "else true" or something else
>
> --
> regards,
> Jaime Casanova
>
> "Programming today is a race between software engineers striving to
> build bigger and better idiot-proof programs and the universe trying
> to produce bigger and better idiots.
> So far, the universe is winning."
> Richard Cook
>
--
Atentamente,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
2007-07-18 02:42 Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Norm Garand <indianlakesolutions@rogers.com>
@ 2007-07-18 12:46 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2 siblings, 0 replies; 5+ messages in thread
From: Fernando Hevia @ 2007-07-18 12:46 UTC (permalink / raw)
To: 'Norm Garand' <indianlakesolutions@rogers.com>; pgsql-sql
You should try 'Coalesce' function.
By the way, your "incident_date-to-quarter" expression could be shortened:
SELECT DISTINCT(pi.serial_number) AS "Incident ID",
to_char(pi.incident_date,'Mon-dd-yyyy') AS "Incident date",
to_char(pi.date_created,'Mon-dd-yyyy') AS "Report Date",
(((EXTRACT (MONTH FROM pi.incident_date ))::integer - 1) / 3) + 1 AS
Quarter
...
Regards,
Fernando.
-----Mensaje original-----
De: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
En nombre de Norm Garand
Enviado el: Martes, 17 de Julio de 2007 23:43
Para: pgsql-sql@postgresql.org
Asunto: [SQL] Inserting an IF statement in the middle of a SELECT in
pl/pgSQL code
Hi:
I can't seem to resolve this issue. I have a fair sized SELECT statement
that runs properly in a
stored procedure using pl/pgSQL, but I have certain parts of the query that
can filter by a NULL
value, or a character string.
What I can't seem to do is insert a conditional IF statement in my code.
Here is how it starts:
DECLARE
r zrec_dd_holder%rowtype;
BEGIN
FOR r IN
SELECT DISTINCT(pi.serial_number) AS "Incident ID",
to_char(pi.incident_date,'Mon-dd-yyyy') AS "Incident date",
to_char(pi.date_created,'Mon-dd-yyyy') AS "Report Date",
CASE
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 1 THEN 1
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 2 THEN 1
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 3 THEN 1
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 4 THEN 2
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 5 THEN 2
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 6 THEN 2
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 7 THEN 3
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 8 THEN 3
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 9 THEN 3
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 10 THEN 4
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 11 THEN 4
WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 12 THEN 4
END AS "Quarter",
pf.name AS "Facility",
pl.name AS "General Location",
f.long_desc AS "Specific Location",
......
The user can choose to allow the default (in this case NULL) or supply a
predetermined value for
"Specific Location".
In the WHERE portion of the SELECT statment, I'd like to insert the IF
statement shown below. I've
tried single and double quotes and the pipeline for concatenation, but
nothing seems to work. Any
suggestions or resolutions would be greatly appreciated. Please feel free to
contact me directly.
AND pi.id = pid.id
AND ( pid.incident_type_cid BETWEEN 117 AND 123 )
/---------------
IF $7 IS NOT NULL THEN
AND f.id = pid.specific_location_cid AND f.long_desc = $7
END IF
---------------/
AND ( pi.location_id = pl.id )
AND pf.id = pl.facility_id
AND pi.person_status_code_id = b.id
regards,
Norm
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-07-19 02:40 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-18 02:42 Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Norm Garand <indianlakesolutions@rogers.com>
2007-07-18 04:46 ` Rodrigo De León <rdeleonp@gmail.com>
2007-07-18 04:50 ` Jaime Casanova <systemguards@gmail.com>
2007-07-19 02:40 ` Jaime Casanova <systemguards@gmail.com>
2007-07-18 12:46 ` Fernando Hevia <fhevia@ip-tel.com.ar>
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