Can a column name used as part of a CTE be used in the SELECT query that creates the CTE?

Question

Can a column name used as part of a CTE be used in the SELECT query that creates the CTE?

Gil Robinson 0

When converting a SQL script from SQL Anywhere to SQL Server, my code generated an error for the following CTE code sample; the error says that the column name used in the ROW_NUMBER function is invalid. Any suggestions on how to fix this would be appreciated:

With TestCTE(CUST_NUM, FIRST_NAME, LAST_NAME, ADDRESS, ROWNUM) as(

SELECT

 testDB.FIELD_1 as CUST_NUM

 testDB.FIELD_2 as FIRST_NAME,

 testDB.FIELD_3 as LAST_NAME,

 testDB.FIELD_4 as TRANACTION_NUMBER,

 CASE
	WHEN TRANSACTION_TYPE = 'D' THEN 'DEPOSIT'
	WHEN TRANSACTION_TYPE = 'W' TEN 'WITHDRAWAL'
	WHEN TRANSACTION_TYPE = 'L' THEN 'LOAN'
	ELSE 'PAYMENT'
 END TRANS_TYPE,

 ROW_NUMBER() OVER(PARTITION by testDB.FIELD_1 ***order by TRANS_TYPE*****)** as ROWNUM
FROM

testDB

2 answers

Your answer

Answer 1

Try

TestCTE(CUST_NUM, FIRST_NAME, LAST_NAME, ADDRESS, ROWNUM) as(

SELECT

SQL

Copy
 testDB.FIELD_1 as CUST_NUM

 testDB.FIELD_2 as FIRST_NAME,

 testDB.FIELD_3 as LAST_NAME,

 testDB.FIELD_4 as TRANACTION_NUMBER,

 
 ROW_NUMBER() OVER(PARTITION by testDB.FIELD_1 order by T.TRANS_TYPE) as ROWNUM
FROM
testDB CROSS APPLY (select 
CASE 	WHEN TRANSACTION_TYPE = 'D' THEN 'DEPOSIT' 	WHEN TRANSACTION_TYPE = 'W' TEN 'WITHDRAWAL' 	WHEN TRANSACTION_TYPE = 'L' THEN 'LOAN' 	ELSE 'PAYMENT'  END TRANS_TYPE) T

Answer 2

The idea in SQL is "all at once". This means that the SELECT list is defined all at once. Therefore, you cannot refer to a column alias defined earlier in the SELECT list in a later column in the SELECT list.

That is the ANSI standard, but there are more than one product that violates this rule and permits you do this exactly the above. SQL Anywhere is by no means the only sinner. However, SQL Server plays by the book.

So you need something like:

With TestCTE(CUST_NUM, FIRST_NAME, LAST_NAME, ADDRESS, ROWNUM) as(
  SELECT  testDB.FIELD_1 as CUST_NUM
          testDB.FIELD_2 as FIRST_NAME,
          testDB.FIELD_3 as LAST_NAME,
          testDB.FIELD_4 as TRANACTION_NUMBER,
          CASE
	          WHEN TRANSACTION_TYPE = 'D' THEN 'DEPOSIT'
	          WHEN TRANSACTION_TYPE = 'W' TEN 'WITHDRAWAL'
	          WHEN TRANSACTION_TYPE = 'L' THEN 'LOAN'
	          ELSE 'PAYMENT'
         END TRANS_TYPE,
   FROM  tbl
), nextCTE AS (
  SELECT CUST_NUM, FIRST_NAME, LAST_NAME, TRANSACTION_NUMBER, 
         ROW_NUMBER() OVER(PARTITION by CUST_NUM TRANS_TYPE) as ROWNUM
   FROM  TestCTE
)

Share via

Can a column name used as part of a CTE be used in the SELECT query that creates the CTE?

2 answers

Your answer