SQL query using alias

Question

I have tried to write below SQL query in MS SQL server, but it is showing syntax error near WHERE.

Select ECUID,ECUNAME 
FROM 
(SELECT     [ECU-ID] as ECUID, [ECU-NAME]as ECUNAME FROM         TBL_ECU_MAST)
WHERE ECUID = 147;

Please help me to solve this.

All subqueries require an alias in SQL Server. I view this as a typographical error. — Gordon Linoff
– Gordon Linoff, Commented Apr 26, 2017 at 11:58

Milney · Accepted Answer · 2017-04-26 12:00:22Z

3

Just give your expression an Alias;

Select aliasHere.ECUID, aliasHere.ECUNAME 
FROM 
(SELECT     
    [ECU-ID] as ECUID, [ECU-NAME]as ECUNAME 
    FROM         TBL_ECU_MAST) aliasHere
WHERE aliasHere.ECUID = 147;

Obviously use a more descriptive alias...

answered Apr 26, 2017 at 12:00

Milney

6,4472 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hassan naeimi · Accepted Answer · 2017-04-26 12:50:42Z

3

you can use another way.like below code

;with t as
(
SELECT [ECU-ID] AS ECUID, [ECU-NAME] AS ECUNAME FROM TBL_ECU_MAST
)
SELECT ECUID,ECUNAME 
FROM t WHERE ECUID = 147

answered Apr 26, 2017 at 12:50

hassan naeimi

1194 bronze badges

Comments

DataWrangler · Accepted Answer · 2017-04-26 12:07:10Z

2

As @Gordon suggested you have to give an alias name for the SubQuery used within the SQL Statement

SELECT ECUID,ECUNAME 
FROM 
  (SELECT [ECU-ID] AS ECUID, [ECU-NAME] AS ECUNAME FROM TBL_ECU_MAST) T
WHERE T.ECUID = 147;

Hope this helps!!

answered Apr 26, 2017 at 12:07

DataWrangler

2,20520 silver badges35 bronze badges

Comments

Mansoor · Accepted Answer · 2017-04-26 12:06:39Z

0

Give alias name for all sub queries :

SELECT A.ECUID, A.ECUNAME 
FROM 
(
  SELECT [ECU-ID] as ECUID, [ECU-NAME]as ECUNAME 
  FROM TBL_ECU_MAST 
) A
 WHERE A.ECUID = 147;

answered Apr 26, 2017 at 12:06

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL query using alias

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related