Can we use same alias name multiple times in SELECT statement

Database
Highlighted
Fan

Can we use same alias name multiple times in SELECT statement

When I use the same alias name multiple times one in CASE and one to alias the column in SELECT statement. It's failing with below Error in TD 15.10 and the same query running fine in TD 16.20

Failed [3863 : 42000] Duplicate definition of 'a' in NAMED phrase.

Eg:

 SELECT

            col1 as a

           ,CASE WHEN col2=100 THEN 1

                  ELSE col2

               END AS a

FROM table1

 

When I use same alias name multiple times to alias columns without case .It's working fine in both TD 15.10 and TD 16.20

Eg: SELECT

    col1 AS a

    ,col1 AS a

FROM table1

 

Can we use the same alias name or not? Which one is correct TD 15.10 or TD 16.20?

 

4 REPLIES 4
Teradata Employee

Re: Can we use same alias name multiple times in SELECT statement

Hi.

 

Your sentence is wrong:

 

CASE WHEN col2=100 AS 1 should be CASE WHEN col2=100 THEN 1

 

Also, the error given by this wrong query is 3706, not 3863:

 

BTEQ -- Enter your SQL request or BTEQ command:
SELECT C_TXT as a,
CASE WHEN ID_N =100 AS 1
ELSE ID_N
END AS a
FROM CARLOS.PRUEBA01;


CASE WHEN ID_N =100 AS 1
$
*** Failure 3706 Syntax error: expected something between an integer and the 'AS' keyword.
Statement# 1, Info =50
*** Total elapsed time was 1 second.


BTEQ -- Enter your SQL request or BTEQ command:
SELECT C_TXT as a,
CASE WHEN ID_N =100 THEN 1
ELSE ID_N
END AS a
FROM CARLOS.PRUEBA01;


*** Query completed. 2 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

a                                   a
------------------------- -----------
UNO                                 1
(null)                              0

 

Cheers.

 

Carlos.

Fan

Re: Can we use same alias name multiple times in SELECT statement

Sorry for the TYPO.

I am not sure about your database version. I am getting the Error in TD 15.10.07.14


*** Logon successfully completed.
*** Teradata Database Release is 15.10.07.14
*** Teradata Database Version is 15.10.07.14
*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:
SELECT
ctl_id as a
,CASE WHEN ctl_id =200 THEN 1
ELSE ctl_id
END as a
FROM db1.table1;

SELECT
ctl_id as a
,CASE WHEN ctl_id =200 THEN 1
ELSE ctl_id
END as a
FROM db1.table1;
*** Failure 3863 Duplicate definition of 'a' in NAMED phrase.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

 

 

If we can use the same alias name multiple times. Is it a bug in TD 15.10.07.14 or Does my database need any other settings change?

Teradata Employee

Re: Can we use same alias name multiple times in SELECT statement

Hi.

 

Checked on and old TD 15.10 version and the 3863 error is there.

 

I don't know if this can be called "a bug" or a "new feature", because naming different columns the same is ambiguous and definetly not a good practice (I've never done it and probably will not do).

 

Version 16.20 seems to admit it though...

 

Cheers.

 

Carlos.

Teradata Employee

Re: Can we use same alias name multiple times in SELECT statement

You should not use the same name for multiple columns in the same relation.

If the name is never referenced, the parser may or may not throw an error (and as you discovered, behavior may vary from one level of software to another). But it is technically invalid.