Home » SQL & PL/SQL » SQL & PL/SQL » relational operator in decode
relational operator in decode [message #303078] Wed, 27 February 2008 22:01 Go to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
Dear all,
I would like to know weather we can use relational operator in decode function or not because i am not able to find any help regarding that

and i would not like to use case statement because we are using oracle 8i and case does not seem to work on that

expected query

decode(sal, sal<0, 'Low Salary', sal<0 , 'High Salary',sal)

Re: relational operator in decode [message #303090 is a reply to message #303078] Wed, 27 February 2008 23:31 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Not possible. Realize the difference between expression and condition, as described in http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm. Only expressions are allowed in DECODE statement.

In your example, you may use SIGN function.
Re: relational operator in decode [message #303100 is a reply to message #303078] Thu, 28 February 2008 00:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CASE works in 8i but not in PL/SQL:
SQL> select case when dummy='X' then 1 else 2 end from dual;
CASEWHENDUMMY='X'THEN1ELSE2END
------------------------------
                             1

1 row selected.

SQL> @v

Version Oracle : 8.1.7.4.0

Regards
Michel
Re: relational operator in decode [message #303101 is a reply to message #303078] Thu, 28 February 2008 00:11 Go to previous messageGo to next message
hb_venkatesh
Messages: 9
Registered: October 2007
Location: india
Junior Member

we could not use relation opertor in decode function because it
could not suppoet <,>,>=,<=

>decode(sal, sal<0, 'Low Salary', sal<0 , 'High Salary',sal)
you will try like
decode(sal, sign(sal),-1,'Low Salary',1, 'High Salary',0)

Before that youjust read about SIGN function

venki
Re: relational operator in decode [message #303106 is a reply to message #303101] Thu, 28 February 2008 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not what flyboy said and pointed to?

Regards
Michel
Re: relational operator in decode [message #303112 is a reply to message #303101] Thu, 28 February 2008 00:43 Go to previous messageGo to next message
hb_venkatesh
Messages: 9
Registered: October 2007
Location: india
Junior Member

Hello Michal,

>Is this not what flyboy said and pointed to?
select decode( sign(100),-1,'negative',1,'positive',0) from dual union all
select decode(sign(-100), -1,'negative',1,'positive',0) from dual

I'made a small mistake from my previous query
>decode(sal, sal<0, 'Low Salary', sal>0 , 'High Salary',sal)
>you will try like
>decode(sal, sign(sal),-1,'Low Salary',1, 'High Salary',0)
remove it this and
decode(sign(sal),-1,'Low Salary',1, 'High Salary',0)
If you use sign() function for eg:
Query Result
sign(-100) => -1 (if less than 0, the result is -1)
sign(0) => 0 (if value is 0, the result is 0)
sign(100) => 1 (if greater than 0, the result is 1)

venki
Re: relational operator in decode [message #303113 is a reply to message #303112] Thu, 28 February 2008 00:46 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does this answer to my question "Is this not what flyboy said and pointed to?" (and I add now without error)?

Regards
Michel
Previous Topic: is it possible to switch between tables in query ?
Next Topic: Help me
Goto Forum:
  


Current Time: Tue Dec 06 14:17:30 CST 2016

Total time taken to generate the page: 0.05718 seconds