CODEWITHSUNDEEP
sqloracle-databaseselect
happy
happy

Reputation: 2628

Resultant multiple rows in one row

I want resulting rows to be in one row with comma separated values so I applied 

rtrim(xmlagg(xmlelement(e, column_name|| ',')).extract('//text()').extract('//text()'),',')

on the resulting rows of the following query

SELECT 
    TO_DATE('01-04-2012','dd-MM-yyyy') - 1 + rownum AS d
FROM all_objects
WHERE TO_DATE('01-04-2012','dd-MM-yyyy') - 1 + rownum 
    <= TO_DATE('30-04-2012','dd-MM-yyyy')
    MINUS
SELECT TS_DATE  
FROM TS_DTL where emp_id=3 and TS_DATE BETWEEN TO_DATE('01-04-2012','dd-MM-yyyy') 
AND TO_DATE('30-04-2012','dd-MM-yyyy')

when I do that as follows::

SELECT 
    rtrim(xmlagg(xmlelement(e, TO_DATE('01-04-2012','dd-MM-yyyy') - 1 + rownum || ','))
        .extract('//text()')
        .extract('//text()') ,',') AS d
FROM all_objects
WHERE TO_DATE('01-04-2012','dd-MM-yyyy') - 1 + rownum 
    <= TO_DATE('30-04-2012','dd-MM-yyyy')

MINUS

SELECT 
    rtrim(xmlagg(xmlelement(e, TS_DATE || ','))
        .extract('//text()')
        .extract('//text()') ,',') 
FROM TS_DTL 
WHERE emp_id=3 
AND TS_DATE 
    BETWEEN TO_DATE('01-04-2012','dd-MM-yyyy') 
    AND TO_DATE('30-04-2012','dd-MM-yyyy')

It does not minus the 2nd query result.

Upvotes: 0

Views: 261

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81930

I'd guess it is due to some data formatting issue. Replace the MINUS with a UNION ALL and compare the resulting rows.

Alternatively use the original select as subselect and in the outer select apply your xmlagg magic:

SELECT rtrim(xmlagg ....))
FROM (
    SELECT
    FROM
    WHERE

    MINUS


    SELECT
    FROM
    WHERE

)

Upvotes: 1

Related Questions