| Decode Function and Range Checking [message #23468] |
Wed, 11 December 2002 11:20  |
victor rodriguez
Messages: 6
Registered: December 2002
|
Junior Member |
|
|
I am converting an MS Access query to an Oracle query. The Access query uses the if-statement with a between operator:
SELECT IIf(([[CtSecTot]]-[[ArSecTot]]) Between 0.01 And 86400,([[CtSecTot]]-[[ArSecTot]])/60,0) AS Min
FROM ORDER_SUMMARY
I want to be able to convert it to Oracle using a Decode to substitute the If-Statement, However I ger an error:
SELECT DECODE(([[CtSecTot]]-[[ArSecTot]]), Between 0.01 And 86400,([[CtSecTot]]-[[ArSecTot]])/60,0) AS Min
FROM ORDER_SUMMARY
ERROR at line 1:
ORA-00936: missing expression
The error points to the Between operator. Can the DECODE function do range checking? Or do I have to develop my own function to do the range checking?
Thanks.
|
|
|
|
| Re: Decode Function and Range Checking [message #23470 is a reply to message #23468] |
Wed, 11 December 2002 11:29  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You can simulate this with DECODE by using the SIGN function, but it is fairly convoluted. I would suggest using a CASE expression instead which is much cleaner:
select case when (a-b) between .01 and 86400 then (a-b)/60 else 0 end
from order_summary; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
