?
Solved

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

Posted on 2008-11-06
5
Medium Priority
?
4,472 Views
Last Modified: 2013-12-19
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


I receive an error
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
Comment
Question by:CJJCA
  • 2
  • 2
5 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
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

Open in new window

0
 
LVL 143

Assisted Solution

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

Open in new window

0
 
LVL 26

Accepted Solution

by:
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

by:CJJCA
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

by:CJJCA
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question