From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: 'Norm Garand' <indianlakesolutions@rogers.com>
To: pgsql-sql@postgresql.org
Subject: Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
Date: Wed, 18 Jul 2007 09:46:33 -0300
Message-ID: <016f01c7c939$abeaa930$8f01010a@iptel.com.ar> (raw)
In-Reply-To: <52149.39195.qm@web88311.mail.re4.yahoo.com>
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
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: fhevia@ip-tel.com.ar, indianlakesolutions@rogers.com
Subject: Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
In-Reply-To: <016f01c7c939$abeaa930$8f01010a@iptel.com.ar>
* 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