tsql alias for select generated column

Question

In T-SQL, I need to assign an alias name to a calculated value for using it in a CASE clause.

SELECT RUT, (11 - ((SUBSTRING(RUT, 1, 1) * 3 + SUBSTRING(RUT, 2, 1) * 2 + SUBSTRING(RUT, 3, 1) * 7 + SUBSTRING(RUT, 4, 1) * 6 + SUBSTRING(RUT, 5, 1) * 5 + SUBSTRING(RUT, 6, 1) * 4 + SUBSTRING(RUT, 7, 1) * 3 + SUBSTRING(RUT, 8, 1) * 2) % 11)) AS VerifDigit, CASE WHEN VerifDigit = 10 THEN 'K' WHEN VerifDigit = 11 THEN '0' ELSE VerifDigit END FROM Citizen

Tim Biegeleisen · Accepted Answer · 2017-09-25 02:55:45Z

You can't use an alias in a select clause in which it was defined. One workaround here would be to subquery, e.g.

WITH cte AS ( SELECT RUT,(11 - ((SUBSTRING(RUT,1,1)*3 + SUBSTRING(RUT,2,1)*2 + SUBSTRING(RUT,3,1)*7 + SUBSTRING(RUT,4,1)*6 + SUBSTRING(RUT,5,1)*5 + SUBSTRING(RUT,6,1)*4 + SUBSTRING(RUT,7,1)*3 + SUBSTRING(RUT,8,1)*2)%11)) AS VerifDigit FROM Citizen ) SELECT VerifDigit, CASE WHEN VerifDigit = 10 THEN 'K' WHEN VerifDigit = 11 THEN '0' ELSE VerifDigit END FROM cte;

SQLBadPanda · Accepted Answer · 2017-09-25 06:55:22Z

You could also use CROSS APPLY with VALUES to calculate the result and then refer to that value in your CASE statement e.g. ...

SELECT RUT, xa.VerifDigit, CASE WHEN xa.VerifDigit = '10' THEN 'K' WHEN xa.VerifDigit = '11' THEN '0' -- this will never occur ELSE xa.VerifDigit END FROM Citizen CROSS APPLY (VALUES(cast((11 - (( substring(RUT, 1, 1) * 3 + SUBSTRING(RUT, 2, 1) * 2 + SUBSTRING(RUT, 3, 1) * 7 + SUBSTRING(RUT, 4, 1) * 6 + SUBSTRING(RUT, 5, 1) * 5 + SUBSTRING(RUT, 6, 1) * 4 + SUBSTRING(RUT, 7, 1) * 3 + SUBSTRING(RUT, 8, 1) * 2) % 11)) AS char(2)))) xa(VerifDigit)

A couple of things worth pointing out though...

You will need to cast the result (VerifDigit) to a character datatype if you are replacing it with 'K' otherwise the CASE will fail with a data conversion; this is shown in the example.
The result of Modulo (%) 11 will never be 11; the remainder of anything divided by 11 will be between 0 and 10, so the second CASE/WHEN will never be hit.

Collectives™ on Stack Overflow

tsql alias for select generated column

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related