Select Case question 2

[avu]

Hello,

I got a syntax error for this query:

SELECT custOrder, lastName, firstName
FROM custTable
WHERE (NOT (lastName IS NULL)) AND custRegion IN (SELECT CASE centerCode WHEN 'A' THEN ('NC','MN','OP') ELSE ('BC','HE','QP','DC') END)
ORDER BY lastName

The error is at "('NC','MN','OP')" and "('BC','HE','QP','DC')". I guess SQL 2000 doesn't like multiple return. How can I fix this?

Thanks much.

 

[SQLBill]

I think the problem is that you need to return the list as a string, so it should be like this:

custRegion IN (SELECT CASE centerCode WHEN 'A' THEN '('NC','MN','OP')' ELSE '('BC','HE','QP','DC')' END)

-SQLBill

 

[AtomicWedgie]

The string should be like this:

WHEN 'A' THEN '(''NC'',''MN'',''OP'')'
ELSE '(''BC'',''HE'',''QP'',''DC'')'
END

Atomic Wedgie

 

[donutman]

It would be interesting to see if the execution plan is any different for this:

SELECT custOrder, lastName, firstName
FROM custTable
WHERE (NOT (lastName IS NULL)) AND (centercode='A' and custRegion IN ('NC','MN','OP') or centercode<>'A' and custRegion in  ('BC','HE','QP','DC'))
ORDER BY lastName

-Karl

 

[maswien]

The SQL should be:

SELECT custOrder, lastName, firstName

FROM custTable

WHERE (NOT (lastName IS NULL))
AND ( centerCode = 'A'
and custRegion IN ('NC','MN','OP')
or centerCode <> 'A'
and custRegion IN ('BC','HE','QP','DC'))

ORDER BY lastName

 

[avu]

Thanks all for your responses.

Karl & mjia's method sure fixed the issue.

Something new to learn everyday! Cheers.

avu