SQL Default Value if no row is returned

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

  • Fallout 76 – Vendor – Great tips
    You thought Fallout 76 only had train station vendors? Far from it. There are many different trading systems in Fallout. The most well-known system is of course the handle with bottle caps, but there...
  • Fallout 76 – Farm Glue
    Glue is one of the most commonly used materials in Fallout 76, especially in the early stages up to level 100. Whether armor or weapons – glue is constantly needed. Glue is also required...
  • Fallout 76 – Farm aluminum
    Aluminium is one of the rare goods in Fallout – but it is needed relatively often. For example, aluminum is needed to repair weapons. But some objects and building materials also need the important...

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...