| Decode help [message #372895] |
Thu, 15 March 2001 22:30  |
andy
Messages: 92
Registered: December 1999
|
Member |
|
|
what is wrong with this, when I run it it says missing right paren and puts the * under the , between hiredate and the "<"
SELECT hiredate, DECODE (hiredate,
(hiredate,"<"to-date('01-jan-81'),count(hiredate),hiredate)
from emp;
disreguard thequotes around the "<" in the statement
the format won't let me post the whole statement without them
Thanks
Andy
|
|
|
|
| Re: Decode help [message #372897 is a reply to message #372895] |
Fri, 16 March 2001 01:27  |
Priya Rajkumar
Messages: 5
Registered: March 2001
|
Junior Member |
|
|
Hi Andy,
From what I understood from your question,
Are you trying to do any greater/lesser kind of comparison? DECODE can be compared only for equality. Eg.,
DECODE(SEX_CODE, 'M', 'Male', 'F', 'Female', 'Unknown')
Hope this helps, if not mail me with the whole statement at the above mentioned email address.
Priya Rajkumar
|
|
|
|
| Re: Decode help [message #372900 is a reply to message #372895] |
Fri, 16 March 2001 07:07 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
If I am interpreting what you want to do correctly there are several things wrong.
1. '...(hiredate,"<"to-date('01-jan-81')...'
you can not directly perform an <, >, or =, operation in a decode statement. you must use the Sign function
Sign(hiredate - to_date('01-jan-81'))
Sign will return:
1 if the equation evaluates to a positive number,
0 if the equation evaluates as being equal,
-1 if the equation evaluates to a negative number.
sample decode with sign:
select decode( Sign(val1- val2), 1, 'val1 is > val2', 0, 'val1 = val2', -1, 'val1 < val2')
2. If this statement '...(hiredate,"<"to_date('01-jan-81')...' was valid you would have to remove the comma after hiredate.
3. you do have 4 left parenthesis and 3 right parenthesis.
If this statement '...(hiredate,"<"to_date('01-jan-81')...' was valid you would need another parenthesis after the to date function to match the one prefixing hiredate
'...(hiredate < to_date('01-jan-81'))...'
4. '...count(hiredate),hiredate)...'
you are returning two different value types. count will return a data type of number and and hiredate is data type of date. All return values of the decode must be of the same data type.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
