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 last-minute vacation destinations for 20241. Kathmandu, Nepal Overview Kathmandu, the capital of Nepal, is … Read more
- 10 ways to wish Spaniards a happy birthdayBirthdays in Spain are celebrated with much warmth and joy, … Read more
- 10 ways to wish Swiss people a happy birthdaySwitzerland, with its rich tapestry of cultures and languages, offers … Read more
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
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.