Home » SQL & PL/SQL » SQL & PL/SQL » Ternary Operator in PL/SQL (Oracle 10g on UNIX)
Ternary Operator in PL/SQL [message #343180] Tue, 26 August 2008 09:44 Go to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
I know that there is NO ternary operator in PL/SQL.

Does anybody has an idea how to simulte in PL/SQL?

Thanks
Re: Ternary Operator in PL/SQL [message #343189 is a reply to message #343180] Tue, 26 August 2008 10:08 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Hi Soham,
We can do this using CASE statements in PLSQl
Ternary operation (a>b)?a:b says if a is greater than b return a or b
In PLSQL
select
(case when sysdate>'20-Aug-2008' then 'YES' else 'NO' end)result from dual
will result in 'YES'.
Regards
Ramya
Re: Ternary Operator in PL/SQL [message #343191 is a reply to message #343180] Tue, 26 August 2008 10:13 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
Thanks Ramya.

This round about trick should be enough for me.

Thanks
Re: Ternary Operator in PL/SQL [message #687474 is a reply to message #343189] Mon, 13 March 2023 19:29 Go to previous message
mathguy
Messages: 105
Registered: January 2023
Senior Member
Strictly speaking, the ternary operator in C and similar has the general form
( condition ? expr1 : expr2 )
where condition is a boolean expression and expr1 and expr2 are expressions returning the same data type (which is also the data type returned by the ternary operator).

This can be simulated in PL/SQL as has already been shown; however, there is a subtlety here. Conditions in PL/SQL (and in SQL) are not Boolean; they are three-valued, they can evaluate to TRUE, FALSE or UNKNOWN. So strictly speaking in PL/SQL (and in SQL), maximally, we might want a quaternary operator, something like
( condition ? expr1 : expr2 : expr3 )
returning expr3 when condition evaluates to UNKNOWN. The CASE implementation suggested 15 years ago combines the FALSE and the UNKNOWN alternatives together. The quaternary operator can also be implemented with a CASE expression, with some care - something like

case when     <condition> then <expr1>
     when not <condition> then <expr2>
     else                      <expr3> end

[Updated on: Mon, 13 March 2023 19:31]

Report message to a moderator

Previous Topic: XML parsing performance
Next Topic: Suggestion on count via sql statement
Goto Forum:
  


Current Time: Mon Mar 04 16:31:59 CST 2024