Oracle NVL returning inconsistence result when used with/without CASE statement

Question

Can anybody help me to understand why oracle db is returning inconsistence result when used with/without CASE statement.

SELECT NVL(CASE WHEN '' IS NULL THEN NULL ELSE NULL END ,TO_DATE('19010101', 'YYYYMMDD')) a,
   NVL(CASE WHEN '' IS NOT NULL THEN NULL ELSE NULL END ,TO_DATE('19010101', 'YYYYMMDD')) b,
   NVL(CASE WHEN '' = '' THEN NULL ELSE NULL END ,TO_DATE('19010101', 'YYYYMMDD')) c,
   NVL(NULL ,TO_DATE('19010101', 'YYYYMMDD')) d from dual;

My actual query uses TRUNC(NVL(:NEW_FORM_TIME_END, TO_DATE('19010101', 'YYYYMMDD'))) >= TRUNC(SYSDATE - INTERVAL '2' DAY) where NEW_FORM_TIME_END could be empty string or NULL but I'm getting this error ORA-06550: line 240, column 91: PLS-00306: wrong number or types of arguments in call to '>=' ORA-06550: line 229, column 9: PL/SQL: Statement ignored

Lukas Eder · Accepted Answer · 2021-04-01T08:35:59.483

Your case expressions are of type VARCHAR2 which is the default type if you're not providing explicit types. A NULL literal doesn't have a type. NVL(<varchar2>, <date>) applies an implicit type conversion on the <date> argument. In other RDBMS, you'd simply get an error because of incompatible types. The PL/SQL version of your expression probably also suffers from a similar problem, although not exactly the same one as you're presenting in your query.

Just make sure you always compare the same types.

Try this to check:

SELECT 
  NVL(CAST(CASE WHEN '' IS NULL THEN NULL END AS DATE), DATE '1901-01-01') a,
  NVL(CAST(CASE WHEN '' IS NOT NULL THEN NULL END AS DATE), DATE '1901-01-01') b,
  NVL(CAST(CASE WHEN '' = '' THEN NULL END AS DATE), DATE '1901-01-01') c,
  NVL(NULL, DATE '1901-01-01') d
FROM dual;

Or alternatively, create a view from your table and then check the dictionary:

CREATE VIEW v AS
SELECT 
  NVL(CASE WHEN '' IS NULL THEN NULL END, DATE '1901-01-01') a,
  NVL(CASE WHEN '' IS NOT NULL THEN NULL END, DATE '1901-01-01') b,
  NVL(CASE WHEN '' = '' THEN NULL END, DATE '1901-01-01') c,
  NVL(NULL, TO_DATE('19010101', 'YYYYMMDD')) d
FROM dual;

SELECT column_name, data_type
FROM all_tab_cols
WHERE table_name = 'V'
ORDER BY column_name;

Yielding

|COLUMN_NAME|DATA_TYPE|
|-----------|---------|
|A          |VARCHAR2 |
|B          |VARCHAR2 |
|C          |VARCHAR2 |
|D          |DATE     |

Thanks:) I Didn't know about the implicit type conversion – Rahil Husain Apr 01 '21 at 08:55 — Rahil Husain, Apr 01 '21 at 08:55

score 0 · Answer 2 · answered Apr 01 '21 at 07:22

Let's analyze all of them:

NVL(CASE WHEN '' IS NULL THEN NULL ELSE NULL END ,TO_DATE('19010101', 'YYYYMMDD'))

This should return TO_DATE('19010101', 'YYYYMMDD')) since '' is the same as NULL in Oracle and the THEN NULL branch is returned.

NVL(CASE WHEN '' IS NOT NULL THEN NULL ELSE NULL END ,TO_DATE('19010101', 'YYYYMMDD'))

This one should also return TO_DATE('19010101', 'YYYYMMDD')) since '' IS NOT NULLisfalse and theELSE NULL` branch is returned.

NVL(CASE WHEN '' = '' THEN NULL ELSE NULL END ,TO_DATE('19010101', 'YYYYMMDD'))

In Oracle, '' = '' translates to NULL = NULL, which in SQL, evaluates to NULL, so `TO_DATE('19010101', 'YYYYMMDD')) is returned.

NVL(NULL ,TO_DATE('19010101', 'YYYYMMDD'))

This one should also return TO_DATE('19010101', 'YYYYMMDD'))

Oracle NVL returning inconsistence result when used with/without CASE statement

2 Answers2