Case statement in a where clause?

[gtarrant]

I'm having a problem with the below script and I don't even know if this is possible. I would like to insert a case statement within my where clause to control rates but have received this error when I fire off the sql:
invalid relational operator (its pointing to 'THEN' in my case statement).

Can someone point out where I've gone wrong. THANKS!!!

SELECT X.TOTAL_COVRG_RATE
FROM PS_RATE_SCHED_COVG X
WHERE X.RATE_SCHEDULE_ID = (CASE WHEN 'LTD1' THEN 'LTD1'
WHEN 'LTD2' THEN 'LTD1'
WHEN 'LTDF' THEN 'LTDF'
WHEN 'LTDG' THEN 'LTDF'
END)
AND X.PLAN_TYPE = '31'
AND X.EFFDT = (SELECT MAX(XX.EFFDT)
FROM PS_RATE_SCHED_COVG XX
WHERE XX.RATE_SCHEDULE_ID = X.RATE_SCHEDULE_ID
AND XX.PLAN_TYPE = X.PLAN_TYPE
AND XX.EFFDT <= GETDATE()))

 

[john76]

What are you comparing in your case statement?

Case [red](some field?) or...[/red]
  When 'LTD1' [red](= ?)[/red] Then 'LTD1'
  ...

John

 

[gtarrant]

I've tried to rewrite this and I'm now getting the error: single-row subquery returns more than one row.

I'm trying to compare all RATE_SCHEDULE_ID values on the table (4 distinct in total) and convert them into just two, giving me the TOTAL_COVRG_RATE for only two.


SELECT X.TOTAL_COVRG_RATE
FROM PS_RATE_SCHED_COVG X
WHERE X.PLAN_TYPE = '31'
AND X.RATE_SCHEDULE_ID =
(SELECT(CASE WHEN Y.RATE_SCHEDULE_ID LIKE 'LTD%' THEN 'LTD1'
WHEN Y.RATE_SCHEDULE_ID LIKE 'LTF%' THEN 'LTF1'
END) FROM PS_RATE_SCHED_COVG Y
WHERE Y.PLAN_TYPE = '31'
AND Y.EFFDT <= (SELECT MAX(XX.EFFDT)
FROM PS_RATE_SCHED_COVG XX
WHERE XX.RATE_SCHEDULE_ID = Y.RATE_SCHEDULE_ID
AND XX.PLAN_TYPE = Y.PLAN_TYPE
AND XX.EFFDT <= GETDATE()))