Home » SQL & PL/SQL » SQL & PL/SQL » CASE not working  () 1 Vote
CASE not working [message #250455] Tue, 10 July 2007 01:39 Go to next message
vinayakawasthi
Messages: 4
Registered: July 2005
Junior Member
hi all,

I have the following query which is giving error. Can someone put some thoughts:

select 'Step '||tas.STEP_NUMBER||':'|| tasd.DESCRIPTION,tas.STEP_STATUS_CODE,
tarfe.REQUEST_NUMBER, tarfe.STATUS "Request Status",
tasd.NAME,
CASE
WHEN tasd.NAME='LINE_MANAGER' THEN NULL
WHEN tasd.NAME='ROLE_APPROVER' THEN
SELECT tard.APPROVER_ID
FROM VW_AP_PARSE_REQUEST_OVERVIEW vpro, TBL_AP_ROLE_DEFAULT tard
WHERE vpro.REQUEST_NUMBER=tarfe.REQUEST_NUMBER
AND vpro.ROLE_ID=tard.ROLE_ID
WHEN tasd.NAME='HR_APPROVER' THEN
SELECT tard.APPROVER_ID
FROM TBL_AP_APPROVER taa, TBL_AP_POPULATION_APPROVER tapa
WHERE taa.APPROVER_ID=tapa.APPROVER_ID
AND tapa.POPULATION_CODE='HR'
WHEN tasd.NAME='GMB_GEB_APPROVER' THEN
SELECT tard.APPROVER_ID
FROM TBL_AP_APPROVER taa, TBL_AP_POPULATION_APPROVER tapa
WHERE taa.APPROVER_ID=tapa.APPROVER_ID
AND tapa.POPULATION_CODE='GEB_GMB'
END approver_id
FROM TBL_AP_STEP tas,
TBL_AP_STEP_DEFINITION tasd,
TBL_AP_STEP_STATUS tass,
TBL_AP_REQUEST_FROM_EMPLOYEE tarfe
WHERE tas.PROCESS_DEFINITION_NUMBER=tasd.PROCESS_DEFINITION_NUMBER
AND tas.STEP_DEFINITION_NUMBER=tasd.STEP_DEFINITION_NUMBER
AND tarfe.REQUEST_NUMBER=tas.REQUEST_NUMBER
AND tarfe.REQUEST_NUMBER=197

this gives "ORA-00936: missing expression"

Please help me.
Re: CASE not working [message #250463 is a reply to message #250455] Tue, 10 July 2007 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Please copy and paste your screen (execute in SQL*Plus).
How can we know on which line you have the error?

Regards
Michel
Re: CASE not working [message #250464 is a reply to message #250455] Tue, 10 July 2007 02:35 Go to previous messageGo to next message
sehgal.best
Messages: 7
Registered: February 2007
Junior Member
You cant use select query in case .. like you have used.
Re: CASE not working [message #250469 is a reply to message #250464] Tue, 10 July 2007 03:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
sehgal.best wrote on Tue, 10 July 2007 09:35
You cant use select query in case .. like you have used.

Wrong. you mean that YOU can't Wink. U need to use brackets:
Here's a small example using the HR schema on my 10g XE:
SQL> SELECT e.last_name
  2       , e.department_id
  3       , CASE
  4            WHEN department_id = 100
  5               THEN NULL
  6            WHEN department_id = 60
  7               THEN (SELECT l.city
  8                     FROM   locations l
  9                          , departments d
 10                     WHERE  d.department_id = e.department_id
 11                     AND    l.location_id = d.location_id)
 12            WHEN department_id = 90
 13               THEN (SELECT department_name
 14                     FROM   departments
 15                     WHERE  department_id = e.department_id)
 16         END mytest
 17  FROM   employees e
 18  WHERE  ROWNUM < 11
 19  /

LAST_NAME                 DEPARTMENT_ID MYTEST
------------------------- ------------- ------------------------------
King                                 90 Executive
Kochhar                              90 Executive
De Haan                              90 Executive
Hunold                               60 Southlake
Ernst                                60 Southlake
Austin                               60 Southlake
Pataballa                            60 Southlake
Lorentz                              60 Southlake
Greenberg                           100
Faviet                              100

10 rows selected.


Our 9i test database can handle it as well (in the SCOTT schema):
SQL> SELECT e.ename
  2       , e.deptno
  3       , CASE
  4            WHEN deptno = 10
  5               THEN NULL
  6            WHEN deptno = 30
  7               THEN (SELECT d.loc
  8                     FROM   dept d
  9                     WHERE  d.deptno = e.deptno)
 10            WHEN deptno = 20
 11               THEN (SELECT d.dname
 12                     FROM   dept d
 13                     WHERE  d.deptno = e.deptno)
 14         END mytest
 15  FROM   emp e
 16  WHERE  ROWNUM < 11
 17  /

ENAME          DEPTNO MYTEST
---------- ---------- --------------
KING               10
BLAKE              30 CHICAGO
CLARK              10
JONES              20 RESEARCH
MARTIN             30 CHICAGO
ALLEN              30 CHICAGO
TURNER             30 CHICAGO
JAMES              30 CHICAGO
WARD               30 CHICAGO
FORD               20 RESEARCH

10 rows selected.


MHE
icon10.gif  Re: CASE not working [message #250647 is a reply to message #250469] Tue, 10 July 2007 13:46 Go to previous message
sehgal.best
Messages: 7
Registered: February 2007
Junior Member
oops Sad .. i will take care next time
Previous Topic: how write the message into a file
Next Topic: ORA-22992 Error
Goto Forum:
  


Current Time: Wed Dec 07 04:36:09 CST 2016

Total time taken to generate the page: 0.08939 seconds