Home » SQL & PL/SQL » SQL & PL/SQL » case issue
case issue [message #195200] Wed, 27 September 2006 06:49 Go to next message
Messages: 112
Registered: July 2006
Senior Member

In my select statement, I want to limit the param [100, -100]. The code below does not work.

Case param
	When > 100 then 100
        When < 100 then -100
        else param

I could write

Case when param > 100 then 100
     when param < 100 then -100
     else param

which works fine, but the param is in fact a calculation which is several rows long. Is there an other solution, so that i have to write the param only once?


Re: case issue [message #195210 is a reply to message #195200] Wed, 27 September 2006 07:10 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The Documentation says no, I'm afraid.

The Simple case statement (which is basically a glorified DECODE) only lets you check for equality.

The Searched case statement requires the expression for each comparison.

Options I can think of quickly:

1) write the calculation as a function, to save space
2) put an extra layer of SELECT..FROM around the query so you can give the calculation value an alias and refer to it that way in the outer layer
Previous Topic: Using Bind variable
Next Topic: Problem Generating XML File
Goto Forum:

Current Time: Sat Oct 22 19:29:21 CDT 2016

Total time taken to generate the page: 0.09897 seconds