CODEWITHSUNDEEP
sql-servermssql-jdbc
user3114967
user3114967

Reputation: 629

No column name was specified for column 2 of 'a' error

I have a MySQL query and I ran it working fine but same query showing error in SQL Server.

SQL Server query:

SELECT 
    COUNT(*) cnt 
FROM 
    (SELECT DISTINCT 
         tc_id, MAX(exn_time), STATUS 
     FROM 
         release_details a, tc_details b  
     WHERE 
         a.project = b.project 
         AND a.tc_id = b.tc_name 
         AND logicaldel = 0 
         AND a.project = 'test' 
     GROUP BY 
         tc_id, STATUS) a 
WHERE 
    a.status = 'PASS';

Error:

No column name was specified for column 2 of 'a'.

How do I modify the above query?

Upvotes: 3

Views: 26012

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Your issue is obviously that your second column in the resultset a doesn't have an alias. You can rewrite the whole query to this for the same result:

SELECT
    COUNT(DISTINCT tc_id) cnt
FROM 
    release_details a
JOIN
    tc_details b  
ON
    a.project = b.project 
    AND a.tc_id = b.tc_name 
WHERE
    logicaldel = 0 
    AND a.project = 'test' 
    AND STATUS = 'PASS'

Since STATUS only can have the value 'PASS', MAX(exn_time) is not helping your counting, DISTINCT should not be used in the beginning of a SELECT when using group by like in your case, it is redundant

Upvotes: 0

Mahesh
Mahesh

Reputation: 8892

Use the Alias name for your inner query.You are getting the MAX(exn_time) but not specified the name for that column that's why throwing the error. And you can use the Joins to the tables to make it more readable.

SELECT COUNT(*) cnt 
FROM (
     SELECT DISTINCT 
         tc_id,
         MAX(exn_time) AS Maxtime ,
         STATUS 
      FROM 
         release_details a JOIN tc_details b  
           ON a.project= b.project 
             AND a.tc_id = b.tc_name 
      WHERE 
           logicaldel = 0  
           AND a.project ='test' 
      GROUP BY 
         tc_id,
         STATUS 
      ) a 
 WHERE a.status='PASS';

Upvotes: 7

Pரதீப்
Pரதீப்

Reputation: 93754

You missed to give Alias name inside subquery

Also as mentioned by Marc_s you need to use proper Inner Join, keep the join condition ON clause and move the filter to where clause

SELECT Count(*) cnt
FROM   (SELECT DISTINCT tc_id,
                        Max(exn_time) Max_exn_time,
                        STATUS
        FROM   release_details a
               INNER JOIN tc_details b
                       ON a.project = b.project
                          AND a.tc_id = b.tc_name
        WHERE  a.project = 'test'
               AND logicaldel = 0
        GROUP  BY tc_id,
                  STATUS) a
WHERE  a.status = 'PASS';

Upvotes: 1

Related Questions