Werbung / Advertisements

SQL Default Value if no row is returned

SQL
Werbung / Advertisements

Sometimes default values are necassary. Normally you can use NVL or NVL2 when you get NULL values as return value. But it is also possible, that no row is returned. In this case, NVL or NVL2 is not working, cause you get no value back – not even NULL. Therefore, there are some options – like producing a NULL value.

COALESCE

-- Use the COALESCE statement, MAX produces automatic a NULL value
SELECT COALESCE(MAX(dummy), 'Z') result
FROM DUAL
WHERE DUMMY = 'Y'; -- 'X'

Current Posts

  • 10 ways to wish Spaniards a happy birthday
    Werbung / Advertisements Birthdays in Spain are celebrated with much warmth and joy, reflecting the country’s vibrant culture and deep-rooted traditions. Understanding the Spanish way of celebrating can turn a simple birthday wish into...
  • 10 ways to wish Swiss people a happy birthday
    Werbung / Advertisements Switzerland, with its rich tapestry of cultures and languages, offers a unique blend of traditions, especially when it comes to celebrating birthdays. Understanding the cultural nuances behind birthday wishes can transform...
  • Navigating Kotor: Tips and Tricks for First-Time Visitors
    Welcome to Kotor Werbung / Advertisements Nestled along the Adriatic coast, Kotor, Montenegro, is a picturesque city that beckons travelers with its blend of historic charm, breathtaking landscapes, and rich cultural heritage. A UNESCO...

NVL and MAX

-- Use the NVL statement, MAX produces automatic a NULL value
SELECT NVL(MAX(dummy), 'Z') result
FROM DUAL
WHERE DUMMY = 'Y'; -- 'X'

EXISTS

-- Use the EXISTS statement in combination with CASE
SELECT CASE
    WHEN EXISTS(SELECT 1
                FROM DUAL
                WHERE DUMMY = 'Y') -- 'X'
    THEN (SELECT DUMMY
          FROM DUAL
          WHERE DUMMY = 'Y') -- 'X'
    ELSE 'Z' result
    END
FROM DUAL;

COUNT and MIN

-- Use the COUNT function in combination with CASE
-- MIN is needed to identify the correct value
SELECT CASE
    WHEN COUNT(1) > 0
        THEN MIN(DUMMY)
        ELSE 'Z'
    END result
FROM DUAL
WHERE DUMMY = 'Y'; -- 'X'

More about SQL and PL/SQL

Werbung / Advertisements

There are lots of blog posts about SQL, PL/SQL and SQL developer. If you just search for code examples you can also use the SQL Guide with lots of examples.

You may also like...

Werbung / Advertisements