ansaurus

Question

rownum in Subquery

Answer 1

+1 A:

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.

Dave Barker 2010-07-02 12:27:31

well i think thats not the problem :D coze i have Tbl.Persidand it works fine

Auro 2010-07-02 14:35:36

Answer 2

A:

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 ...

josephj1989 2010-07-02 12:31:30

doesnt || neans just ad behind? like 1 || 2 = 12 ??

Auro 2010-07-02 14:34:41

Yes - it's a shortcut for string concatentation - so it automatically converts the type on either side to a string.Also you would still have the issue that ROWNUM would have a arbitrary value.

JulesLt 2010-07-03 08:05:23

Answer 3

+3 A:

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.

Donnie 2010-07-02 12:42:05

nah its not a column in my Table.i have a table where a person can have more then one row something like an history. I need to Merge 2 tables (same attributs etc) and when i uses this script(its an insert/select) i dont know the actual row number. soi wanted to read the actual highst of the user and start adding at this number (e.g. 5 is the hist now then i want to start adding with 6 and so on...)

Auro 2010-07-02 14:33:43

'(it happens after all sorting, etc)' this may be the rease why it dosent work. Thx!

Auro 2010-07-02 14:39:19

It gets assigned before sorting, which can cause some people problems.SELECT numval from big_table_of_numbers WHERE ROWNUM < 3 ORDER BY numval DESC will get 2 numbers from the table, and then order them in descending order, rather than the last 2 numbers.Which 2 numbers it gets is up to the database.You need to do SELECT * FROM (SELECT numval FROM blah ORDER BY numval DESC) WHERE rownum < 2

JulesLt 2010-07-02 17:48:50

Answer 4

A:

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.

JulesLt 2010-07-03 08:22:23

ansaurus

tags:

views:

answers:

rownum in Subquery

related questions