asked on

Oracle sort query

I have a problem with sorting a table field:

I have an working query:
SELECT distinct (f1 || ' - ' || substr(f2,'1','50')) as myfield
FROM mytab
where upper(f2) <> 'NOT DEFINED'
ORDER BY to_number(regexp_substr(f2,'^[0-9]+')) desc

The above query works but I now need to add 'Not Defined' at the bottom of the list. I mean just add 'Not Defined' at the bottom of the above output.
But This query does not work:
(select 'Not Defined' as myfield from dual)
UNION
(
SELECT distinct (f1 || ' - ' || substr(f2,'1','50')) as myfield
FROM mytab
where upper(f2) <> 'NOT DEFINED'
ORDER BY to_number(regexp_substr(f2,'^[0-9]+')) desc
)

Very strange

(
SELECT distinct (f1 || ' - ' || substr(f2,'1','50')) as myfield
FROM mytab
where upper(f2) <> 'NOT DEFINED'
ORDER BY to_number(regexp_substr(f2,'^[0-9]+')) desc
)

The above query gives error (I only added start and close first bracket in the working query: ORA-009-7: missing right parenthesis. Could you help troubleshoot..?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

toooki

ASKER

Thanks a lot for the help.
What you said worked perfectly. Never thought the way you mentioned.
Thanks.

slightwv (䄆 Netminder)

The dummy sort column comes in handy for just this reason. I think most of us have used it at one time or another to force an order when one doesn't naturally exist.