Sign in or 
Location: The Official Oracle Wiki
Discussion: decode statement
Category:
Discussion Forum /
General Discussion
Share this
Showing 7 posts
|
shanmugarajanbu |
decode statement
Oct 10 2009, 11:04 PM EDT
hi, any one plz help.if emp name as raj, shan, dos then dept no should come as 10 and shan, arun, antony then dept no as 20. how to check this condition using the decode statement. i have written like below one but its not working. DECODE (empname, 'raj', 10, 'shan', 10, 'dos', 10, 'shan', 20, 'arun', 20, 'antony', 20 ) . Do you find this valuable? |
|
naveenhks |
1. RE: decode statement
Oct 14 2009, 12:00 PM EDT
does shan belongs both departmants i.e 10 & 20 , If yes then decode will always return 10 for shan. see below...SQL> r 1* select decode('&ename', 'raj', 10, 'shan', 10, 'dos', 10, 'arun', 20, 'antony', 20, 0 ) result_value from dual Enter value for ename: shan old 1: select decode('&ename', 'raj', 10, 'shan', 10, 'dos', 10, 'shan', 20, 'arun', 20, 'antony', 20, 0 ) result_value from dual new 1: select decode('shan', 'raj', 10, 'shan', 10, 'dos', 10, 'shan', 20, 'arun', 20, 'antony', 20, 0 ) result_value from dual RESULT_VALUE ------------ 10 SQL> r 1* select decode('&ename', 'raj', 10, 'shan', 10, 'dos', 10, 'shan', 20, 'arun', 20, 'antony', 20, 0 ) result_value from dual Enter value for ename: dummy old 1: select decode('&ename', 'raj', 10, 'shan', 10, 'dos', 10, 'shan', 20, 'arun', 20, 'antony', 20, 0 ) result_value from dual new 1: select decode('dummy', 'raj', 10, 'shan', 10, 'dos', 10, 'shan', 20, 'arun', 20, 'antony', 20, 0 ) result_value from dual RESULT_VALUE ------------ 0 Do you find this valuable? |
|
shanmugarajanbu |
2. RE: decode statement
Oct 16 2009, 7:21 AM EDT
Hi Naveen,Thanks, is there is any other way ill get the employee as shan for dept no 10 and 20. Regards, Shan. Do you find this valuable? |
|
naveenhks |
3. RE: decode statement
Oct 16 2009, 9:59 AM EDT
I really don't understand your requirement. What you are trying to accomplish. Are you getting these departments from a table or are these static values. Where are these employee names stored. I think decode is not the solution.I cannot help you until I understand your question. If possible can you please send me the actual query where you are trying to implement this. Regards, Naveen Do you find this valuable? |
linlasj |
4. RE: decode statement
Oct 21 2009, 1:36 PM EDT
Hi,I agree. This looks as another problem in disguise. So, Shan, what is the original issue you are solving? Do you find this valuable? |
|
linlasj |
5. RE: decode statement
Oct 21 2009, 1:37 PM EDT
"Hi,...and the reason for this view of mine is that the DECODE is essentially an IF-statement. And...dynamical values are not done in IF-statements. Do you find this valuable? |
m.noaman |
6. RE: decode statement
Oct 23 2009, 12:24 PM EDT
try bellow case statementi did remove shan from dept. no. 20 cause he is already in 10 SELECT CASE WHEN UPPER(EMPNAME) IN ('RAJ','SHAN','DOS') THEN 10 WHEN UPPER( EMPNAME) IN ('ARUN','ANTONY') THEN 20 END AS DEPARTMENT FROM .... Do you find this valuable? |
