You Asked

Hello i just wanted to ask you the (CASE WHEN THEN END;) is the ternary operator in oracle sql - pl/sql? thanks. I provided some example code above of java and pl/sql thanks!.

/*plsql code*/
DECLARE 
myage NUMBER := 28;
statusage VARCHAR(200) := NULL;
BEGIN
statusage := CASE WHEN myage >= 70 THEN 'third age!'
WHEN myage < 70 AND myage >=50 THEN 'second age!'
ELSE 'first age!' END;
DBMS_OUTPUT.PUT_LINE('I am ' || myage || ' years old and i am in the ' || statusage);
END;

// Java code
class Main 

{
    
public static void main(String[] args) 
    
{

int myage = 28;
String statusage = null;
statusage = (myage >= 70) ? "third age!" : (myage < 70 && myage >=50) ? "second age!" : "first age!";
System.out.println("\nI am " + myage + " years old and i am in the " + statusage);
    
}

}

and Chris said...

There is no ternary operator in SQL or PL/SQL. Case expressions are one way to do this. You could also use IF statements in PL/SQL.

Decode in SQL is probably closer in spirit to a ternary operator than CASE:

var myage number;
exec :myage := 28;
select decode ( 
  :myage > 70, 
  true, 'third age!', 
  decode (
    :myage < 70 AND :myage >=50, 
    true, 'second age!', 
    'first age! ' 
  )
);

DECODE(:MYA
-----------
first age!

Rating

(1 rating)

Comments

mathguy, February 06, 2026 - 11:12 pm UTC

The ternary operator makes sense when the language recognizes two-valued logic. In SQL, if anything, you would need a quaternary operator - assign values when a logical expression is TRUE, another when it is FALSE, and yet another when it is NULL.

Besides the CASE expression and variants, Oracle SQL and PL/SQL has another "operator" that is very close in spirit to a ternary operator: the function NVL2. This evaluates an expression (of various data types; since Oracle 19, Boolean expressions are permitted) and returns one value or another, depending on whether the evaluated expression is NULL or non-NULL. So the logical condition is very special: IS NULL vs IS NOT NULL. Note that THIS particular expression can only evaluate to TRUE or FALSE, never to NULL - so it does make sense to have a ternary operator for it.

For more general expressions, you must state what value you want assigned if the Boolean expression has the value NULL (neither TRUE nor FALSE). If you want NULL to be treated the same as FALSE, you could use the NULLIF function inside NVL2, to "null"ify the Boolean expression when it is FALSE, like this:

select nvl2( nullif(1<2, false) , '_True_', '_False_') as result;



RESULT 
-------
_True_

If the Boolean expression (in this case, 1<2) passed to NULLIF evaluates to TRUE, then NULLIF itself returns the Boolean value TRUE (or 1). Since this value is not NULL, NVL2 returns the first "return expression" (in the example, the string '_True_'). If the Boolean expression passed to NULLIF evaluates to NULL or to FALSE, NULLIF returns NULL, and then NVL2 returns the second "return expression" ('_False_'). Exactly as the ternary operator of C, Java etc.

DECODE is simpler, but at its core DECODE behaves more like a lookup function, not a ternary operator. NVL2 is truly a ternary operator, but only for a very specific Boolean input; that can be made fully general like I showed above.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

DBMS_OUTPUT

Ternary Operator in SQL - PL/SQL

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

DBMS_OUTPUT