No column was specified for column ошибка - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

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?

marc_s

729k174 gold badges1327 silver badges1455 bronze badges

asked Mar 3, 2015 at 11:19

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';

answered Mar 3, 2015 at 11:21

MaheshMahesh

8,4912 gold badges32 silver badges52 bronze badges

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';

answered Mar 3, 2015 at 11:22

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

91.4k18 gold badges130 silver badges168 bronze badges

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

answered Mar 3, 2015 at 11:51

t-clausen.dkt-clausen.dk

43.3k12 gold badges55 silver badges91 bronze badges

Источник

Remove From My Forums

Question

Hello, I’m trying to run a small script that will tell me the below information.

* “Cash in hand and at bank” for all dealerships in December 2017
* totals for all dealerships broken out by their Business Unit

I’m stuck with a «cte» error please can anyone advise where I have gone wrong.

ERROR —

Msg 8155, Level 16, State 2, Line 41
No column name was specified for column 2 of ‘cte1’.

Use BLANK


-- Temp Table 1 --------------------------------------------------------------------------------------------------------------------
CREATE TABLE #BLANK ([BUSNS_ASCT_CD] varchar(13), [Dealer_Code] varchar(13), [BUSNS_UNIT_ID] varchar(2), [YTD_RPT_AMT] varchar(15), [FINCL_ACCT_TYP_NBR] decimal, [RPT_YR_NBR] decimal)
-------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO #BLANK ([BUSNS_ASCT_CD], [Dealer_Code], [BUSNS_UNIT_ID], [YTD_RPT_AMT], [FINCL_ACCT_TYP_NBR], [RPT_YR_NBR])  
	Select
		b.BUSNS_ASCT_CD,                     
		b.Dealer_Code,                       
		a.BUSNS_UNIT_ID,                     
		c.YTD_RPT_AMT,                       
		c.FINCL_ACCT_TYP_NBR,
		c.RPT_YR_NBR                         
		
--INTO  #BLANK 
	  
		from [COUNTRY] a
    Left join [GMBA] b
		ON b.CNTRY_CD = a.CNTRY_CD           
	
	inner join DLR_FINCL_ACCT_DTL c 
		on c.BUSNS_ASCT_CD = b.BUSNS_ASCT_CD

	Where FINCL_ACCT_TYP_NBR = 2000	And RPT_YR_NBR = 2017		
		




;with cte1 
as 
(
select distinct BUSNS_ASCT_CD, SUM(CAST(YTD_RPT_AMT As numeric(15))) OVER (Partition by BUSNS_UNIT_ID), YTD_RPT_AMT, BUSNS_UNIT_ID, FINCL_ACCT_TYP_NBR, RPT_YR_NBR                     
FROM #BLANK
)
                   
	
select	
        BUSNS_ASCT_CD, YTD_RPT_AMT, BUSNS_UNIT_ID, FINCL_ACCT_TYP_NBR, RPT_YR_NBR                                                       			     
from cte1




---------------------------------------------------------------------------------------------------------------------------------------
-------------- Need to get rid of the temp table 
USE BLANK
Drop Table #BLANK

Answers

Hi FENGE2,

As your error message shows that ‘No column name was specified for column 2 of ‘cte1»,
it means that the second column ‘SUM(CAST(YTD_RPT_AMT As numeric(15))) OVER (Partition by BUSNS_UNIT_ID)’ has no column name.

So, if you would like to make the name of
Window Functions to ‘YTD_RPT_AMT’ or other name ?

For more details about WITH common_table_expression , you can refer to this article :https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

Here is the example script.
```
------change the name of window functions  to 'YTD_RPT_AMT'
;with cte1 as 
(
select distinct 
BUSNS_ASCT_CD, 
SUM(CAST(YTD_RPT_AMT As numeric(15))) OVER (Partition by BUSNS_UNIT_ID) as  YTD_RPT_AMT, 
BUSNS_UNIT_ID, 
FINCL_ACCT_TYP_NBR, 
RPT_YR_NBR                     
FROM #BLANK
) 	
select	
BUSNS_ASCT_CD, YTD_RPT_AMT, BUSNS_UNIT_ID, FINCL_ACCT_TYP_NBR, RPT_YR_NBR                                                       			     
from cte1

------change the name of window functions  to other name 
;with cte1 as 
(
select distinct 
BUSNS_ASCT_CD, 
SUM(CAST(YTD_RPT_AMT As numeric(15))) OVER (Partition by BUSNS_UNIT_ID) as  sum_value,
YTD_RPT_AMT, 
BUSNS_UNIT_ID, 
FINCL_ACCT_TYP_NBR, 
RPT_YR_NBR                     
FROM #BLANK
) 	
select	
BUSNS_ASCT_CD,sum_value, YTD_RPT_AMT, BUSNS_UNIT_ID, FINCL_ACCT_TYP_NBR, RPT_YR_NBR                                                       			     
from cte1
```
Hope it can help you.

Best Regards,

Rachel

MSDN Community Support
Please remember to click «Mark as Answer» the responses that resolved your issue, and to click «Unmark as Answer» if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com.
- Proposed as answer by
  
  Monday, November 5, 2018 8:41 AM
- Marked as answer by
  FENGE2
  Monday, November 5, 2018 11:10 AM

Источник

I have this table:

from which I create this View:

This view’s CardId allows me to JOIN against the Card table with so I can retrieve the Count from any CardId. Here’s my SQL:

SELECT * FROM (
    SELECT
        si.CardId SourceCardId,
        ti.CardId TargetCardId,
        (SELECT TOP 1 r.Name
        FROM dbo.Relationship r
        WHERE r.Id = rac.RelationshipId) [Name],
        Count(*) [Count]
    FROM dbo.Relationship_AcquiredCard rac
    JOIN dbo.AcquiredCard sac ON rac.SourceAcquiredCardId = sac.Id
    JOIN dbo.AcquiredCard tac ON rac.TargetAcquiredCardId = tac.Id
    JOIN dbo.CardInstance si ON sac.CardInstanceId = si.Id
    JOIN dbo.CardInstance ti ON tac.CardInstanceId = ti.Id
    GROUP BY si.CardId, ti.CardId, rac.RelationshipId
    -- you can probably ignore everything above
    ) X
CROSS APPLY
    (values (X.TargetCardId),
            (X.SourceCardId)
    ) whatdoesthisdo(CardId) --wut

What does whatdoesthisdo do? I got the CROSS APPLY from this answer. If I try to alias in the usual way, I get this error:

Thanks!

asked Feb 16, 2020 at 16:53

It should be clear from the below

SELECT WhatDoesThisDo.CardId
FROM   (VALUES (1),
               (2) ) WhatDoesThisDo(CardId)

WhatDoesThisDo provides the table alias for the derived table defined by the VALUES clause. It then requires a comma delimited list of all column names (as there is no way of naming them inside the VALUES itself).

In this case it only returns one column so the complete column list is (CardId)

The relevant part of the grammar is

derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]

though the optionality indicated by the square brackets above does not apply with table value constructors.

answered Feb 16, 2020 at 17:10

Martin SmithMartin Smith

81.3k15 gold badges232 silver badges327 bronze badges

Источник

Error Message

When validating SQL with a computed column in a Query Layer in SQL Server, the following error may occur.

Underlying DBMS error: ~ No column name was specified for column [n] of 'a'.

Cause

A column alias is not specified for a computed column.

When a query layer is initially created, the user defined query is utilized as a sub-query to select the geometry column.

For example:

Code:
SELECT objectid, shape.STConvexHull() as shape2 FROM sde.mySpatialTable

A database trace returns:

Code:
exec sp_prepare @p1 ...,N'select shape2 from (SELECT objectid, shape.STConvexHull() as shape2 
FROM sde.mySpatialTable) a',1

If an alias is not provided for the STConvexHull() computed column in the Query Layer dialog, such as in the case below:

Code:
SELECT objectid, shape.STConvexHull() FROM sde.mySpatialTable

an error occurs.

Underlying DBMS error [[Microsoft][SQL Server Native Client 10.0][SQL Server} 
No column name was specified for column 2 of 'a'.]

Solution or Workaround

Ensure computed columns receive a column alias. See the example below.

Code:
SELECT shape.STConvexHull() AS MyConvexHull FROM MySpatialTable;

Источник

Home > SQL Server Error Messages > Msg 8155 — No column name was specified for column <Column Index> of ‘<Object Name>’.

SQL Server Error Messages — Msg 8155 — No column name was specified for column <Column Index> of ‘<Object Name>’.

SQL Server Error Messages — Msg 8155

Error Message

Server: Msg 8155, Level 16, State 2, Line 1
No column name was specified for column <Column Index> 
of '<Object Name>'.

Causes

There’s a couple of ways of how this error can be encountered. The first method is through the use of sub-queries wherein the sub-query is used in a JOIN statement. To illustrate, here’s a simple query that will generate this error message:

SELECT *
FROM [dbo].[Sales] A INNER JOIN (SELECT [SalesDate], MAX([SalesAmount])
                                 FROM [dbo].[Sales]
                                 GROUP BY [SalesDate]) B
  ON A.[SalesDate] = B.[SalesDate]
GO

Msg 8155, Level 16, State 2, Line 5
No column name was specified for column 2 of 'B'.

Another way of encountering this error message is with the use of row constructor or table-valued constructor as derived table. Introduced in SQL Server 2008, row constructors consist of a single VALUES clause with multiple value lists enclosed in parenthesis and separated by commas. The following script illustrates how this error message may be generated with a row constructor or table-valued constructor:

SELECT *
FROM (VALUES ( 'FL', 'Florida' ),
             ( 'NY', 'New York' )) [USState]
GO

Msg 8155, Level 16, State 2, Line 3
No column name was specified for column 1 of 'USState'.
Msg 8155, Level 16, State 2, Line 3
No column name was specified for column 2 of 'USState'.

Solution / Work Around:

As the message suggests, a column name has to be specified in either a sub-query or in a row constructor or table-valued constructor used as derived table. The column name is not required if a query that uses a group function such as MAX or AVG is executed by itself but once that query is used as a sub-query in a JOIN statement, then a column name has to be specified for the output of the group function.

In the first scenario, supplying a column name in the MAX([SalesAmount]) group function will overcome this error:

SELECT *
FROM [dbo].[Sales] A INNER JOIN (SELECT [SalesDate], MAX([SalesAmount]) AS [MaxSalesAmount]
                                 FROM [dbo].[Sales]
                                 GROUP BY [SalesDate]) B
  ON A.[SalesDate] = B.[SalesDate]
GO

Similarly, in the second scenario, providing column names for each value included in the table-valued constructor will overcome this error:

SELECT *
FROM (VALUES ( 'FL', 'Florida' ),
             ( 'NY', 'New York' )) [USState] ( [Code], [Name] )
GO

The output of this query is as follows:

Code  Name
----- ------------
FL    Florida
NY    New York

Frequently Asked
Questions — SQL Server Error Messages
Tips & Tricks — SQL Server Error Messages 1 to 500

Источник

Question

Answers

Error Message

Cause

Solution or Workaround