| Is it a bug?!!! [message #39474] |
Thu, 18 July 2002 09:21  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Hi,
When I
SQL> SELECT
2 cr_id,
3 (CASE WHEN TO_CHAR(dob,'MM')>=04
4 THEN TO_CHAR(dob,'YYYY')
5 WHEN TO_CHAR(dob,'MM')<=03
6 THEN TO_CHAR(dob,'YYYY')+1
7 END)
8 FROM mr_cus
9 GROUP BY cr_id,dob
10
SQL> /
THEN TO_CHAR(dob,'YYYY')+1
*
ERROR at line 6:
ORA-00932: inconsistent datatypes
If I comment out the second WHEN clause it works.
if instead of second WHEN I do ELSE I get the same message.
And I really need this CASE work (I can do a DECODE of course, but it will complicate my task that is much bigger than the above query)!
If I am doing something wrong whith the syntax it's one thing, but if it is a bug then I should think of something else. I have 8.1.7.3.0
Any ideas?
|
|
|
|
| Re: Is it a bug?!!! [message #39477 is a reply to message #39474] |
Thu, 18 July 2002 11:01  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
No, it is not a bug - by adding one to the return value of to_char, the value is implicitly being changed to a number which does not match the other datatypes.
Actually, you can simplify this column as just:
to_char(add_months(dob, case when to_char(dob, 'mm') <= '03' then 12 else 0 end), 'yyyy') |
|
|
|
| Re: Is it a bug?!!! [message #39480 is a reply to message #39474] |
Thu, 18 July 2002 13:04 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Thank you,
before I red your message I already made it a function,but I will try your idea and see if it works faster (I am dealing with millions of records.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
