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

[Norm Garand]

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-') AS "Incident date",
 to_char(pi.date_created,'Mon-dd-') 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

[SQL] A better look at trying to insert a Conditional in a SELECT statement

[Norm Garand]

Hi Again:

thanks to those who sent me responses on my first post. I'll try to explain a 
bit better. There
are several parameters that are passed by the user. Each of the parameters that 
are being passed,
may have a NULL value, or an actual numerical value. The parameters filter a 
large report down a
to small amount of records. With that being said, the problem I'm encountering 
is that I can't
seem to insert a conditional (whether it be a CASE statement or an IF 
statement) in the middle of
the SELECT statement. I'm not sure if the problem stems from using the ROWTYPE 
variable notation.
Here is the complete code. It works as long as a NULL value doesn't get passed.

To show what I mean, I'd like to insert the conditional around this line (23rd 
from the bottom):

f.id = pid.specific_location_cid AND f.long_desc = $7

-- Function: sp_hirs_a_01dd(integer, character varying, integer, character 
varying, character
varying, character varying, character varying)

-- DROP FUNCTION sp_hirs_a_01dd(integer, character varying, integer, character 
varying, character
varying, character varying, character varying);

CREATE OR REPLACE FUNCTION sp_hirs_a_01dd(integer, character varying, integer, 
character varying,
character varying, character varying, character varying)
  RETURNS SETOF rec_dd_holder AS
$BODY$
DECLARE
r rec_dd_holder%rowtype;
BEGIN
FOR r IN 

SELECT  DISTINCT(pi.serial_number) AS "Incident ID",
to_char(pi.incident_date,'Mon-dd-') AS "Incident date",
to_char(pi.date_created,'Mon-dd-') 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", 

b.long_desc AS "Status",
pi.person_age AS "Age",

CASE
WHEN pi.gender_code_id ='31' THEN 'M'
WHEN pi.gender_code_id ='32' THEN 'F'
ELSE NULL
END
AS "Gender",

to_char(pi.incident_date,'hh24:mm') AS "Time",

pc.long_desc AS "Incident Type", 
a.long_desc AS "Incident Subtype",

CASEWHEN pid.fallscf_behavoiur_val ='t' THEN 'Behaviour. '  WHEN 
pid.fallscf_behavoiur_val ='f'
THEN '' END ||
CASEWHEN pid.fallscf_bowel_bladder_val ='t' THEN 'Bowel/Bladder 
Problem.' WHEN
pid.fallscf_bowel_bladder_val ='f' THEN '' END ||
CASEWHEN pid.fallscf_comm_information_val  ='t'  THEN 
'Communication/Information. ' WHEN
pid.fallscf_comm_information_val = 'f' THEN '' END ||
CASEWHEN pid.fallscf_env_condition_val  ='t'  THEN 'Environmental 
Conditions. ' WHEN
pid.fallscf_env_condition_val = 'f' THEN '' END ||
CASEWHEN pid.fallscf_eq_malfunction_val  ='t'  THEN 'Equipment 
Malfunction. '   WHEN
pid.fallscf_eq_malfunction_val = 'f' THEN '' END || 
CASEWHEN pid.fallscf_eq_supplies_val  ='t'  THEN 
'Equipment/Supplies Unavailable. ' WHEN
pid.fallscf_eq_supplies_val = 'f' THEN '' END ||
CASEWHEN pid.fallscf_visitor_val  ='t'  THEN 'Family/Visitor 
Assisting. '   WHEN
pid.fallscf_visitor_val = 'f' THEN '' END ||
CASEWHEN pid.fallscf_footwear_val  ='t'  THEN 'Inappropriate 
Footwear. 'WHEN
pid.fallscf_footwear_val = 'f' THEN '' END || 
CASEWHEN pid.fallscf_instructionsnotfollowed_val  ='t'  THEN 
'Instructions Not Followed. '  WHEN
pid.fallscf_instructionsnotfollowed_val = 'f' THEN '' END ||
CASEWHEN pid.fallscf_interference_val  ='t'  THEN 
'Interference/Interruption. ' WHEN
pid.fallscf_interference_val = 'f' THEN '' END ||
CASEWHEN pid.fallscf_material_val  ='t'  THEN 'Material/Liquid on 
Floor. '  WHEN
pid.fallscf_material_val = 'f' THEN '' END || 
CASEWHEN pid.fallscf_medication_val  ='t'  THEN 'Medication. '  
WHEN pid.fallscf_medication_val =
'f' THEN '' END ||
CASEWHEN pid.fallscf_overreaching_val  ='t'  THEN 'Overreaching. '  
WHEN
pid.fallscf_overreaching_val = 'f' THEN '' END ||
CASEWHEN pid.fallscf_physical_cond_val  ='t'  THEN 
'Physical/Medical Condition. '   WHE