Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

case statement to decode

Jim PDec 5 2007 — edited Dec 7 2007

Hello,
I am used to using case within my select statements, I need to use decode if possible for this one. Its for a record group within in forms, and the case statement is not working. Could someone please help me write this with decode instead of case?

SELECT CASE
       WHEN months_between(sysdate , c.birth_dt) BETWEEN 168 AND 216 THEN 1 --14 to 18
       WHEN months_between(sysdate , c.birth_dt) BETWEEN 217 AND 252 THEN 2 --18 to 21
       WHEN months_between(sysdate , c.birth_dt) BETWEEN 253 AND 780 THEN 3 --21 to 65
       WHEN months_between(sysdate , c.birth_dt) > 780 then 4 --older than 65
       ELSE NULL END 
FROM mw_clients c

Thanks in advance!
Jim P.

Comments

612219
I have tried the following statement and it works perfectly...
SELECT (CASE
WHEN months_between(sysdate , birthdate) BETWEEN 168 AND 216 THEN 1
WHEN months_between(sysdate , birthdate) BETWEEN 217 AND 252 THEN 2
WHEN months_between(sysdate , birthdate) BETWEEN 253 AND 780 THEN 3
WHEN months_between(sysdate , birthdate) > 780 then 4
ELSE 0
END) age
FROM person where months_between(sysdate,birthdate) > 167;

Good luck!
DB
584255
Well...what exactly is not working in the CASE?
Jim P
Yeah,
My original post works in PL/SQL Developer, but when I try and use the query in forms for a record group, I get a "ORA-00905: mission keyword" error

I did some searching in the forms forum, they had a couple of examples of using decode instead of case.

Thanks!
Jim P.
Sven W.
Forms is not always on the most currect SQL dialect. A workaround is to put the SQL statement on the database and call a packaged procedure/function/view from your forms.

This is good practice anyhow since it increases code reusability, maintainability and usually performance (equal or less network roundtrips).
66470
Good old way to work with decode and interval is the sign function

e.g.

select decode(sign(months_between(sysdate,c.birth_dt) - 215),1,1
,decode(sign(months_between(sysdate,c.birth_dt) - 251),1,2
,decode(sign(months_between(sysdate,c.birth_dt) - 779),1,3,4)
)
)
from mw_clients c

hth
Sven W.
My original post works in PL/SQL Developer, but when
I try and use the query in forms for a record group,
I get a "ORA-00905: mission keyword" error
Not sure where this error is coming from, but maybe it is because you use the select in a record group. You should supply a name for your column. PSSBOSS already gave a very nice example.
Aketi Jyuuzou 
select
decode(Least(months_between(sysdate , c.birth_dt),168),168,
decode(greatest(months_between(sysdate , c.birth_dt),216),216,1,0),0)+
decode(Least(months_between(sysdate , c.birth_dt),217),217,
decode(greatest(months_between(sysdate , c.birth_dt),252),252,2,0),0)+
decode(Least(months_between(sysdate , c.birth_dt),253),253,
decode(greatest(months_between(sysdate , c.birth_dt),780),780,3,0),0)+
decode(Least(months_between(sysdate , c.birth_dt),781),781,4,0)+
decode(greatest(months_between(sysdate , c.birth_dt),167),167,null,0)
from mw_clients c

Above solution is used principle which is X+0 = X , On math.

Below solution is very easy to write.
But I think that performance will be bad.

select
(select 1 from dual where months_between(sysdate , c.birth_dt) BETWEEN 168 AND 216 union all
 select 2 from dual where months_between(sysdate , c.birth_dt) BETWEEN 217 AND 252 union all
 select 3 from dual where months_between(sysdate , c.birth_dt) BETWEEN 253 AND 780 union all
 select 4 from dual where months_between(sysdate , c.birth_dt) > 780)
 from mw_clients c
BluShadow
Below solution is very easy to write.
But I think that performance will be bad.
Not really as dual is internal to the SQL engine so there's little difference between the two...
SQL> ed
Wrote file afiedt.buf

  1  with mw_clients as (select add_months(sysdate,-200) as birth_dt from dual union
  2                      select add_months(sysdate,-220) from dual union
  3                      select add_months(sysdate,-400) from dual)
  4  -- END OF TEST DATA
  5  select 4+
  6         decode(Least(months_between(sysdate , c.birth_dt),168),
  7                168,decode(greatest(months_between(sysdate , c.birth_dt),216),
  8                           216,-3,0)
  9                ,0)+
 10         decode(Least(months_between(sysdate , c.birth_dt),217),
 11                217,decode(greatest(months_between(sysdate , c.birth_dt),252),
 12                           252,-2,0)
 13                ,0)+
 14         decode(Least(months_between(sysdate , c.birth_dt),253),
 15                253,decode(greatest(months_between(sysdate , c.birth_dt),780),
 16                           780,-1,0)
 17                ,0) as result
 18* from mw_clients c
SQL> /

    RESULT
