OraFAQ Forum: SQL & PL/SQL » Ternary Operator in PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Ternary Operator in PL/SQL (Oracle 10g on UNIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Ternary Operator in PL/SQL [message #343180]

Tue, 26 August 2008 09:44

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

Report message to a moderator

Re: Ternary Operator in PL/SQL [message #343189 is a reply to message #343180]

Tue, 26 August 2008 10:08

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

Report message to a moderator

Re: Ternary Operator in PL/SQL [message #343191 is a reply to message #343180]

Tue, 26 August 2008 10:13

soham.desai@gmail.com
Messages: 21
Registered: August 2008

Junior Member

Thanks Ramya.

This round about trick should be enough for me.

Thanks

Report message to a moderator

Re: Ternary Operator in PL/SQL [message #687474 is a reply to message #343189]

Mon, 13 March 2023 19:29

mathguy
Messages: 108
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 26 00:19:51 CDT 2025