I was looking at a post on AskTom this morning, which was looking to "Select entire rows with most recent date only. No duplicates" and wondered how Tom would have approached it. The question had been marked as for version 8.1.7 of Oracle.
However, looking at the query that the poster had included, there was a new word that seemed strange to me – readuncommitted. I’d never seen it in an Oracle query before, so I looked at more of the code:
Select Jobstop.PODdatetime Date_Time
, job.routenumber Route
, Stop.Name Location
, Case When Jobstop.PODSign IS NOT NULL Then 'Electronic' End AS PODsign
, JobStop.ArriveDateTimeSource ArriveScan
From Ordermain with (readuncommitted, INDEX=OrderDateOrderID)
Left Outer Join Customer with (readuncommitted) on Customer.CustomerID=Ordermain.CustomerID
Left Outer Join Job with (readuncommitted) on Ordermain.OrderID=Job.OrderID
Left Outer Join Jobstop with (readuncommitted) on Job.JobID=Jobstop.JobID
Left Outer Join Stop with (readuncommitted) on Jobstop.StopID=Stop.StopID
Left Outer Join Jobstoppiece with (readuncommitted) on Jobstop.JobstopID=Jobstoppiece.JobstopID
Left Outer Join Piece with (readuncommitted) on Jobstoppiece.PieceID=Piece.PieceID
Where Ordermain.Orderstatus IN ('N', 'A', 'I','P')
And Left(Ordermain.Service,3) NOT IN ('LEA', 'FUE', 'Wai', 'Mon')
And Ordermain.Orderdate between '9/21/07' AND '9/27/07'
Order By Piece.reference, JobStop.PODdatetime
There’s no way that this could be a query for Oracle 8.1.7 – the ANSI join syntax didn’t come into the database until version 9i . So, maybe it’s a 9i query then, and the poster got the version wrong (unlikely, but possible). No Oracle query that I’ve seen uses the "readuncommitted" term – this would be a really bad thing to do, as far as I can see.
Hmm – different syntax, really bad ideas for data selection. Then it hit me – it’s a SQL Server query!!
Lesson one in getting help – make sure you ask in the right place!