----------
         3
         2
         1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1277652150

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     3 |    27 |     9  (34)| 00:00:01 |
|   1 |  VIEW            |      |     3 |    27 |     9  (34)| 00:00:01 |
|   2 |   SORT UNIQUE    |      |     3 |       |     9  (78)| 00:00:01 |
|   3 |    UNION-ALL     |      |       |       |            |          |
|   4 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        456  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>


SQL> ed
Wrote file afiedt.buf

  1  with mw_clients as (select add_months(sysdate,-200) as birth_dt from dual union
  2                      select add_months(sysdate,-220) from dual union
  3                      select add_months(sysdate,-400) from dual)
  4  -- END OF TEST DATA
  5  select c.birth_dt,
  6         (select 1 from dual where months_between(sysdate , c.birth_dt) BETWEEN 168 AND 216 union
  7          select 2 from dual where months_between(sysdate , c.birth_dt) BETWEEN 217 AND 252 union
  8          select 3 from dual where months_between(sysdate , c.birth_dt) BETWEEN 253 AND 780 union
  9          select 4 from dual where months_between(sysdate , c.birth_dt) > 780
 10         ) as result
 11* from mw_clients c
SQL> /

BIRTH_DT                RESULT
------------------- ----------
06/08/1974 10:15:39          3
06/08/1989 10:15:39          2
06/04/1991 10:15:39          1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 706016619

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     3 |    27 |     9  (34)| 00:00:01 |
|   1 |  SORT UNIQUE     |      |     4 |       |    12  (84)| 00:00:01 |
|   2 |   UNION-ALL      |      |       |       |            |          |
|*  3 |    FILTER        |      |       |       |            |          |
|   4 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|*  5 |    FILTER        |      |       |       |            |          |
|   6 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|*  7 |    FILTER        |      |       |       |            |          |
|   8 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|*  9 |    FILTER        |      |       |       |            |          |
|  10 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|  11 |  VIEW            |      |     3 |    27 |     9  (34)| 00:00:01 |
|  12 |   SORT UNIQUE    |      |     3 |       |     9  (78)| 00:00:01 |
|  13 |    UNION-ALL     |      |       |       |            |          |
|  14 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|  15 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|  16 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(MONTHS_BETWEEN(SYSDATE@!,:B1)<=216 AND
              MONTHS_BETWEEN(SYSDATE@!,:B2)>=168)
   5 - filter(MONTHS_BETWEEN(SYSDATE@!,:B1)<=252 AND
              MONTHS_BETWEEN(SYSDATE@!,:B2)>=217)
   7 - filter(MONTHS_BETWEEN(SYSDATE@!,:B1)<=780 AND
              MONTHS_BETWEEN(SYSDATE@!,:B2)>=253)
   9 - filter(MONTHS_BETWEEN(SYSDATE@!,:B1)>780)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        538  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> ed
MichaelS
Not sure, but sometimes a simple inlining might work (not to mention to create a view of course ;) ):
select age
  from (select case
                  when months_between (sysdate, c.birth_dt) between 168 and 216 then 1 --14 to 18
                  when months_between (sysdate, c.birth_dt) between 217 and 252 then 2 --18 to 21
                  when months_between (sysdate, c.birth_dt) between 253 and 780 then 3 --21 to 65
                  when months_between (sysdate, c.birth_dt) > 780 then 4 --older than 65
                  else null
               end age
          from mw_clients c)
Aketi Jyuuzou
Hi BluShadow.
Thanks for research.
Laurent Schneider
Not sure, but sometimes a simple inlining might work
(not to mention to create a view of course ;) ):
select age
from (select case
when months_between (sysdate,
c.birth_dt) between 168 and 216 then 1 --14 to 18
when months_between (sysdate,
c.birth_dt) between 217 and 252 then 2 --18 to 21
when months_between (sysdate,
c.birth_dt) between 253 and 780 then 3 --21 to 65
when months_between (sysdate,
c.birth_dt) > 780 then 4 --older than 65
else null
d age
from mw_clients c)
what happened to someone who is 216.5 months old?
;-)
MichaelS
what happened to someone who is 216.5 months old? ;-)
...
else null
....

According to some other thread, it is unknown or not defined or has no age or has no middle name or .... ;)
Laurent Schneider
Well it happened to me 224 months ago ;-)
Jim P

Thanks you all for your help!

I went with Aketi's first solution:

SELECT decode(Least(months_between(sysdate , c.birth_dt),168),168,
       decode(greatest(months_between(sysdate , c.birth_dt),216),216,1,0),0)+
       decode(Least(months_between(sysdate , c.birth_dt),216),216,
       decode(greatest(months_between(sysdate , c.birth_dt),252),252,2,0),0)+
       decode(Least(months_between(sysdate , c.birth_dt),252),252,
       decode(greatest(months_between(sysdate , c.birth_dt),780),780,3,0),0)+
       decode(Least(months_between(sysdate , c.birth_dt),781),781,4,0)+
       decode(greatest(months_between(sysdate , c.birth_dt),167),167,null,0)
FROM mw_clients c

@ Blue shadow, Thanks for the testing on the query!

