anonymous

Below is my WHERE clause:

WHERE SOP10200.SOPTYPE = 2 and
SOP10200.QUANTITY > 0 AND
SOP10100.DOCID NOT IN ('SP','DF','TS') AND
SOP10100.VOIDSTTS = 0 AND
SOP10100.DOCDATE >= @StartDate and
SOP10100.DOCDATE <= Left(@EndDate,12) AND
(CASE @SelectBy WHEN 1 THEN (IV00101.USCATVLS_1
BETWEEN @BeginSelectionText AND @EndSelectionText)
WHEN 2 THEN (IV00101.ITMCLSCD
BETWEEN @BeginSelectionText AND @EndSelectionText)
ELSE (SOP10200.ITEMNMBR BETWEEN
@BeginSelectionText AND @EndSelectionText) END)
(CASE @MainOnly WHEN 1 THEN IV00101.USCATVLS_4
= 'M' END)

Is this possible? SQL doesn't like this. What am I doing
wrong. I can't find any do***entation on how to do a CASE
statement in a WHERE clause other than that you can.
Should I use an IF statement somehow?
Basically, If the person enters 1,2 or 3 for the @SelectBy,
then I need to search different fields for the text that
they enter.
Any help would be greatly appreciated.
Thank You

nigel rivett

SOP10100.DOCDATE <= Left(@EndDate,12)
AND ( ( @SelectBy = 1 and IV00101.USCATVLS_1 BETWEEN
@BeginSelectionText AND @EndSelectionText )
or
( @SelectBy = 2 and IV00101.ITMCLSCD BETWEEN
@BeginSelectionText AND @EndSelectionText )
or
( @SelectBy not in (1,2) and SOP10200.ITEMNMBR
BETWEEN @BeginSelectionText AND @EndSelectionText)
)
and (@MainOnly <> 1 or IV00101.USCATVLS_4 = 'M')

alternatively (the case statement will select values not
statements)

SOP10100.DOCDATE <= Left(@EndDate,12)
and case @SelectBy when 1 then IV00101.USCATVLS_1 when 2
then IV00101.ITMCLSCD else SOP10200.ITEMNMBR end BETWEEN
@BeginSelectionText AND @EndSelectionText
and IV00101.USCATVLS_4 = case when @MainOnly = 1 then 'M'
else IV00101.USCATVLS_4 end