SQL Server nested query in #temp table throwing column name error

prdp8dxp  于 2023-04-04  发布在  其他
关注(0)|答案(1)|浏览(197)

The following code gives me an invalid column name error for all the fields that I am trying to alter in the temporary table. It works fine if instead I write the table.

From other posts I have tried to change UPDATE #temp_polelbyregion with UPDATE y but I still have the same error messages

ALTER TABLE #temp_polelbyregion ALTER COLUMN REGION varchar(100)
ALTER TABLE #temp_polelbyregion ADD [Coordinate] float null
ALTER TABLE #temp_polelbyregion ADD [Street] float null
ALTER TABLE #temp_polelbyregion ADD [Block] float null
ALTER TABLE #temp_polelbyregion ADD [Postcode] float null
ALTER TABLE #temp_polelbyregion ADD [City] float null
ALTER TABLE #temp_polelbyregion ADD [County] float null
ALTER TABLE #temp_polelbyregion ADD [Cresta] float null
ALTER TABLE #temp_polelbyregion ADD [State] float null
ALTER TABLE #temp_polelbyregion ADD [Country] float null
ALTER TABLE #temp_polelbyregion ADD [None] float null
ALTER TABLE #temp_polelbyregion ADD [KnownCON] float null
ALTER TABLE #temp_polelbyregion ADD [KnownYB] float null
ALTER TABLE #temp_polelbyregion ADD [KnownHGT] float null
ALTER TABLE #temp_polelbyregion ADD [KnownOCC] float null

UPDATE #temp_polelbyregion
SET 
 Coordinate = x.Coordinate
,Street = x.Street
,Block = x.Block
,Postcode = x.Postcode
,City = x.City
,County = x.County
,Cresta = x.Cresta
,State = x.State
,Country = x.Country
,None = x.None
,KnownCON = x.KnownCON
,KnownYB = x.KnownYB
,KnownHGT = x.KnownHGT
,KnownOCC = x.KnownOCC
FROM
    (
    SELECT
     ACCGRPID
    ,REGION
    ,Coordinate = SUM(CASE WHEN GeoResolutionCode in (1) THEN LOCVAL ELSE 0 END)
    ,Street = SUM(CASE WHEN GeoResolutionCode in (2,3) THEN LOCVAL ELSE 0 END)
    ,Block = SUM(CASE WHEN GeoResolutionCode in (4) THEN LOCVAL ELSE 0 END)
    ,Postcode = SUM(CASE WHEN GeoResolutionCode in (5,6) THEN LOCVAL ELSE 0 END)
    ,City = SUM(CASE WHEN GeoResolutionCode in (7,8) THEN LOCVAL ELSE 0 END)
    ,County = SUM(CASE WHEN GeoResolutionCode in (9) THEN LOCVAL ELSE 0 END)
    ,Cresta = SUM(CASE WHEN GeoResolutionCode in (11) THEN LOCVAL ELSE 0 END)
    ,State = SUM(CASE WHEN GeoResolutionCode in (10,12) THEN LOCVAL ELSE 0 END)
    ,Country = SUM(CASE WHEN GeoResolutionCode in (14) THEN LOCVAL ELSE 0 END)
    ,None = SUM(CASE WHEN GeoResolutionCode in (0) THEN LOCVAL ELSE 0 END)
    ,KnownCON = SUM(CASE WHEN [BLDGSCHEME] <> 'RMS IND' AND [BLDGCLASS] = '0' THEN 0 ELSE LOCVAL END)
    ,KnownYB = SUM(CASE WHEN YEAR([YEARBUILT]) = 9999 THEN 0 ELSE LOCVAL END)
    ,KnownHGT = SUM(CASE WHEN [NUMSTORIES] = 0 THEN 0 ELSE LOCVAL END)
    ,KnownOCC = SUM(CASE WHEN [OCCTYPE] = '0' THEN 0 ELSE LOCVAL END)
    FROM #temp_locs
    GROUP BY ACCGRPID, REGION
    ) x INNER JOIN #temp_polelbyregion y ON x.REGION = y.REGION AND x.ACCGRPID = y.ACCGRPID

The error is:

Invalid column name 'Coordinate'.
Msg 207, Level 16, State 1, Line 851
Invalid column name 'Street'.
Msg 207, Level 16, State 1, Line 852
Invalid column name 'Block'.
Msg 207, Level 16, State 1, Line 853
Invalid column name 'Postcode'.
Msg 207, Level 16, State 1, Line 854
Invalid column name 'City'.
Msg 207, Level 16, State 1, Line 855
Invalid column name 'County'.
Msg 207, Level 16, State 1, Line 856
Invalid column name 'Cresta'.
Msg 207, Level 16, State 1, Line 857
Invalid column name 'State'.
Msg 207, Level 16, State 1, Line 858
Invalid column name 'Country'.
Msg 207, Level 16, State 1, Line 859
Invalid column name 'None'.
Msg 207, Level 16, State 1, Line 860
Invalid column name 'KnownCON'.
Msg 207, Level 16, State 1, Line 861
Invalid column name 'KnownYB'.
Msg 207, Level 16, State 1, Line 862
Invalid column name 'KnownHGT'.
Msg 207, Level 16, State 1, Line 863
Invalid column name 'KnownOCC'.```
wgeznvg7

wgeznvg71#

That UPDATE is not syntactically correct. I think you want to INSERT:

INSERT INTO #temp_polelbyregion  

(( SELECT ACCGRPID ,REGION ,Coordinate = SUM(CASE WHEN GeoResolutionCode in (1) THEN LOCVAL ELSE 0 END) ,
Street = SUM(CASE WHEN GeoResolutionCode in (2,3) THEN LOCVAL ELSE 0 END) ,
Block = SUM(CASE WHEN GeoResolutionCode in (4) THEN LOCVAL ELSE 0 END) ,
Postcode = SUM(CASE WHEN GeoResolutionCode in (5,6) THEN LOCVAL ELSE 0 END) ,
City = SUM(CASE WHEN GeoResolutionCode in (7,8) THEN LOCVAL ELSE 0 END) ,County = SUM(CASE WHEN GeoResolutionCode in (9) THEN LOCVAL ELSE 0 END) ,Cresta = SUM(CASE WHEN GeoResolutionCode in (11) THEN LOCVAL ELSE 0 END) ,State = SUM(CASE WHEN GeoResolutionCode in (10,12) THEN LOCVAL ELSE 0 END) ,Country = SUM(CASE WHEN GeoResolutionCode in (14) THEN LOCVAL ELSE 0 END) ,None = SUM(CASE WHEN GeoResolutionCode in (0) THEN LOCVAL ELSE 0 END) ,KnownCON = SUM(CASE WHEN [BLDGSCHEME] <> 'RMS IND' AND [BLDGCLASS] = '0' THEN 0 ELSE LOCVAL END) ,KnownYB = SUM(CASE WHEN YEAR([YEARBUILT]) = 9999 THEN 0 ELSE LOCVAL END) ,KnownHGT = SUM(CASE WHEN [NUMSTORIES] = 0 THEN 0 ELSE LOCVAL END) ,KnownOCC = SUM(CASE WHEN [OCCTYPE] = '0' THEN 0 ELSE LOCVAL END) 
FROM #temp_locs GROUP BY ACCGRPID, REGION ) x INNER JOIN #temp_polelbyregion y ON x.REGION = y.REGION AND x.ACCGRPID = y.ACCGRPID)

For syntax examples see https://www.w3schools.com/sql/sql_update.asp

相关问题