@ Laurent, Thanks for catching my .5 error. I did change it too overlap, which is what is desired anyways.

@ Michaels, I liked yours the best because it used case instead of decode, but it did not work in forms.

@ Sven, I am looking at adding something similar to our utilities packages, this might be usefull later on.

Thanks everyone for your help!

Jim P.

Message was edited by:
Jim P

Laurent Schneider
well, does not look good to me

why not using 
case
when monthsbetween < 168 then null
when monthsbetween < 216 then 1
when monthsbetween < 252 then 2
when monthsbetween < 780 then 3
else 4
end
Message was edited by:
Laurent Schneider
Its for a record group within in forms, and the case statement is not working.
I should have read this
Laurent Schneider
maybe something like
  decode(greatest(months_between(sysdate , c.birth_dt),168),
      168, null,
      decode(greatest(months_between(sysdate , c.birth_dt),216),
        216,1,
        decode(greatest(months_between(sysdate , c.birth_dt),252),
          252,2,
          decode(greatest(months_between(sysdate , c.birth_dt),780),
          780,3,
          4
        )
      )
    )
  )
Laurent Schneider 
SELECT
months_between(sysdate,birth_dt)/12 age,sysdate,birth_dt, 
decode(Least(months_between(sysdate , c.birth_dt),168),168,
decode(greatest(months_between(sysdate , c.birth_dt),216),216,1,0),0)+
decode(Least(months_between(sysdate , c.birth_dt),216),216,
decode(greatest(months_between(sysdate , c.birth_dt),252),252,2,0),0)+
decode(Least(months_between(sysdate , c.birth_dt),252),252,
decode(greatest(months_between(sysdate , c.birth_dt),780),780,3,0),0)+
decode(Least(months_between(sysdate , c.birth_dt),781),781,4,0)+
decode(greatest(months_between(sysdate , c.birth_dt),167),167,null,0) n
FROM mw_clients c;
       AGE SYSDATE   BIRTH_DT           N
---------- --------- --------- ----------
        18 06-DEZ-07 06-DEZ-89          3
Laurent Schneider 
SELECT
trunc(months_between(sysdate,birth_dt)/12,2) age,sysdate,birth_dt, 
decode(Least(months_between(sysdate , c.birth_dt),168),168,
decode(greatest(months_between(sysdate , c.birth_dt),216),216,1,0),0)+
decode(Least(months_between(sysdate , c.birth_dt),216),216,
decode(greatest(months_between(sysdate , c.birth_dt),252),252,2,0),0)+
decode(Least(months_between(sysdate , c.birth_dt),252),252,
decode(greatest(months_between(sysdate , c.birth_dt),780),780,3,0),0)+
decode(Least(months_between(sysdate , c.birth_dt),781),781,4,0)+
decode(greatest(months_between(sysdate , c.birth_dt),167),167,null,0) n
,
  decode(greatest(months_between(sysdate , c.birth_dt),168),
      168, null,
      decode(greatest(months_between(sysdate , c.birth_dt),216),
        216,1,
        decode(greatest(months_between(sysdate , c.birth_dt),252),
          252,2,
          decode(greatest(months_between(sysdate , c.birth_dt),780),
          780,3,
          4
        )
      )
    )
  )
n2
FROM mw_clients c;

       AGE SYSDATE   BIRTH_DT           N N2                                      
---------- --------- --------- ---------- ----------------------------------------
        21 06-DEZ-07 06-DEZ-86          5 2
Jim P
Laurent,
When I tested mine I did not get the difference between N and N2.

Please see picture: http://www.aidanj.net/laurent.gif

Did I use your query wrong?

Thanks!
Jim P.
Jim P
Laurent,

I see, there is also a difference at age 65. n=0 and n2 = 4.
I now like your better ;)


Thanks so much for your help!
Jim P.
Aketi Jyuuzou

By the way

If Birthday is "2000/02/29" and sysdate is "2003/02/28",
"months_between(sysdate,birth_dt)/12" is wrong age.

for instance,

select
months_between(nowTime,BirthDay)/12 as wrongAge,
trunc((to_number(to_char(nowTime,'YYYYMMDD'))
      -to_number(to_char(BirthDay,'YYYYMMDD')))/10000) as correctAge
from (select to_date('2003/02/28','yyyy/mm/dd') as nowTime,
             to_date('2000/02/29','yyyy/mm/dd') as BirthDay from dual);

wrongAge  correctAge
--------  ----------
       3           2

Maybe you have to use this Laurent Schneider's solution.
http://laurentschneider.com/wordpress/2005/07/how-old-are-you.html

BEDE
If Forms does not know the case statement, why not create a view that comprises the case and select from that view?
There are several cases when it's best to query views in forms, saving lots of processing in the post-query trigger for instance.
Or, in forms, after performing the select, you may use PL/SQL if ... elsif ... to do the works of a case
Jim P
I decided to create a procedure within a package instead. I think it will be much more efficient to only call when needed.

Thanks so much everyone for all your help!

Jim P.
1 - 23
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 4 2008
Added on Dec 5 2007
23 comments
8,457 views