Oracle : Select distinct doesn't work when cur

my need is to make a reports with a list of some people and their invoice; Since I am generating some XML (XML publisher) a way is to use cursor expression. The SQL request consist of a first cursor that give a list of contact(s), and a second cursor inside the first one list the invoice(s) for each contact. The problem is that the contact SQL request returns several times the same contact (as many as he has invoices in fact) so I need to use distinct, and it doesn't work !

To have a request that works everywhere for reproducibility purpose i will use sql request based on oracle system table all_table and dual ;

SQL request reproducing a list of contacts :

select 
    'ALBERT EINSTEIN' CONTACT_NAME 
  from 
  ALL_VIEWS
  where view_name IN ( 'ALL_INDEXES', 'ALL_TABLES') ;

=> ALBERT EINSTEIN ALBERT EINSTEIN

SQL request reproducing a list of (1) invoices connected to the contact :

SELECT 123456 INVOICE_NUMBER, 10000 INVOICE_AMOUNT, 'EUR' INVOICE_CURRENCY FROM DUAL  ;

123456 10000 EUR

So the SQL for the report using cursor expression is :

select distinct
cursor (
  select 
    distinct 
    'ALBERT EINSTEIN' CONTACT_NAME 
    ,CURSOR (SELECT 123456 INVOICE_NUMBER, 10000 INVOICE_AMOUNT, 'EUR' INVOICE_CURRENCY FROM DUAL ) AS INVOICES
  from 
  ALL_VIEWS
  where view_name IN ( 'ALL_INDEXES', 'ALL_TABLES')
  ) AS CONTACTS
  from dual ;

so to generate XML :

select dbms_xmlgen.getxml('
select distinct
cursor (
  select 
    distinct 
    ''ALBERT EINSTEIN'' CONTACT_NAME 
    ,CURSOR (SELECT 123456 INVOICE_NUMBER, 10000 INVOICE_AMOUNT, ''EUR'' INVOICE_CURRENCY FROM DUAL ) AS INVOICES
  from 
  ALL_VIEWS
  where view_name IN ( ''ALL_INDEXES'', ''ALL_TABLES'')
  ) AS CONTACTS
  from dual 
 ') from dual
;

that is :

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <CONTACTS>
   <CONTACTS_ROW>
    <CONTACT_NAME>ALBERT EINSTEIN</CONTACT_NAME>
    <INVOICES>
     <INVOICES_ROW>
      <INVOICE_NUMBER>123456</INVOICE_NUMBER>
      <INVOICE_AMOUNT>10000</INVOICE_AMOUNT>
      <INVOICE_CURRENCY>EUR</INVOICE_CURRENCY>
     </INVOICES_ROW>
    </INVOICES>
   </CONTACTS_ROW>
   <CONTACTS_ROW>
    <CONTACT_NAME>ALBERT EINSTEIN</CONTACT_NAME>
    <INVOICES>
     <INVOICES_ROW>
      <INVOICE_NUMBER>123456</INVOICE_NUMBER>
      <INVOICE_AMOUNT>10000</INVOICE_AMOUNT>
      <INVOICE_CURRENCY>EUR</INVOICE_CURRENCY>
     </INVOICES_ROW>
    </INVOICES>
   </CONTACTS_ROW>
  </CONTACTS>
 </ROW>
</ROWSET>

=> show two times the same contact with the same invoice that is useless, and not expected since distinct is used.

By commenting the second cursor all the distinct works :

select dbms_xmlgen.getxml('
select distinct
cursor (
  select 
    distinct 
    ''ALBERT EINSTEIN'' CONTACT_NAME 
    --,CURSOR (SELECT 123456 INVOICE_NUMBER, 10000 INVOICE_AMOUNT, ''EUR'' INVOICE_CURRENCY FROM DUAL ) AS INVOICES
  from 
  ALL_VIEWS
  where view_name IN ( ''ALL_INDEXES'', ''ALL_TABLES'')
  ) AS CONTACTS
  from dual 
 ') from dual
;

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <CONTACTS>
   <CONTACTS_ROW>
    <CONTACT_NAME>ALBERT EINSTEIN</CONTACT_NAME>
   </CONTACTS_ROW>
  </CONTACTS>
 </ROW>
</ROWSET>

So the problem arise when there is a cursor inside a cursor only.

Does some people have a workaround about that ?

回答1:

i think you need distinct output as like below

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <CONTACTS>
   <CONTACTS_ROW>
    <CONTACT_NAME>ALBERT EINSTEIN</CONTACT_NAME>
    <INVOICE_NUMBER>123456</INVOICE_NUMBER>
    <INVOICE_AMOUNT>10000</INVOICE_AMOUNT>
    <INVOICE_CURRENCY>EUR</INVOICE_CURRENCY>
   </CONTACTS_ROW>
  </CONTACTS>
 </ROW>
</ROWSET>

for this no second cursor is required. you can achive like below

select dbms_xmlgen.getxml('
select 
cursor (
  select 
    distinct 
    ''ALBERT EINSTEIN'' CONTACT_NAME 
    , 123456 INVOICE_NUMBER, 10000 INVOICE_AMOUNT, ''EUR'' INVOICE_CURRENCY 
  from 
  ALL_VIEWS
  where view_name IN ( ''ALL_INDEXES'', ''ALL_TABLES'')
  ) AS CONTACTS
  from dual 
 ') from dual
;

since the distint is different in this occasion. hence no sub cursor is required. here distinct will remove all records in subquery it self.

回答2:

I too get the same situation, after some time I realized that if I am using ORDER BYclause at sub cursor then it is not giving the DISTINCT values. Use your sub cursor without adding the ORDER BY clause. It will definitely give DISTINCT values