Receiving syntax error on SQL script within Vbscript

[kdjonesmtb2]

I am receiving the following syntax error for a table alias that is being used for a SQL script within a Vbscript

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'C'.

Line (186): "Set rs20 = conn.execute(query)".


strConnection = "DRIVER=SQL Server;SERVER=abn-nhp-sql-t01, 11001;UID=keithg;APP=QuickTest Professional;WSID=KEITHGJ790;DATABASE=NHPInterface;Trusted_Connection=Yes"


'Source file is QNXT NHP APCD file sent to Beacon via FTP
'Target file is return file sent from Beacon to NHP via FTP
'Source file is located:\\Nhpftp01\d$\R3_Prod\users\beacon\Interface\Out\TestFiles



Set conn = CreateObject("ADODB.Connection")


conn.Open strConnection



query = "Select " &_
"O.BEME001 as NHP_BEME001 " &_
",C.BEME001 as Beacon_BEME001 " &_
",O.ME107 as NHP_ME107 " &_
",C.ME107 as Beacon_ME107 " &_
",O.ME105 as NHP_ME105 " &_
",C.ME105 as Beacon_ME105 " &_
",O.ME104 AS NHP_ME104 " &_
",C.ME104 AS Beacon_ME104 " &_
",O.ME014 AS NHP_ME014 " &_
",C.ME014 AS Beacon_ME014 " &_
",O.ME013 AS NHP_ME013 " &_
",C.ME013 AS Beacon_ME013 " &_
",O.ME043 AS NHP_ME043 " &_
",C.ME043 AS Beacon_ME043 " &_
",O.ME044 AS NHP_ME044 " &_
",C.ME044 AS Beacon_ME044 " &_
",O.ME015 AS NHP_ME015 " &_
",C.ME015 AS Beacon_ME015 " &_
",O.ME016 AS NHP_ME016 " &_
",C.ME016 as Beacon_ME016 " &_
",O.ME017 as NHP_ME017 " &_
",C.ME017 as Beacon_ME017 " &_
",O.BEME016 AS NHP_BEME016 " &_
",C.BEME016 AS Beacon_BEME016 " &_
",O.BEME018 as NHP_BEME018 " &_
",C.BEME018 as Beacon_BEME018 " &_
",O.BEME019 AS NHP_BEME019 " &_
",C.BEME019 AS Beacon_BEME019 " &_
",O.BEME020 AS NHP_BEME020 " &_
",C.BEME020 AS Beacon_BEME020 " &_
",O.ME009 AS NHP_ME009 " &_
",C.ME009 AS Beacon_ME009 " &_
",O.ME102 as NHP_ME102 " &_
",C.ME102 as Beacon_ME102 " &_
",O.ME101 as NHP_ME101 " &_
",C.ME101 as Beacon_ME101 " &_
",O.ME058 as NHP_ME058 " &_
",C.ME058 as Beacon_ME058 " &_
",O.BEME021 as NHP_BEME021 " &_
",C.BEME021 as Beacon_BEME021" &_
",O.ME108 as NHP_ME108 " &_
",C.ME108 as Beacon_ME108 " &_
",O.ME109 as NHP_ME109 " &_
",C.ME109 as Beacon_ME109 " &_
",O.ME110 as NHP_ME110 " &_
",C.ME110 as Beacon_ME110 " &_
",O.BEME022 as NHP_BEME022 " &_
",C.BEME022 as Beacon_BEME022 " &_
",O.ME062 as NHP_ME062 " &_
",C.ME062 as Beacon_ME062 " &_
",O.ME041 as NHP_ME041 " &_
",C.ME041 as Beacon_ME041" &_
",O.ME042 as NHP_ME042 " &_
",O.ME046 as NHP_ME046 " &_
",C.ME046 as Beacon_ME046 " &_
",O.BEME005 as NHP_BEME005 " &_
",C.BEME005 as Beacon_BEME005 " &_
",O.BEME006 as NHP_BEME006 " &_
",C.BEME006 as Beacon_BEME006 " &_
",O.BEME002 as NHP_BEME002 " &_
",C.BEME002 as Beacon_BEME002 " &_
",O.BEME032 as NHP_BEME032 " &_
",C.BEME032 as Beacon_BEME032 " &_
",O.BEME033 as NHP_BEME033 " &_
",C.BEME033 as Beacon_BEME033 " &_
",ME107Variance=case when O.ME107=C.ME107 then '1' Else '0' end " &_
"From dbo.Beacon_NHP_APCD_20130407 O " &_
"Inner Join dbo.Beacon_ResponseQCFile_20130425 C "

Set rs20 = conn.execute(query)

 

[guitarzan]

Your Inner Join is missing an ON clause.

Have the responses people have given to all your other recent posts been any help at all?

 

[PHV]

Replace this:
",ME107Variance=case when O.ME107=C.ME107 then '1' Else '0' end " &_
with this:
",case when O.ME107=C.ME107 then '1' Else '0' end as ME107Variance" &_

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886