case statement with between

[j9]

Hi,

I'm trying to use a 'between' condition within a CASE statement, but I get an error. I don't want to have to repeat the CASE expression for each 'When' because it's pretty long. Anyway, here's what I want to do:

CASE myVeryComplexSubqueryThatReturnsAnInteger
WHEN BETWEEN 1 AND 5
THEN 90 * myVeryComplexSubqueryThatReturnsAnInteger
ELSE WHEN BETWEEN 6 AND 10
THEN 45 * myVeryComplexSubqueryThatReturnsAnInteger
ETC.
END

THANKS!

 

[DugsDMan]

Try this...

SELECT CASE WHEN myVeryComplexSubqueryThatReturnsAnInteger BETWEEN 1 AND 5
THEN 90 * myVeryComplexSubqueryThatReturnsAnInteger
WHEN myVeryComplexSubqueryThatReturnsAnInteger
BETWEEN 6 AND 10
THEN 45 * myVeryComplexSubqueryThatReturnsAnInteger
ELSE
THEN 15 * myVeryComplexSubqueryThatReturnsAnInteger
END

 

[j9]

Yes, I know that works, but I wanted to avoid doing it that way because myVeryComplexSubqueryThatReturnsAnInteger is actually about 30 lines long and I have 5 'else' statements. Thanks anyway.

 

[DugsDMan]

Just use an alias for the subquery and reference the integer that it returns, something like:

SELECT CASE WHEN A.ReturnedInteger BETWEEN 1 AND 5
THEN 90 * A.ReturnedInteger
WHEN A.ReturnedInteger BETWEEN 6 AND 10
THEN 45 * A.ReturnedInteger
ELSE
THEN 15 * A.ReturnedInteger
END as NewResults
FROM myVeryComplexSubqueryThatReturnsAnInteger A

 

[j9]

I tried something like that but it doesn't recognize the alias (I had tried creating a separate column for the subquery with an alias). Any idea?

 

[DugsDMan]

Can you dummy out the table name and post the query? I could probably do something then...