Thanks Alexandre...

Alexandre Benson Smith wrote:

> 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 !
>
>
>