Thread: 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.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
Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
From
"Rodrigo De León"
Date:
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)
Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
From
"Jaime Casanova"
Date:
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
Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
From
"Fernando Hevia"
Date:
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.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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
Re: Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
From
"Jaime Casanova"
Date:
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