invalid column name

[Anthony97]

This code works fine in access but i'm moving this query to sql it compiles fine but then when i try to execute it i get numerous errors of invalid columns. I've attached a sample of the code. As always your help is greatly appreciated.


Before this is the code from a access 2007 query

SELECT tships.[Load ],
       tships.[Ship Id],
Trim([XPD flag] & " " & [ACDT flag] & " " & [AFP flag] & " " & [AHTL flag] & " " & [AOC FLAG] & " " & [BUMP FLAG] & " " & [CARQ FLAG] & " " & [CDEL FLAG] & " " & [CFAL FLAG] & " " & [CNSW FLAG] & " " & [CRIT FLAG] & " " & [CROL FLAG] & " " & [CRTR FLAG] & " " & [CRVT FLAG] & " " & [CUDC FLAG] & " " & [DATA FLAG] & " " & [DATE FLAG] & " " & [DEFL FLAG] & " " & [DETN FLAG] & " " & [DIC FLAG] & " " & [DLDM FLAG] & " " & [DLSH FLAG] & " " & [DMRC FLAG] & " " & [DROP FLAG] & " " & [DRSK FLAG] & " " & [DTNA FLAG] & " " & [FTZ FLAG] & " " & [ILOD FLAG] & " " & [INFO FLAG] & " " & [LCFP FLAG] & " " & [LFCG FLAG] & " " & [LNFT FLAG] & " " & [MDWD FLAG] & " " & [MNMC FLAG] & " " & [MNSL FLAG] & " " & [MSCU FLAG] & " " & [NOLT FLAG] & " " & [ODEL FLAG] & " " & [OTHR FLAG])                        AS [Reason Codes1],
Trim([PRLD FLAG] & " " & [QUOT FLAG] & " " & [RAIL FLAG] & " " & [RCAA FLAG] & " " & [RCC FLAG] & " " & [RCEG FLAG] & " " & [RCIN FLAG] & " " & [RCL FLAG] & " " & [RCLN FLAG] & " " & [RCLP FLAG] & " " & [RCMC FLAG] & " " & [RCNP FLAG] & " " & [RCPS FLAG] & " " & [RCQU FLAG] & " " & [RCSC FLAG] & " " & [RCSE FLAG] & " " & [RCSH FLAG] & " " & [RCSP FLAG] & " " & [RCSQ FLAG] & " " & [RCSU FLAG] & " " & [RCTT FLAG] & " " & [RCTW FLAG] & " " & [RCWA FLAG] & " " & [RUSH FLAG] & " " & [RUTM FLAG] & " " & [SCBK FLAG] & " " & [SDEL FLAG] & " " & [SHTG FLAG] & " " & [SHTT FLAG] & " " & [SPEQ FLAG] & " " & [STDL FLAG] & " " & [TDDE FLAG] & " " & [TEAM FLAG] & " " & [TLCO FLAG] & " " & [UPSD FLAG] & " " & [VORL FLAG] & " " & [WTHR FLAG] & " " & [XRTE FLAG] & " " & [CCBK FLAG] & " " & [WBDN FLAG]) AS [Reason Codes2],
AS [Reason Codes3],
AS [XPD flag],

After this it the modifications that i've made

SELECT tShips.[Load ],
       tShips.[Ship Id],
    LTrim([XPD flag] + [ACDT flag] + [AFP flag] + [AHTL flag] + [AOC FLAG] + [BUMP FLAG] + [CARQ FLAG] + [CDEL FLAG] + [CFAL FLAG] + [CNSW FLAG] + [CRIT FLAG] + [CROL FLAG] + [CRTR FLAG] + [CRVT FLAG] + [CUDC FLAG] + [DATA FLAG] + [DATE FLAG] + [DEFL FLAG] + [DETN FLAG] + [DIC FLAG] + [DLDM FLAG] + [DLSH FLAG] + [DMRC FLAG] + [DROP FLAG] + [DRSK FLAG] + [DTNA FLAG] + [FTZ FLAG] + [ILOD FLAG] + [INFO FLAG] + [LCFP FLAG] + [LFCG FLAG] + [LNFT FLAG] + [MDWD FLAG] + [MNMC FLAG] + [MNSL FLAG] + [MSCU FLAG] + [NOLT FLAG] + [ODEL FLAG] + [OTHR FLAG]) AS [Reason Codes1],
    LTrim([PRLD FLAG] + [QUOT FLAG] + [RAIL FLAG] + [RCAA FLAG] + [RCC FLAG] + [RCEG FLAG] + [RCIN FLAG] + [RCL FLAG] + [RCLN FLAG] + [RCLP FLAG] + [RCMC FLAG] + [RCNP FLAG] + [RCPS FLAG] + [RCQU FLAG] + [RCSC FLAG] + [RCSE FLAG] + [RCSH FLAG] + [RCSP FLAG] + [RCSQ FLAG] + [RCSU FLAG] + [RCTT FLAG] + [RCTW FLAG] + [RCWA FLAG] + [RUSH FLAG] + [RUTM FLAG] + [SCBK FLAG] + [SDEL FLAG] + [SHTG FLAG] + [SHTT FLAG] + [SPEQ FLAG] + [STDL FLAG] + [TDDE FLAG] + [TEAM FLAG] + [TLCO FLAG] + [UPSD FLAG] + [VORL FLAG] + [WTHR FLAG] + [XRTE FLAG] + [CCBK FLAG] + [WBDN FLAG]) AS [Reason Codes2],
    LTrim([reason codes1] & [reason codes2]) AS [Reason Codes3],
       CASE 
         WHEN [xpd] = 1
         THEN 'XPD'
         ELSE NULL
       END AS [XPD FLAG],

[ck9663]

What column is throwing the error? And you also missed on in

LTrim([reason codes1] & [reason codes2]) AS [Reason Codes3]

Happy Coding!!!

~~ CK

[Anthony97]

I changed the & to + between [reason codes1] + [reason codes2] I'm still getting invalid columns starting with xpd flag and it continues to the next flag

[ck9663]

Check if all these columns are in your table. Also, there's no TRIM() function in SQL Server. Only LTRIM() and RTRIM().

Good Luck!!!

~~ CK