Re: Question on a dynamic where statement

[Tony DeLia <tdelia@EROLS.COM>]

Doris,

   You'll probably be getting a flood of responses with this solution:

A - If variable $entered_dept does not have embedded quotes...

let $q = chr(39)
let $whereclause = 'WHERE DEPTID = ' || $q || $entered_dept || $q

this yields:

  WHERE DEPTID = '10000'  (Assuming Deptid '10000' entered)

You have to account for the quotes ($q) in your SQL statement.

Then...

B - Tto utilize your dynamic statement...

select (lots of fields)
from ps_employees
[$whereclause]

                             Have fun, Tony DeLia

Doris K. Brinson wrote:
>
> Is there a way using SQR to use a variable as the where statement in a
> select command.  I am trying to write a label program that will allow the
> user to enter either an emplid, department or location. Depending on what
> the user entered, I want to select the appropriate data. I can easily set a
> variable to say
>   let $whereclause = 'WHERE DEPTID = $entered_dept'
>
> SELECT (lots of fields)
> FROM PS_EMPLOYEES
> $whereclause
>
> But where I run the above type code, I get an SQL error.  I know the easy
> way would be to write different selects for each of the allowed criteria,
> but I think there has to be a better way.
>
> Doris

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com