Json_table Not Returning Correct Data When Used In A Subquery (Doc ID 2226251.1)

Last updated on APRIL 21, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

json_table not returning correct data when used in a subquery
 
SQL> select 'X'
  from cs_email_test_temp_view cetv2
  where cetv2.employee_id = '0072'
  and cetv2.email_type = 'UNIV';
 
-
X

SQL> select 'X'
  from dual
  where exists (
  select null from cs_email_test_temp_view cetv2
  where cetv2.employee_id = '0072'
  and cetv2.email_type = 'UNIV');
 
no rows selected

The result returned by these two queries should be the same i.e. 'X' since in both cases the select is checking for the same rows.

However, the version that uses the subquery does not return any value even though a row should exist for employee_id "0072" and email type "UNIV"

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms