Oracle PL/SQL Query Order By issue with Distinct

Question

Does anyone know what is wrong with this query?

 SELECT DISTINCT c.CN as ClaimNumber, 
         a.ItemDate as BillReceivedDate, c.DTN as
 DocTrackNumber
         FROM ItemData a,
         ItemDataPage b,
         KeyGroupData c
         WHERE a.ItemTypeNum in (112, 113, 116, 172, 189)
         AND a.ItemNum = b.ItemNum
         AND b.ItemNum = c.ItemNum
         ORDER BY a.DateStored DESC;

I have done T-Sql most of my career and this looks correct to me, however this query is for an Oracle database and Toad just places the cursor on the a.DateStored in the Order By section. I'm sure this is elementary for anyone doing PL/SQL.

Thanks!

[EDIT] For future reference, the error given by SQL*Plus was: "ORA-01791: not a SELECTed expression"

Answer 1

Nevermind, executing in SQL Plus gave me a more informative answer. The DateStored needs to be in the select statement so this works:

    SELECT DISTINCT c.CN as ClaimNumber,          
a.ItemDate as BillReceivedDate, 
c.DTN as DocTrackNumber, 
a.DateStored         
FROM ItemData a,         
ItemDataPage b,         
KeyGroupData c         
WHERE a.ItemTypeNum in (112, 113, 116, 172, 189)         
AND a.ItemNum = b.ItemNum         
AND b.ItemNum = c.ItemNum         
ORDER BY a.DateStored DESC;

Answer 2

You will need to modify the query as such:

SELECT DISTINCT c.CN as ClaimNumber, 
         a.ItemDate as BillReceivedDate, c.DTN as
 DocTrackNumber, a.DateStored
         FROM ItemData a,
         ItemDataPage b,
         KeyGroupData c
         WHERE a.ItemTypeNum in (112, 113, 116, 172, 189)
         AND a.ItemNum = b.ItemNum
         AND b.ItemNum = c.ItemNum
         ORDER BY a.DateStored DESC;

When doing a DISTINCT your order by needs to be one of the selected columns.

Answer 3

I believe that the elements of the order by clause must also be in the select clause.