| use between in case on one fild [message #619364] |
Mon, 21 July 2014 23:52  |
 |
hahaie
Messages: 194
Registered: May 2014
|
Senior Member |
|
|
hello,
i used this plsql cod for case on one fild but have error:
(CASE ST.WID_MIN_PRDST WHEN BETWEEN 600 AND 699 THEN '600-699'
WHEN BETWEEN 700 AND 720 THEN '700-720'
WHEN BETWEEN 750 AND 780 THEN '750-780'
END
)GRP_WID
please help me
thanks
[Updated on: Tue, 22 July 2014 00:01] Report message to a moderator |
|
|
|
| Re: use between in case on one fild [message #619365 is a reply to message #619364] |
Mon, 21 July 2014 23:57  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
First, what about giving feedback to your previous question?, http://www.orafaq.com/forum/t/193671/
And, regarding your current topic, post the error details. Error thrown by Oracle has a meaning and there is no reason for you not to mention that.
Edit : I see you updated your original post.
Try this :
CASE
WHEN ST.WID_MIN_PRDST BETWEEN 600 AND 699 THEN '600-699'
WHEN ST.WID_MIN_PRDST BETWEEN 700 AND 720 THEN '700-720'
WHEN ST.WID_MIN_PRDST BETWEEN 750 AND 780 THEN '750-780'
END
[Updated on: Tue, 22 July 2014 00:08] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: use between in case on one fild [message #619372 is a reply to message #619371] |
Tue, 22 July 2014 00:22 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
hahaie wrote on Tue, 22 July 2014 10:47Not understood your solution
Ok, then meditate over the difference among the two(with your glasses  ):
Your's :
(CASE ST.WID_MIN_PRDST WHEN BETWEEN 600 AND 699 THEN '600-699'
WHEN BETWEEN 700 AND 720 THEN '700-720'
WHEN BETWEEN 750 AND 780 THEN '750-780'
END
)GRP_WID
Mine :
CASE
WHEN ST.WID_MIN_PRDST BETWEEN 600 AND 699 THEN '600-699'
WHEN ST.WID_MIN_PRDST BETWEEN 700 AND 720 THEN '700-720'
WHEN ST.WID_MIN_PRDST BETWEEN 750 AND 780 THEN '750-780'
END
And if you still do not understand, then try to understand this :
SQL> SELECT ename,
2 CASE e.sal
3 WHEN /*e.sal*/
4 BETWEEN 1000 AND 3000 THEN
5 'poor'
6 END salary
7 FROM emp e
8 ORDER BY ename
9 /
BETWEEN 1000 AND 3000 THEN
*
ERROR at line 4:
ORA-00936: missing expression
SQL> SELECT ename,
2 CASE /*e.sal*/
3 WHEN e.sal
4 BETWEEN 1000 AND 3000 THEN
5 'poor'
6 END salary
7 FROM emp e
8 ORDER BY ename
9 /
ENAME SALARY
---------- ------
ADAMS poor
ALLEN poor
BLAKE poor
CLARK poor
FORD poor
JAMES
JONES poor
KING
MARTIN poor
MILLER poor
SCOTT poor
ENAME SALARY
---------- ------
SMITH
TURNER poor
WARD poor
14 rows selected.
[Updated on: Tue, 22 July 2014 00:25] Report message to a moderator |
|
|
|
|
|
| Re: use between in case on one fild [message #619375 is a reply to message #619374] |
Tue, 22 July 2014 00:50 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
hahaie wrote on Tue, 22 July 2014 11:02I guess Is there a way that the field name is mentioned only once.
What you are saying is only possible in PL/SQL for CASE construct. My above test cases are SQL.
So, the following syntax is invalid in SQL :
CASE variable
WHEN ... THEN...
WHEN ... THEN...
WHEN ... THEN...
END
But that is not the problem here.
In your case, BETWEEN expects the variable before it. So, you have to write it the way I suggested above.
Edit : Mentioned the cause for OP's issue regarding BETWEEN in CASE construct
[Updated on: Tue, 22 July 2014 01:06] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
