CODEWITHSUNDEEP
sqloracle-databasesql-order-byunion
PedroEstevesAntunes
PedroEstevesAntunes

Reputation: 91

'Order By' with 'Union' and 'GroupBy' (Oracle)

Good Morning. I have a query that has Group By and Union tables. The problem is when I try to order the final result. I'm always getting an error:

SELECT {Account}.[Id], {Account}.[AccountName], {Account}.[Project], {Account}.[Initiative], {Account}.[AccountName], {Application}.[Id], {Application}.[ApplicationName]
FROM {Account}
INNER JOIN {ApprovalWorkUnitDetail} ON {ApprovalWorkUnitDetail}.[AccountID] = {Account}.[Id]
INNER JOIN {Issue} ON {Issue}.[Id] = {ApprovalWorkUnitDetail}.[IssueID]
INNER JOIN {ServiceRequest} ON {ServiceRequest}.[Id] = {Issue}.[ServiceRequestId]
INNER JOIN {ServiceRequestProduct} ON {ServiceRequestProduct}.[ServiceRequestId] = {ServiceRequest}.[Id]
INNER JOIN {Product} ON {Product}.[Id] = {ServiceRequestProduct}.[ProductId]
LEFT JOIN {Application} ON {Application}.[Id] = {Account}.[ApplicationID]
WHERE ({ApprovalWorkUnitDetail}.[ContractID] = @ContractID)
    AND ({ServiceRequest}.[Id] = @ServiceRequestID OR @ServiceRequestID = @NullIdentifier)
    AND ({Product}.[Id] = @ProductID OR @ProductID = @NullIdentifier)
    AND ({ApprovalWorkUnitDetail}.[MonthIdentifier] = @MonthIdentifier)
    AND ({ApprovalWorkUnitDetail}.[YearIdentifier] = @YearIdentifier)
    AND ({Issue}.[PaymentStatusID] = @PaymentStatusApprovedID)

UNION

SELECT {Account}.[Id], {Account}.[AccountName], {Account}.[Project], {Account}.[Initiative], {Account}.[AccountName], {Application}.[Id], {Application}.[ApplicationName]
FROM {Account}
INNER JOIN {ApprovalRefinementDetail} ON {ApprovalRefinementDetail}.[AccountID] = {Account}.[Id]
INNER JOIN {Worklog} ON {Worklog}.[Id] = {ApprovalRefinementDetail}.[WorklogID]
INNER JOIN {Issue} ON {Worklog}.[IssueId] = {Issue}.[Id]
INNER JOIN {ServiceRequest} ON {ServiceRequest}.[Id] = {Issue}.[ServiceRequestId]
INNER JOIN {ServiceRequestProduct} ON {ServiceRequestProduct}.[ServiceRequestId] = {ServiceRequest}.[Id]
INNER JOIN {Product} ON {Product}.[Id] = {ServiceRequestProduct}.[ProductId]
LEFT JOIN {Application} ON {Application}.[Id] = {Account}.[ApplicationID]
WHERE ({ApprovalRefinementDetail}.[ContractID] = @ContractID)
    AND ({Product}.[Id] = @ProductID OR @ProductID = @NullIdentifier)
    AND ({ServiceRequest}.[Id] = @ServiceRequestID OR @ServiceRequestID = @NullIdentifier)
    AND ({ApprovalRefinementDetail}.[MonthIdentifier] = @MonthIdentifier)
    AND ({ApprovalRefinementDetail}.[YearIdentifier] = @YearIdentifier)
    AND ({Worklog}.[PaymentStatusID] = @PaymentStatusApprovedID)
 
GROUP BY {Account}.[Id], {Account}.[AccountName], {Account}.[Project], {Account}.[Initiative], {Account}.[AccountName], {Application}.[Id], {Application}.[ApplicationName]
ORDER BY {Account}.[AccountName]

I don't know why I am always getting error ORA-00904 and it indicates that AccountName is an invalid identifier.

I am using ORACLE database. Many thanks

Upvotes: 1

Views: 359

Answers (1)

GMB
GMB

Reputation: 222422

I would recommend moving your union query to a subquery, and sorting in the outer scope:

select *
from (
    -- your big query
) t
order by accountname

Upvotes: 1

Related Questions