Ordering query results

Is there a cola in tabA?

Using this simple test case that sort of mirrors what you show, it works for me:

drop table tab1 purge;
create table tab1(colA char(1), colB char(1), colC char(1));
insert into tab1 values('a','b','c');
insert into tab1 values('1','2','3');
commit;

select exch.colA || '|' ||  hol.colB || '|' ||  exch.colC
from
( select colA,colB,colC from tab1 ) exch,
( select colA,colB,colC from tab1 ) hol
where exch.colA=hol.colA
order by exch.colA, hol.colB, exch.colC
/

Select allOpen in new window

OH, I added the DISTINCT and it produces the ORA-01791.

Can you move the distinct into an inner select?

If i remove the distinct from all selects, i get ordered rows.
But i get duplicates. How can i remove them.

Also, why after removing distinct i am able to order data.

Move the distinct to an inner query then do the concatenation outside that.

I posted an example as did sdstuber.

I get the duplicates even when I move distinct to inner query

I'm saying take the original query you have that uses two inline views, remove the concatenation leave the distinct.  That should run.

Then wrap that in a query with the concatenation.

Like what I showed in http:#a39310836 and sdstuber showed in http:#a39310862

this is your original query:

SELECT DISTINCT exch.colA  ||'|'|| hol.colB  ||'|'|| exch.colC
    FROM (  SELECT bc.colA, 
                   bc.colB, 
                   ex.colC, 
              FROM tabA bc, tabB ex 
             WHERE AND bc.colA IS NOT NULL 
                   AND ex.colB = bc.colB
                   AND ex.colB IS NOT NULL 
          GROUP BY bc.colA, 
                   bc.colB, 
                   ex.colC, 
          ORDER BY bc.colA) exch, 
         (SELECT * 
            FROM tabA
           WHERE AND dt IS NOT NULL 
                 AND dt >= TRUNC(TO_DATE ('09-JUL-10', 'dd-mon-yy'),'MONTH') 
                 AND day_type = 'Myday') hol
   WHERE hol.colA = exch.colA
   ORDER BY exch.colA; <-- error on ordering

Select allOpen in new window

Line 1: Add an alias to the concatenation
Line 3: remove colB, causing duplication I believe
Line 4: remove trailing comma
Line 6: remove incorrect AND after WHERE
LINE 8: testing for NOT NULL redundant due to Line 7
Line 10: remove colB, causing duplication I believe
Line 11: remove trailing comma
Line 12: contains am order by - which it should not
Line 13: Specify the fields you require (not *), add DISTINCT here too (?)
Line 15: remove incorrect AND after WHERE
Line 19: use the concatenation alias for the order by

SELECT DISTINCT exch.colA  ||'|'|| hol.colB  ||'|'|| exch.colC  AS MY_CONCAT
    FROM (  SELECT bc.colA, 
                  -- bc.colB, -- will produce dups I believe
                   ex.colC
              FROM tabA bc, tabB ex 
             WHERE bc.colA IS NOT NULL
                   AND ex.colB = bc.colB
                 --  AND ex.colB IS NOT NULL  -- not null test is redundant
          GROUP BY bc.colA, 
                --   bc.colB, -- will produce dups I believe
                   ex.colC
         ) exch, 
         (SELECT DISTINCT colA, colB 
            FROM tabA
           WHERE dt IS NOT NULL 
                 AND dt >= TRUNC(TO_DATE ('09-JUL-10', 'dd-mon-yy'),'MONTH') 
                 AND day_type = 'Myday') hol
   WHERE hol.colA = exch.colA
   ORDER BY MY_CONCAT

Select allOpen in new window

tdied-up version:

SELECT DISTINCT exch.colA  ||'|'|| hol.colB  ||'|'|| exch.colC  AS MY_CONCAT
    FROM (  SELECT bc.colA, 
                   ex.colC
              FROM tabA bc, tabB ex 
             WHERE bc.colA IS NOT NULL
                   AND ex.colB = bc.colB
          GROUP BY bc.colA, 
                   ex.colC
         ) exch, 
         (SELECT DISTINCT colA, colB 
            FROM tabA
           WHERE dt IS NOT NULL 
                 AND dt >= TRUNC(TO_DATE ('09-JUL-10', 'dd-mon-yy'),'MONTH') 
                 AND day_type = 'Myday') hol
   WHERE hol.colA = exch.colA
   ORDER BY MY_CONCAT

Select allOpen in new window

ORA-01791:      not a SELECTed expression
Cause:      There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.
Action:      Remove the inappropriate ORDER BY item from the SELECT list and retry the statement.

Without data to trail this on I'm not completely sure the tidied-up version is 100%, but hopefully closer.

{+ edit, It may be possible to remove the DISTINCT in Line 1, by including the distinct in the second subquery}

Wondering if line 8 of the original was intended as seen below to avoid concatenantions without the colB - but from the joined subquery 'hol' instead, particularly as that is being filtered by date. Also think it would be useful to trial without distinct in line 1. Hope this helps.

SELECT exch.colA  ||'|'|| hol.colB  ||'|'|| exch.colC  AS MY_CONCAT
    FROM (  SELECT bc.colA, 
                   ex.colC
              FROM tabA bc, tabB ex 
             WHERE bc.colA IS NOT NULL
                   AND ex.colB = bc.colB
          GROUP BY bc.colA, 
                   ex.colC
         ) exch, 
         (SELECT DISTINCT colA, colB 
            FROM tabA
           WHERE dt IS NOT NULL 
                 AND dt >= TRUNC(TO_DATE ('09-JUL-10', 'dd-mon-yy'),'MONTH') 
                 AND day_type = 'Myday') hol
   WHERE hol.colA = exch.colA
        AND hol.colB IS NOT NULL
   ORDER BY MY_CONCAT

Select allOpen in new window

another observation, I would recommend using century in all dates, and indeed would much prefer to see you use a YYYY-MM-DD pattern instead. Also not sure why you don't just specify the first day of the month.
         to_date('2010-07-01','YYYY-MM-DD')

Excellent attempt