Search code examples
sqloraclerownum

rownum in Subquery

I was wondring why this doesn't work:

SELECT 
  (
    SELECT COALESCE(MAX(a.LaufNr),0) + TBL.Rownum
    FROM schemaB.PersonRelatedTbl A
    WHERE A.Persid = Tbl.Persid
  )
  , Tbl.Some_Other_Attributs
FROM schemaA.PersonRelatedTbl TBL 
WHERE ...

This + TBL.Rownum gives an error
why?

greets Auro

Solution

  • rownum is a pseudocolumn in a result set. It is not associated with any tables, and is the among the last things that get assigned (it happens after all sorting, etc).

    It is possible that the row_number() function would be more useful for what you're doing, but, if you want the numbers to stay the same across all query invocations for each given row then you're going to have to store the numbers in the database (or use rowid, but, that's more like a serial number and is pretty ugly to show to end users). It's not really clear from your example what you expect rownum to be doing.