CODEWITHSUNDEEP
sqloracle-databaserownum
domiSchenk
domiSchenk

Reputation: 890

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

Upvotes: 2

Views: 3307

Answers (4)

JulesLt
JulesLt

Reputation: 1785

Reading between the lines in the comments above, and your SQL, I would suggest reading up on Oracle's analytic functions.

http://www.orafaq.com/node/55

These are excellent for calculating things that typically need nested sub-queries that perform group functions - i.e. 'give me the running balance of X for each row', 'give me the rank of X out of the total set of data' or simply 'give me the total count of data'.

What makes them hard to grasp at first is that the syntax is complex, as they can do similar queries for partitions of your data set.

In your case you could probably use the COUNT(*) OVER () operation to get the 'height' from your other table.

Upvotes: 1

Donnie
Donnie

Reputation: 46903

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.

Upvotes: 2

josephj1989
josephj1989

Reputation: 9709

Row num is per output row - not for every table

Why not try

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

Upvotes: 0

Dave Barker
Dave Barker

Reputation: 6437

The TBL alias isn't used in the sub query but in the main query. ie 

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

is not a valid query.

Upvotes: 1

Related Questions