Inserting an IF statement in the middle of a SELECT in pl/pgSQL code

Поиск
Список
Период
Сортировка
От Norm Garand
Тема Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
Дата
Msg-id 52149.39195.qm@web88311.mail.re4.yahoo.com
обсуждение исходный текст
Ответы Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code  ("Rodrigo De León" <rdeleonp@gmail.com>)
Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code  ("Jaime Casanova" <systemguards@gmail.com>)
Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code  ("Fernando Hevia" <fhevia@ip-tel.com.ar>)
Список 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.idAND ( 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_idAND pi.person_status_code_id = b.id



regards,

Norm


В списке pgsql-sql по дате отправления:

Предыдущее
От: Andrew Sullivan
Дата:
Сообщение: Re: data dependent sequences?
Следующее
От: "Rodrigo De León"
Дата:
Сообщение: Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code