ORA-01785 ORDER BY error 1

[Andrzejek]

I have this SQL with the UNION in it:

SELECT DISTINCT [b]PSDETL_PROJECTNAME[/b]
 FROM S4111000.PSDETL_INFORMATION
 WHERE (PSDETL_DELBYTE IN ('A', 'N', 'F', 'Z')) AND  (SUBSTR(PSDETL_PROJECTNAME, 1, 13) IN  (SELECT DISTINCT SUBSTR(S4111000.PS404_PERMITS.PSPERMIT_PROJECTNAME, 1, 13)  FROM S4111000.PS404_MITIGATE_PERMITS, S4111000.PS404_PERMITS  WHERE S4111000.PS404_MITIGATE_PERMITS.PSSITE_404_NUMBER = S4111000.PS404_PERMITS.PSPERMIT_404_NUMBER
 AND ((S4111000.PS404_PERMITS.PSPERMIT_MONITOR_REQUIRED IS NULL OR  S4111000.PS404_PERMITS.PSPERMIT_MONITOR_REQUIRED = 'Y') AND  (S4111000.PS404_MITIGATE_PERMITS.PSSITE_MONITOR_RELEASED IS NULL)))) 
[red]UNION[/red] 
SELECT DISTINCT [b]PSDETL_PROJECTNAME[/b]  FROM S4111000.PSDETL_INFORMATION  WHERE (PSDETL_DELBYTE IN ('A', 'N', 'F')) AND  (SUBSTR(PSDETL_PROJECTNAME, 1, 13) IN  (SELECT DISTINCT SUBSTR(S4111000.PS404_PERMITS.PSPERMIT_PROJECTNAME, 1, 13)  FROM S4111000.PS404_MITIGATE_PERMITS, S4111000.PS404_PERMITS  WHERE S4111000.PS404_MITIGATE_PERMITS.PSSITE_404_NUMBER = S4111000.PS404_PERMITS.PSPERMIT_404_NUMBER
 AND (S4111000.PS404_PERMITS.PSPERMIT_MONITOR_REQUIRED = 'N')))[blue]
ORDER BY [COLOR=blue yellow]SUBSTR[/color](PSDETL_PROJECTNAME, 4, 6), PSDETL_PROJECTNAME[/blue]

And I get this error:[tt]
ORA-01785 ORDER BY item must be the number of a SELECT-list expression[/tt]

SQL works just fine if I do not use ORDER BY, or just use [tt] ORDER BY 1[/tt] or [tt] ORDER BY PSDETL_PROJECTNAME[/tt], but if I include SUBSTR in my ORDER BY I get SUBSTR highlighted and an error ORA-01785


I have the details about the error:

Cause: Either an ORDER BY item for a set expression is not a column number, or the ORDER BY item is not the number of a SELECT list column.

Action: The ORDER BY item must be a number between 1 and the number of columns in the SELECT list. Check the column number and retry the statement.

But that does not make sense to me - my ORDER BY has column name used in Select.

Whe can't I use SUBSTR in my ORDER BY?

Have fun.

---- Andy

 

[Dagon]

The problem is that you're using a UNION. The ORDER BY clause has to refer to a column in the query as a whole rather than just the second part of the UNION. In that situation, Oracle expects either a column position or the name of one of the selected columns.

You can get round it fairly easily using something like:

SELECT PSDETL_PROJECTNAME
FROM
(SELECT PSDETL_PROJECTNAME
 FROM S4111000.PSDETL_INFORMATION
 WHERE (PSDETL_DELBYTE IN ('A', 'N', 'F', 'Z')) AND  (SUBSTR(PSDETL_PROJECTNAME, 1, 13) IN  (SELECT DISTINCT SUBSTR(S4111000.PS404_PERMITS.PSPERMIT_PROJECTNAME, 1, 13)  FROM S4111000.PS404_MITIGATE_PERMITS, S4111000.PS404_PERMITS  WHERE S4111000.PS404_MITIGATE_PERMITS.PSSITE_404_NUMBER = S4111000.PS404_PERMITS.PSPERMIT_404_NUMBER
 AND ((S4111000.PS404_PERMITS.PSPERMIT_MONITOR_REQUIRED IS NULL OR  S4111000.PS404_PERMITS.PSPERMIT_MONITOR_REQUIRED = 'Y') AND  (S4111000.PS404_MITIGATE_PERMITS.PSSITE_MONITOR_RELEASED IS NULL)))) 
UNION 
SELECT PSDETL_PROJECTNAME  FROM S4111000.PSDETL_INFORMATION  WHERE (PSDETL_DELBYTE IN ('A', 'N', 'F')) AND  (SUBSTR(PSDETL_PROJECTNAME, 1, 13) IN  (SELECT DISTINCT SUBSTR(S4111000.PS404_PERMITS.PSPERMIT_PROJECTNAME, 1, 13)  FROM S4111000.PS404_MITIGATE_PERMITS, S4111000.PS404_PERMITS  WHERE S4111000.PS404_MITIGATE_PERMITS.PSSITE_404_NUMBER = S4111000.PS404_PERMITS.PSPERMIT_404_NUMBER
 AND (S4111000.PS404_PERMITS.PSPERMIT_MONITOR_REQUIRED = 'N'))))
ORDER BY SUBSTR(PSDETL_PROJECTNAME, 4, 6)

BTW, you don't need to select DISTINCT PSDETL_PROJECTNAME if you're doing a UNION because UNION de-duplicates the resultset anyway.

 

[Andrzejek]

Thank you very much.

I did find very close sollution to yours:

SELECT * FROM ([green] --- my original Union SQL here --[/green] )
ORDER BY SUBSTR(PSDETL_PROJECTNAME, 4, 6), PSDETL_PROJECTNAME

And you are right about DISTINCT, it is left from where I originally build the separate SELECTs to form a UNION

Have fun.

---- Andy