SQL Server query error with the error "Object or column name is missing or empty"

Question

I have the following query in a stored procedure on an SQL server:

SELECT TLI.LESNumber ,COUNT(TLT.PL) INTO #PWCM FROM #tmpLESImport TLI INNER JOIN tbl_LES L on TLI.LESNumber=L.NUMB WHERE ISNULL(L.DELT_FLAG,0)=0 AND L.SCHL_PK=@SCHL _PK AND TLI.PL IS NOT NULL AND LEN(TLI.PL)>0 GROUP BY LESNumber HAVING COUNT(PL)>1 

When the request is executed, I get the following error:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Can someone tell me why? #PWCM does not appear anywhere before this request.

Answer 1

When you SELECT INTO table, it creates a table (in this case, a temporary table). To create a table, each column requires a name that is not specified in the count column. You just need to give it a name:

 SELECT TLI.LESNumber,COUNT(TLT.PL) [NumRecords] INTO #PWCM FROM #tmpLESImport TLI ... 

Answer 2

I had this error for this request

 SELECT CASE WHEN COALESCE([dbo].[my-table].[field],"") = '...' THEN 'A' WHEN COALESCE([dbo].[my-table].[field],"") = '...' THEN 'B' ... END AS aaa INTO ##TEMPTABLE FROM [dbo].[my-table] 

Turns out I had to change the "" inside COALSCE to '' .

Solved it for me