James N Hitz wrote:

> Dear All
>
> I am making a stored procedure that will generate a list of employees,
> searching by DEPT, EMPID, JOBGROUP... or whatever else is specified as
> an INPUT parameter. I created the following but the CASE section causes
> it to fail miserably (also consider the commented out section). Is this
> the proper way to go about it?
>
>

...snip...


Hi,

You are using case like it was returning a string to the WHERE clause of
the SQL statement, it dont; works this way.

Case is a "function" that you return a scalar value.

I would substitute this

WHERE ISACTIVE=1 AND
(SELECT CASE :REFTYPE
WHEN 2 then
DEPTID = :REFNO
WHEN 3 then
JOBGROUP = :REFNO
WHEN 4 then
EMPGROUP = :REFNO
END
FROM EMPLOYEES)



for

WHERE ISACTIVE=1 AND
(((:REFTYPE = 2) and (DEPTID = :REFNO)) or
((:REFTYPE = 3) and (JOBGROUP = :REFNO)) or
((:REFTYPE = 4) and (EMPGROUP = :REFNO)))


see you !


--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br