Is there any issue here using select statement in nvl function?

Question

I have this below simple query:

 Select * from Table_A 
    where approved_date > NVL((Select Max(Last_Processed_Time) from Table_B),
TO_DATE('2021/04/19 11:13:44', 'yyyy/mm/dd hh24:mi:ss'))

If I run the query, it doesn't return any rows, but, it return records when I run the query as below:

Select * from Table_A 
where approved_date > TO_DATE('2021/04/19 11:13:44', 'yyyy/mm/dd hh24:mi:ss') 

I have to check Table_B record count, In my case, Table_B is empty. I am checking if Table_B is empty, and if it is empty, I will use a hardcoded date. so I am using NVL. Am I missing anything here?

Answer 1

Am I missing anything here?

If (Select Max(Last_Processed_Time) from Table_B) is greater than the largest approved_date in table_a then you will filter out all the rows and the result set will be empty.

but, it return records when I run the query as below:

Select *
from   Table_A 
where  approved_date > TO_DATE('2021/04/19 11:13:44', 'yyyy/mm/dd hh24:mi:ss') 

Then you probably have rows where the approved_date is greater than 2021-04-19T11:13:44 but is not greater than the maximum last_processed_time in table_b.

Answer 2

I have created table_b and table_a

CREATE table table_b(
  Last_Processed_Time date
);

CREATE table table_a(
  approved_date DATE
);

INSERT INTO table_a SELECT hire_date FROM EMPLOYEES;

[2021-04-21 14:46:02] 107 rows affected in 54ms

And your query is working fine

Select * from table_a 
where approved_date > NVL((Select Max(Last_Processed_Time) from Table_B), TO_DATE('1999/04/19 11:13:44', 'yyyy/mm/dd hh24:mi:ss'))

And I got the rows.

APPROVED_DATE
-------------------
1999-12-07 00:00:00
1999-08-10 00:00:00
1999-11-16 00:00:00
2000-03-08 00:00:00
1999-12-12 00:00:00
2000-02-06 00:00:00
1999-10-15 00:00:00
2000-01-29 00:00:00
1999-11-23 00:00:00
2000-01-24 00:00:00
2000-02-23 00:00:00
2000-03-24 00:00:00
2000-04-21 00:00:00
2000-04-21 00:00:00
1999-05-24 00:00:00
2000-01-04 00:00:00
1999-06-21 00:00:00
2000-02-03 00:00:00
1999-12-19 00:00:00
1999-06-21 00:00:00
2000-01-13 00:00:00

[2021-04-21 14:46:23] 21 rows retrieved starting from 1 in 106ms (execution: 44ms, fetching: 62ms)