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"
Oracle SELECT DISTINCT. Summary: in this tutorial, you will learn how to use the Oracle SELECT DISTINCT statement to query distinct data from tables. The DISTINCT clause is used in a SELECT statement to filter duplicate rows in the result set. It ensures that rows returned are unique for the column or columns specified in the SELECT clause.
The problem is that the columns used in the ORDER BY aren't specified in the DISTINCT. To do this, you need to use an aggregate function to sort on, and use a GROUP BY to make the DISTINCT work. Show activity on this post.
If we add the DISTINCT operation, it would be added between SELECT and ORDER BY: But now, with the extended sort key column CreationDate, the semantics of the DISTINCT operation has been changed, so the result will no longer be the same. This is not what we want, so both the SQL standard, and all reasonable databases forbid this usage.
If you use the SELECT DISTINCT statement to query data from a column that has many NULL values, the result set will include only one NULL value. See the locations table in the sample database.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With