Multiple parameter for WHERE clause

[Sheri Woo <Sheri.Woo@CNPL.ENBRIDGE.COM>]

Anne, regarding your question dynamic where clause. I've seen it done two
different ways.

1. Use 1=1.  We do this all the time and it works just fine.


If a parameter IS entered we build our where clause dynamically and it ends up
looking like this (we have a ton of code to concatenate this together... I'll
just show you the end result):

$WhereBenPlan is the variable containing our dynamic where clause and it would
contain:  A.BEN_PLAN = 'XYZ'

or, if we allow multiple entries  A.BEN_PLAN IN ('XYZ','ABC')


If a parameter is NOT entered we set $WhereBenPlan to 1=1.  This condition will
always evaluate as true so you'll get all your benefit plans.

In the program our WHERE statements look like this.  Depending on what our users
have entered the $Where statements contain 1=1 or contain an actual criteria.

SELECT .... etc
FROM PS_Table1
WHERE [$WhereCompany]
  AND [$WhereBenPlan]
... etc

2. Dynamically parse the entire statement together,  i.e. find a way to have
your final statement
read one of two ways depending on what was entered:


a) if data WAS entered:

SELECT .... etc
FROM PS_Table1
WHERE [$WhereStatement]  ==> this would contain conditions for Company AND
Benefit plan
... etc

b) if data was NOT entered
SELECT .... etc
FROM PS_Table1
WHERE [$WhereStatement]  ==> this would contain conditions for ONLY Benefit plan
... etc

Hope this helps. Let me know if you need more info


-------

I have a PeopleSoft report which can have multiple parameter that can
either be used or not depending on the users preference.
ie: If a benefit plan is entered use the code specified else use all benefit
plan. I have around 6 different options like this.

I am wondering what would be the most efficient way to code this type of
specification??

Anne Mongauzy <amongauzy@GVJONES.COM.AU>