SQL Default Value if no row is returned

by ·

SQL

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

  • AI Recruiting
    Everything you need to know about AI recruiting!
    The application process has changed significantly in recent years. Companies are increasingly relying on AI recruiting to automate the selection process and make it more efficient. But what exactly is AI recruiting and what...
  • Flagge von Neuseeland, Neuseeland im Überblick, Alles Gute zum Geburtstag auf Maori
    Happy birthday in Maori
    The Maori are an indigenous people of New Zealand who have a unique culture and traditions – including their own language. There are therefore different ways to congratulate Maori on their birthday. In this...
  • HappyBirthday
    Happy Birthday in Hawaiian – 3 easy ways
    Happy birthday in Hawaiian Happy birthday in Hawaiian means “Hauʻoli Lā Hānau“. This terms are used most often because they are the standard phrases and can always be used. In social networks, but also...

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.

Tags:

You may also like...