SQL Default Value if no row is returned
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.
-- Use the COALESCE statement, MAX produces automatic a NULL value SELECT COALESCE(MAX(dummy), 'Z') result FROM DUAL WHERE DUMMY = 'Y'; -- 'X'
- Fallout 76 – Vendor – Great tips
- Fallout 76 – Farm GlueGlue 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 aluminumAluminium 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'
-- 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'