Hi, Can we use Decode query with ' 1

[KhushiRaina]

Hi,
Can we use Decode query with 'In' statement
Eg:if location In (1,4,5) then return ('Metro')
elsif location In (19,20,25) then return ('Main')
end if
So on....
How can i check for renge of values, currently i am doing as:
Select location,decode(location,1,'Metro',4,'Metro',
19,'Main' and so on.....

But i cannot keep checking for individual nos..
Could anyone suggest me a better option
Thanks..
Khushi.

 

[jee]

I am not totally clear on what you are trying to do, but perhaps the "CASE" construct in 8i will help you - something like this:

SELECT CASE
WHEN location >= 1 and location <= 5 THEN 'Metro'
WHEN location >= 19 and location <= 25 THEN 'Main'
ElSE 'Unknown'
END
FROM table
WHERE...

Note: In 8i CASE is not supported in PL/SQL so if you want to use this in PL/SQL code you will have to use dynamic SQL.

 

[KhushiRaina]

hi,
Thanks for such a quick response but how i want is:-
I need to merge two city data and then if city is 'Unknown' then again split the data into cities depending on location so my original query is like this :-
Eg: Select decode(city,'A','B','Unknown',
decode(location,1,'Metro',4,'Metro',21,'Main','None'),City)
from....
So if city is 'A' then to be merged with city 'B' and then if city 'Unknown' then if location IN (1,4) then city is Metro and so on...
The nos need not to be in sequence so i cannot use between clause.
So could you suggest me any solution for this....
THanks,
Khushi

 

[fmorel]

Great !!!!
Thanks a lot Jee !

I did not know that CASE function exist... I cannot find any information about case in my 8.1.7 documentation, by I tested it and it seems to work well. It will be much easier and lisible than tricky decodes !

Could you give the reference of a documentation for case ? Limitations, etc.

KhushiRaina , to test ranges with decodes, you can use the sign function: for example, if you want to test the range [2,6], you will test:

decode ( sign(x-1)+sign(7-x),2,'In range','Out of range')

sign = 1 if y>0
sign = 0 if y=0
sign = -1 if y<0

 

[KhushiRaina]

hi,
fmorel, could you give an example to explain the above solution.I did not get what you are saying.
Jee, if i use case then can i name this case stmt and group by on this case stmt, how can it be done.Any idea???

Thanks
khushi