Column or global variable ROWNUM not found

Question

I have made an attempt at the ROW_NUMBER(), and when exporting the results in a csv it was worked perfectly. The issue I’m having now is in the where clause to bring back records where ROWNUM = 1.

I am getting the error message:

Column or global variable ROWNUM not found.

I have tried using FETCH to bring back one row; this works however the following join does not get considered.

Do you know what the best method is to bring back only records with ROWNUM = 1?

LEFT JOIN                                                           
(
    SELECT 
        CONO15, ACTR15, FLG115,  MEMB15 AS PAYOWNER, 
        PYTP15 AS PAYTYPE, PYRF15 AS PAYREF, 
        ROW_NUMBER() OVER (
            PARTITION BY MEMB15, PYTP15, PYRF15 
            ORDER BY  PYTP15 ASC) AS ROWNUM 
    FROM TKTDTA.PY015L1 
    WHERE ROWNUM = 1 
)C ON C.CONO15 = A.CONO09 AND C.PAYREF = A.PAYREF

ypercubeᵀᴹ · Answer 1 · 2016-03-16T08:35:42.337

Lets see what we have here. Inside the parentheses we have a derived table (often called a subquery although this is a more general term), which is then aliased with the name C:

...                                                           
(
    SELECT 
        CONO15, ACTR15, FLG115,  MEMB15 AS PAYOWNER, 
        PYTP15 AS PAYTYPE, PYRF15 AS PAYREF, 
        ROW_NUMBER() OVER (
            PARTITION BY MEMB15, PYTP15, PYRF15 
            ORDER BY  PYTP15 ASC) AS ROWNUM 
    FROM TKTDTA.PY015L1 
    WHERE ROWNUM = 1 
) C  ...

Column ROWNUM is not a column of the base table (PY015L1) but defined in the SELECT clause. And there lies the problem. The logical processing of a query (and any subquery including a derived table) has a specific order which is (some parts not included):

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

So WHERE is before SELECT and cannot use any new columns defined there. That's the reason for the error.

You might ask? Isn't ROWNUM a real column? Haven't we just defined it?

Yes we did but it has a certain scope that can be used. C table is also a real table (it's just not a base table). We can use them after they has been defined, so ROWNUM can be used in the following clauses (ORDER BY which is after SELECT) and outside the parentheses, wherever the C table can be used. Both table C and column ROWNUM are real tables and columns but only after they have been defined. It's like Pinocchio ("See dad, I'm alive! And I'm a boy, I'm a real boy!"). He is a real boy but only at the end of the story, after he has been transformed).

In your case, what is probably wanted is to remove the WHERE ROWNUM = 1 and use the column in the following clauses, ON or WHERE. I'd guess the ON clause:

LEFT JOIN                                                           
(
    SELECT 
        CONO15, ACTR15, FLG115,  MEMB15 AS PAYOWNER, 
        PYTP15 AS PAYTYPE, PYRF15 AS PAYREF, 
        ROW_NUMBER() OVER (
            PARTITION BY MEMB15, PYTP15, PYRF15 
            ORDER BY  PYTP15 ASC) AS ROWNUM 
    FROM TKTDTA.PY015L1 
) C ON  C.CONO15 = A.CONO09 AND C.PAYREF = A.PAYREF  
    AND C.ROWNUM = 1

score 2 · Answer 2 · edited Apr 13 '17 at 12:42

2

You have to use a subselect to build the rownum column and then filter it in the outer SELECT.

SELECT * 
 FROM (SELECT ... row_number() Over....as rownum)
WHERE rownum = 1

For additional background information, see:

What is wrong with this statement? 'Unknown column'?

edited Apr 13 '17 at 12:42

Community

1

answered Mar 14 '16 at 22:04

MichaelTiefenbacher

498
3
6

Column or global variable ROWNUM not found

2 Answers2