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'
- 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...
- Happy birthday in MaoriThe 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...
- Happy Birthday in Hawaiian – 3 easy waysHappy 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'
-- 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
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.