Problem happens when doing a cross join within table

Question

I am modifying my query to include pagination and rownum counts in it, but somehow the rownum is causing an error

Ambigous column name

Code:

WITH List AS ( SELECT ROW_NUMBER() OVER (ORDER BY error_type asc) AS rowNum, MAX(id) AS id, COUNT(errorid) AS ecount, MAX(errorid) AS errorid, FORMAT(MAX(datein), 'MMMM d, yyyy h:mm:ss tt PST', 'en-US') AS max_datein, template, line, error_message, UPPER(error_type) AS error_type FROM mytable mt GROUP BY errorid, template, line, error_message, error_type ), ListRecordCount AS ( SELECT * FROM List, ( SELECT MAX(rowNum) AS TotalrecordCount FROM List ) AS TotalrecordCount ) SELECT * FROM ListRecordCount INNER JOIN List mi ON mt.id = mi.id WHERE rowNum BETWEEN 1 and 5000 ORDER BY mi.max_datein

Error I am getting is this:

[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The multi-part identifier "mt.id" could not be bound. (4104)
[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'rowNum'. (209)
[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'rowNum'. (209)

You must alias ListRecordCount as mt and specify mt.rowNum. And ` max(errorid)` is useless as you group by errorid. But why do you join those CTEs? — dnoeth
– dnoeth, Commented Dec 30, 2018 at 17:02
Now after your change, i get this: > [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'mt'. (102) — JDS
– JDS, Commented Dec 30, 2018 at 17:11

Raymond W · Accepted Answer · 2018-12-30 17:32:10Z

Because you first specify rowNum in List table, then you select * from List to create ListRecordCount table, which also includes column rowNum here. So if you join ListRecordCount with List, you will have rowNum in both tables, which causes ambiguous.

And also from your query above, I don't see a reason why you join these two tables.

I believe this query is what you are looking for.

WITH List AS( SELECT row_number() over(ORDER BY error_type asc) AS rowNum , max(id) as id, count(errorid) as ecount , max(errorid) as errorid, FORMAT(max(datein), 'MMMM d, yyyy h:mm:ss tt PST', 'en-US') as max_datein , template,line,error_message, upper(error_type) AS error_type from mytable mt group by errorid, template,line,error_message,error_type ), ListRecordCount AS( SELECT * , (SELECT MAX(rowNum) AS TotalrecordCount FROM List) AS TotalrecordCount FROM List ) SELECT * FROM ListRecordCount WHERE rowNum BETWEEN 1 and 5000 order by max_datein

Gordon Linoff · Accepted Answer · 2018-12-30 17:11:32Z

You have specified row_num in both tables. Perhaps you intend either:

 select . . . from ListRecordCount lrc join List mi on mt.id = mi.id where lrc.rowNum between 1 and 5000

or:

 select . . . from ListRecordCount lrc join List mi on mt.id = mi.id where mi.rowNum between 1 and 5000

Or, if you want the row number based on the result set:

select * from (select . . ., row_number() over (order by ?) as seqnum from ListRecordCount lrc join List mi on mt.id = mi.id ) mil where seqnum between 1 and 5000

your answer is very confusing and i tried the first two and seems not working, have not tried 3rd one because i don't understood that part, can you post a complete example

GMB · Accepted Answer · 2018-12-30 19:06:11Z

The error refers to your main SELECT clause :

SELECT * FROM ListRecordCount INNER JOIN List mi ON mt.id = mi.id WHERE rowNum BETWEEN 1 and 5000 ^^

The problem comes from the fact that both common table expressions ListRecordCount and List have a column called rowNum (more details later).

To solve this, all you need to do is prefix column rowNum in the main query. It has to be either mt.rowNum or mi.rowNum, for example :

... SELECT * FROM ListRecordCount mt INNER JOIN List mi ON mt.id = mi.id WHERE mt.rowNum BETWEEN 1 and 5000 ORDER BY mi.max_datein

Nb : looks like you were missing the declaration of the mt alias on ListRecordCount, I added it.

Details

List defines a rowNum column :

WITH List AS ( SELECT ROW_NUMBER() OVER (ORDER BY error_type asc) AS rowNum, ...

ListItems does a SELECT * on List hence it also has column rowNum :

... ListRecordCount AS ( SELECT * FROM List, ...

Collectives™ on Stack Overflow

Problem happens when doing a cross join within table

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Related