Building a "WHERE" Clause with Direct SQL Passthru

[LeonelSanchezJr]

I have built "WHERE" clauses for direct SQL passthrus, but this one is slightly different.

I have 3 parameter fields which can either all be populated or only one or two.

My code goes like this:

-SET &M_WHERE1= IF '&ORG.EVAL' EQ ' ' THEN ' '
- ELSE ' S.ORIGINLOCATIONCODE = ''&ORG''';
-SET &M_WHERE2= IF '&DST.EVAL' EQ ' ' THEN ' '
- ELSE ' S.DESTINATIONLOCATIONCODE = ''&DST''';
-SET &M_WHERE3= IF '&APPT.EVAL' EQ 'N' OR 'n' THEN ' '
- ELSE ' SE.ADDEDDATE IS NOT NULL ';
-SET &M_WHERE = IF &M_WHERE1.EVAL NE ' ' THEN &M_WHERE1
- ELSE '' ;
-SET &M_WHERE = IF &M_WHERE.EVAL NE ' ' THEN IF &M_WHERE2.EVAL NE ' ' THEN &M_WHERE | ' AND ' + | &M_WHERE2
- ELSE &M_WHERE ;



Thanks,

Leo ;-)

 

[focwizard]

Leo,

Is there a question related to this post, or did I miss it?

 

[LeonelSanchezJr]

Sorry,

The code above does not work.

I need the "WHERE" clause to be populated with the combination of 1, 2 or all 3 parameters chosen alcong with the "AND" if needed.



Thanks,

Leo ;-)

 

[focwizard]

Leo,

Try this:

-SET &M_WHERE1= IF &ORG EQ ' ' THEN ' '
- ELSE '  S.ORIGINLOCATIONCODE = ''&ORG.EVAL''';
-SET &M_WHERE2= IF &DST EQ ' ' THEN ' '
- ELSE '  S.DESTINATIONLOCATIONCODE = ''&DST.EVAL''';

-SET &M_WHERE2C=IF &M_WHERE2 EQ ' ' THEN ' ' ELSE 
-               IF &M_WHERE1 NE ' ' THEN ' AND ' ELSE ' ';

-SET &M_WHERE3= IF &APPT EQ 'N' OR 'n' THEN ' '
- ELSE '  SE.ADDEDDATE IS NOT NULL ';

-SET &M_WHERE3C=IF &M_WHERE3 EQ ' ' THEN ' ' ELSE
-               IF &M_WHERE2 NE ' ' THEN ' AND ' ELSE
-               IF &M_WHERE1 NE ' ' THEN ' AND ' ELSE ' ';

-SET &M_WHERE  = IF &M_WHERE1 EQ ' ' AND &M_WHERE2 EQ ' ' AND 
-                   &M_WHERE3 EQ ' ' THEN ' ' ELSE
-  'WHERE ' | &M_WHERE1 | &M_WHERE2C | &M_WHERE2 | &M_WHERE3C | &M_WHERE3;

-TYPE &M_WHERE

What I've done is remove the quotes around amper variables (so you don't need the .EVALs). An amper variable in single quotes is treated as a literal, unless you use the .EVAL operator, which forces evaluation.

I also calculate the 'connector' between variables, depending on whether there's a value or not, and whether there's a prior value.