Solved

# Decode statement to check for less than greater than sends an error

Posted on 2008-11-06
Medium Priority
4,472 Views
Hi,

Im using Oracle 10 g and sql talk for windows
The query can work with a case statement, but have to convert my SQL  to use the DECODE statement.
I would like the decode to use logic like this:
if  (A.FIXED + B. RATE) is less than minrate,
then use minrate
else
if (A.FIXED + B. RATE) is greater than maxrate
then use maxtrate
else
use the rate from (A.FIXED + B. RATE)
the decode will run like the sql below, but this is not checking for less than or greater than
Decode((A.FIXED + B. RATE),
A.MINRATE,  A. MINTRATE,
A.MAXRATE,  A.MAXRATE,  (A.FIXED + B.RATE)) APR4

If i change the decode to include < or > (like below),

Decode((A.FIXED + B. RATE),
< A.MINRATE, - A. MINTRATE,
>  A.MAXRATE,  A.MAXRATE,  (A.FIXED + B.RATE)) APR4

Error: ORA=00936 missing expression.

Below is the whole sql

SELECT
(A.FIXED + B. RATE)  APR1, A.MINRATE,
A.MAXRATE,
B.RATE,
(SELECT DECODE(CY.LEAPYR, 'Y', 366, 365)   FROM CY CY WHERE YRNBR = (SELECT TO_CHAR( SYSDATE,'YYYY')  FROM DUAL)) AS DAYSINYEAR,
Decode((A.FIXED + B. RATE),
A.MINRATE,  A. MINTRATE,
A.MAXRATE,  A.MAXRATE,  (A.FIXED + B.RATE)) APR4
FROM
A A ,
B B
WHERE A.A = 'CODE'
AND A.DATE IS NULL
AND B. NBR = A. NBR
AND B. DATE2 = (SELECT MAX(DATE2)  FROM
TABLEA  WHERE NBR =  01);

Thank you from CJJA
0
Question by:CJJCA
• 2
• 2

LVL 143

Assisted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22900290
this will do better
``````CASE WHEN A.FIXED + B. RATE < A.MINRATE THEN A. MINTRATE
WHEN A.FIXED + B. RATE > A.MAXRATE THEN A.MAXRATE
ELSE A.FIXED + B.RATE END APR4
``````
0

LVL 143

Assisted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22900300
or, better even:
``````LEAST(GREATEST( A.MINRATE , A.FIXED + B. RATE), A.MAXRATE) APR4
``````
0

LVL 26

Accepted Solution

tigin44 earned 1000 total points
ID: 22900314
decode is not suitable for your purpose...
in DECODE you just compare one scaler value with other values... but you want to campore more then one value so continue using case when clause...
0

Author Comment

ID: 22900604
Thank you,  the LEAST statement that Angel suggested  is working in SQL,   I now have to test it in the online environment where the CASE statment wouldn't provide the expected results.
0

Author Comment

ID: 22909331
Hi,
Although, the Greatest and Least function work in sql, they did not work in the environment I use to place them on forms.
The query was re-wrote so that no logic was involved in the columns being listed.
The query example is below in case the format can be helpful to someone.   Thank you all so much for your help.  I learned how to use a function that I didn't know how to use.  I apprciate your sharing your knowledge.

SELECT
TO_CHAR(SUB1.APR * 100, '00.00') APRU,
TO_CHAR(SUB1.APR / DAYS_IN_YEAR * 100, '.0000') DPR9
FROM
(
SELECT
RATE,
(A. FIXED + B. RATE) DPR1,
A.MINRATE,
A.MAXIATE,
CASE
WHEN
(
A.FIXED + B. RATE
)
< A.MINRATE
THEN A. MINRATE
WHEN
(
A.FIXED + B.RATE
)
> A.MAXRATE
THEN A.MAXRATE
ELSE (A.FIXED + B.RATE)
END AS APR,
DECODE( (SELECT YRNBR FROM CY WHERE YRNBR = (SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL)), 'Y', 366, 365 ) as "DAYS_IN_YEAR"
FROM
A A ,
B B
WHERE
A.CD = 'CODE'
AND A.DATE IS NULL
AND B. NBR = A. DNBR
AND B.DATE      =
(
SELECT
MAX(DATE)
FROM
C C
WHERE
C.NBR =  01        )
)
SUB1
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
###### Suggested Courses
Course of the Month14 days, 3 hours left to enroll