Use of Case in the where clause

[snufse1]

I have a proc where I need to condition my "where" clause:

create procedure get_eqmdet
(in @jobnumber char(12)
@costcode char(8),
@suffix char(2),

select ........
where glmcu = @jobnumber and
glsub = @costcode and
case
when @suffix = ' ' then glalty in (' ', 'HR')
when @suffix <> ' ' then glalty = @suffix
end and ......



I am passing in @Suffix as a parm. I am getting error:
Keyword IN not expected. Valid tokens: END.

 

[blom0344]

AFAIK a CASE will return a certain VALUE for each record passed. In your situation it returns an expression. I doubt if this is allowed..

Ties Blom

 

[fredericofonseca]

This should do the trick
select ........
where glmcu = @jobnumber and
glsub = @costcode and
and (
(@suffix = ' ' and glalty in (' ', 'HR')
or (@suffix <> ' ' and glalty = @suffix)
)


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

one bracket missing

select ........
where glmcu = @jobnumber and
glsub = @costcode and
and (
(@suffix = ' ' and glalty in (' ', 'HR'))
or (@suffix <> ' ' and glalty = @suffix)
)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[snufse1]

Thank you. Worked purrrfectly.....