Sorting with Union and to_char(date field)

[stvalluri]

I am trying to sort 2 columns like :

(SELECT w_id AS "work id" , TO_CHAR(w_date, 'ddMonYY') AS "Date I" FROM W WHERE w_id IN (181185, 181166)
UNION
SELECT w_id as "work id", TO_CHAR(w_date, 'ddMonYY') AS "Date I" FROM W WHERE w_id IN (182394,179945,207781 )
)ORDER BY "work id", "Date I"

The Date I field is obviously sorted as a character.
I need to use the actual date sorting but also represent the date field as a character in my results.

Any clue??

 

[SantaMufasa]

stvalluri, you can tell SQL*Plus not to print the DATE column by which you are sorting, as follows. (Changes in bold.):

col x noprint
SELECT w_id AS "work id" , TO_CHAR(w_date, 'ddMonYY') "Date I"[b], w_date x[/b]
  FROM W WHERE w_id IN (181185, 181166)
UNION 
SELECT w_id as "work id", TO_CHAR(w_date, 'ddMonYY') "Date I"[b], w_date[/b]
  FROM W WHERE w_id IN (182394,179945,207781 )
 ORDER BY  "work id", [b]x[/b];

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[IanWaterman]

How about doing this

Select w_id as "work id", w_date
from
(SELECT w_id , TO_CHAR(w_date, 'ddMonYY') AS "Date I" , w_date
FROM W
WHERE w_id IN (181185, 181166)
UNION
SELECT w_id, TO_CHAR(w_date, 'ddMonYY') AS "Date I" ,w_date
FROM W
WHERE w_id IN (182394,179945,207781 )
)ORDER BY "work id", "Date I"

 

[stvalluri]

Thanks for both the replies.

Mufasa, your solutions works fine with sqlplus but the complex report I am working is a third party tool which embeds sql and it fails there with the error "Invalid SQL". Is this a feature related to oracle version?

IanWaterman, - Sorry I just posted a sample query, but my query is more complex with another sorting field(another long query) so, could not get your solution work too.

 

[SantaMufasa]

Stvalluri,

Sorry...I did not know that your query is embedded in a non-SQL*Plus venue. Had I known that, I would have suggested a solution similar to Ian's excellent technique. I say "similar" since you probably need a different "ORDER BY" from Ian's. You should be able to embed the following into your third-party software without problems. (If you do have problems, please post the specific error/problem you are having so that we can help you arrive at a successful implementation.):

Select w_id as "work id", [b]"Date I"[/b]
from
(SELECT w_id , TO_CHAR(w_date, 'ddMonYY') AS "Date I" , w_date  
FROM W 
WHERE w_id IN (181185, 181166)
UNION 
SELECT w_id, TO_CHAR(w_date, 'ddMonYY') AS "Date I" ,w_date
FROM W 
WHERE w_id IN (182394,179945,207781 )
)ORDER BY  "work id", [b]w_date[/b]

The reason why this technique should work is because the inner "SELECT...UNION...SELECT" forms an "in-line" view, which is perfectly legal.

Let us know your findings

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[stvalluri]

Thank you very much for your immediate response.
This solution works with tools like Toad.
However,
I have tried to execute this within the tool - it fails with the error - ORA-00904: "work id": invalid identifier -

I need to embed the sql only within this and I have also contacted the vendor for support of the sql formatting commands.

thanks once again

 

[SantaMufasa]

Stvalluri,

Try the following version from which I have "sanitised" all quoted aliases:

Select w_id, "Date I"
from
(SELECT w_id , TO_CHAR(w_date, 'ddMonYY')Date_I, w_date  
FROM W 
WHERE w_id IN (181185, 181166)
UNION 
SELECT w_id, TO_CHAR(w_date, 'ddMonYY'),w_date
FROM W 
WHERE w_id IN (182394,179945,207781 )
)ORDER BY  w_id, w_date

Let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Beilstwh]

Why not the following

(SELECT w_id AS "work id" , TO_CHAR(w_date, 'ddMonYY') AS "Date I" FROM W WHERE w_id IN (181185, 181166)
UNION
SELECT w_id as "work id", TO_CHAR(w_date, 'ddMonYY') AS "Date I" FROM W WHERE w_id IN (182394,179945,207781 )
)ORDER BY "work id", TO_DATE("Date I",'DDMONRR')

Bill
Oracle DBA/Developer
New York State, USA