WinSQL Query - Syntax for use in Excel
Hi All
I have this SQL query which works fine in WinSQL select oqrout as "Route", oqdlix as "Delivery #", oaortp as "Order Type", oborno as "CO #", oacuno as "Customer #", okcunm as "Customer", obponr as "Line #", obitno as "Item #", mmitds as "Description", oborst as "Line Status", oborqt as "Qty", oarldt as "Requested Delv Dt", obdsdt as "Sched Delv Dt" From mvxcdtprod.ooline inner join mvxcdtprod.mitmas on mmitno = obitno inner join mvxcdtprod.mhdisl on urridn||digits(urridl) = oborno||'0'|| digits(obponr)||digits(obposx) inner join mvxcdtprod.mhdish on oqdlix = urdlix and oqinou in (1,3) inner join mvxcdtprod.oohead on oaorno = oborno and oaortp in ('1CR','1CS','1CU','1DS','1EX','1NO','1NP','1PU') inner join mvxcdtprod.ocusma on okcuno = oacuno where obdsdt <= '20090305' and oborno '2000052540' and oborst between 44 and 47 and oaoblc < '6' and (oqrout = '611CPU' or oaortp = '1PU') group by oqrout, oqdlix, oaortp, oborno, oacuno, okcunm, obponr, obitno, mmitds, oborst, oborqt, oaortp, oarldt, obdsdt I am trying to incorporate it into an Excel vba program and I can't seem to get the syntax correct. this is what i currently have: mysql = "select oqrout as ""Route"", oqdlix as ""Delivery #"", oaortp as ""Order Type"", " & _ "oborno as ""CO #"", oacuno as ""Customer #"", " & _ "okcunm as ""Customer"", obponr as ""Line #"", obitno as ""Item #"", " & _ "mmitds as ""Description"", oborst as ""Line Status"", oborqt as ""Qty"", " & _ "oarldt as ""Requested Delv Dt"", obdsdt as ""Sched Delv Dt"", obposx as ""Posx"" " & _ " From mvxcdtprod.ooline " & _ "inner join mvxcdtprod.mitmas on mmitno = obitno " & _ "inner join mvxcdtprod.mhdisl on uurridn||digits(urridl) = oborno||'0'||digits(obponr)||digits(obposx) " & _ "inner join mvxcdtprod.mhdish on oqdlix = urdlix and oqinou in (1,3) " & _ "inner join mvxcdtprod.oohead on oaorno = oborno and oaortp in ('1CR','1CS','1CU','1DS','1EX','1NO','1NP','1PU') " & _ "inner join mvxcdtprod.ocusma on okcuno = oacuno " & _ " where obdsdt <= '" & Format(Date, "yyyymmdd") & "' " & _ "and oborno '2000052540' " & _ "and oborst between 44 and 47 " & _ "and oaoblc < '6' " & _ "and (oqrout = '611CPU' or oaortp = '1PU') " & _ " group by oqrout, oqdlix, oaortp, oborno, oacuno, okcunm, obponr, obitno, mmitds, oborst, oborqt, oaortp, oarldt, obdsdt, obposx" when i run it i get a "General OBDC error". Would appreciate any help. Thanks Julie |
WinSQL Query - Syntax for use in Excel
I re-wrote you code to make it easier to debug. I would remove lines from
the code below to isolate your problem. Start with 1 select option 1 Where option and 1 From option. If this works then add lines until it works (first try my modifications to see if you get the same error). SQL statment sometimes (depending on the server) need returns after 256 characters. Not sure if you are getting the errors because the lines are too long. I trick I use is to manually perform a query from excel using the worksheet menu Data - Import External Data - New Web query (or database query) and select the option(s) using the wizard. The last menu on the Wizard that has FINISH has an option View or Edit Query. Select this option and press Finish. A new window will open. Select View SQL. Yuo can copy these SQL statments and put them into your macro. Selectmysql = "select oqrout as ""Route""," & _ "oqdlix as ""Delivery #""," & _ "oaortp as ""Order Type"", " & _ "oborno as ""CO #""," & _ "oacuno as ""Customer #""," & _ "okcunm as ""Customer""," & _ "obponr as ""Line #""," & _ "obitno as ""Item #""," & _ "mmitds as ""Description""," & _ "oborst as ""Line Status""," & _ "oborqt as ""Qty""," & _ "oarldt as ""Requested Delv Dt""," & _ "obdsdt as ""Sched DelvDt""," & _ "obposx as ""Posx"" " FromSQL = "From mvxcdtprod.ooline " & _ "inner join mvxcdtprod.mitmas on mmitno = obitno " & _ "inner join mvxcdtprod.mhdisl on uurridn||digits(urridl) = " & _ "oborno||'0'||digits(obponr)||digits(obposx) " & _ "inner join mvxcdtprod.mhdish on oqdlix = urdlix and " & _ "oqinou in (1,3) " & _ "inner join mvxcdtprod.oohead on oaorno = oborno and" & _ "oaortp in ('1CR','1CS','1CU','1DS','1EX','1NO','1NP','1PU') " & _ "inner join mvxcdtprod.ocusma on okcuno = oacuno " WhereSQL = "where obdsdt <= '" & Format(Date, "yyyymmdd") & "' " & _ "and oborno '2000052540' " & _ "and oborst between 44 and 47 " & _ "and oaoblc < '6' " & _ "and (oqrout = '611CPU' or oaortp = '1PU') " & _ " group by oqrout, oqdlix, oaortp, oborno, oacuno, okcunm, " & _ "obponr, obitno, mmitds, oborst, oborqt, oaortp, oarldt, obdsdt, obposx" SQL = SelectSQL & FromSQL & WhereSQL " wrote: Hi All I have this SQL query which works fine in WinSQL select oqrout as "Route", oqdlix as "Delivery #", oaortp as "Order Type", oborno as "CO #", oacuno as "Customer #", okcunm as "Customer", obponr as "Line #", obitno as "Item #", mmitds as "Description", oborst as "Line Status", oborqt as "Qty", oarldt as "Requested Delv Dt", obdsdt as "Sched Delv Dt" From mvxcdtprod.ooline inner join mvxcdtprod.mitmas on mmitno = obitno inner join mvxcdtprod.mhdisl on urridn||digits(urridl) = oborno||'0'|| digits(obponr)||digits(obposx) inner join mvxcdtprod.mhdish on oqdlix = urdlix and oqinou in (1,3) inner join mvxcdtprod.oohead on oaorno = oborno and oaortp in ('1CR','1CS','1CU','1DS','1EX','1NO','1NP','1PU') inner join mvxcdtprod.ocusma on okcuno = oacuno where obdsdt <= '20090305' and oborno '2000052540' and oborst between 44 and 47 and oaoblc < '6' and (oqrout = '611CPU' or oaortp = '1PU') group by oqrout, oqdlix, oaortp, oborno, oacuno, okcunm, obponr, obitno, mmitds, oborst, oborqt, oaortp, oarldt, obdsdt I am trying to incorporate it into an Excel vba program and I can't seem to get the syntax correct. this is what i currently have: mysql = "select oqrout as ""Route"", oqdlix as ""Delivery #"", oaortp as ""Order Type"", " & _ "oborno as ""CO #"", oacuno as ""Customer #"", " & _ "okcunm as ""Customer"", obponr as ""Line #"", obitno as ""Item #"", " & _ "mmitds as ""Description"", oborst as ""Line Status"", oborqt as ""Qty"", " & _ "oarldt as ""Requested Delv Dt"", obdsdt as ""Sched Delv Dt"", obposx as ""Posx"" " & _ " From mvxcdtprod.ooline " & _ "inner join mvxcdtprod.mitmas on mmitno = obitno " & _ "inner join mvxcdtprod.mhdisl on uurridn||digits(urridl) = oborno||'0'||digits(obponr)||digits(obposx) " & _ "inner join mvxcdtprod.mhdish on oqdlix = urdlix and oqinou in (1,3) " & _ "inner join mvxcdtprod.oohead on oaorno = oborno and oaortp in ('1CR','1CS','1CU','1DS','1EX','1NO','1NP','1PU') " & _ "inner join mvxcdtprod.ocusma on okcuno = oacuno " & _ " where obdsdt <= '" & Format(Date, "yyyymmdd") & "' " & _ "and oborno '2000052540' " & _ "and oborst between 44 and 47 " & _ "and oaoblc < '6' " & _ "and (oqrout = '611CPU' or oaortp = '1PU') " & _ " group by oqrout, oqdlix, oaortp, oborno, oacuno, okcunm, obponr, obitno, mmitds, oborst, oborqt, oaortp, oarldt, obdsdt, obposx" when i run it i get a "General OBDC error". Would appreciate any help. Thanks Julie |
WinSQL Query - Syntax for use in Excel
For starters I would try without the double-quoted aliases, so oqrout as
Route etc. RBS wrote in message ... Hi All I have this SQL query which works fine in WinSQL select oqrout as "Route", oqdlix as "Delivery #", oaortp as "Order Type", oborno as "CO #", oacuno as "Customer #", okcunm as "Customer", obponr as "Line #", obitno as "Item #", mmitds as "Description", oborst as "Line Status", oborqt as "Qty", oarldt as "Requested Delv Dt", obdsdt as "Sched Delv Dt" From mvxcdtprod.ooline inner join mvxcdtprod.mitmas on mmitno = obitno inner join mvxcdtprod.mhdisl on urridn||digits(urridl) = oborno||'0'|| digits(obponr)||digits(obposx) inner join mvxcdtprod.mhdish on oqdlix = urdlix and oqinou in (1,3) inner join mvxcdtprod.oohead on oaorno = oborno and oaortp in ('1CR','1CS','1CU','1DS','1EX','1NO','1NP','1PU') inner join mvxcdtprod.ocusma on okcuno = oacuno where obdsdt <= '20090305' and oborno '2000052540' and oborst between 44 and 47 and oaoblc < '6' and (oqrout = '611CPU' or oaortp = '1PU') group by oqrout, oqdlix, oaortp, oborno, oacuno, okcunm, obponr, obitno, mmitds, oborst, oborqt, oaortp, oarldt, obdsdt I am trying to incorporate it into an Excel vba program and I can't seem to get the syntax correct. this is what i currently have: mysql = "select oqrout as ""Route"", oqdlix as ""Delivery #"", oaortp as ""Order Type"", " & _ "oborno as ""CO #"", oacuno as ""Customer #"", " & _ "okcunm as ""Customer"", obponr as ""Line #"", obitno as ""Item #"", " & _ "mmitds as ""Description"", oborst as ""Line Status"", oborqt as ""Qty"", " & _ "oarldt as ""Requested Delv Dt"", obdsdt as ""Sched Delv Dt"", obposx as ""Posx"" " & _ " From mvxcdtprod.ooline " & _ "inner join mvxcdtprod.mitmas on mmitno = obitno " & _ "inner join mvxcdtprod.mhdisl on uurridn||digits(urridl) = oborno||'0'||digits(obponr)||digits(obposx) " & _ "inner join mvxcdtprod.mhdish on oqdlix = urdlix and oqinou in (1,3) " & _ "inner join mvxcdtprod.oohead on oaorno = oborno and oaortp in ('1CR','1CS','1CU','1DS','1EX','1NO','1NP','1PU') " & _ "inner join mvxcdtprod.ocusma on okcuno = oacuno " & _ " where obdsdt <= '" & Format(Date, "yyyymmdd") & "' " & _ "and oborno '2000052540' " & _ "and oborst between 44 and 47 " & _ "and oaoblc < '6' " & _ "and (oqrout = '611CPU' or oaortp = '1PU') " & _ " group by oqrout, oqdlix, oaortp, oborno, oacuno, okcunm, obponr, obitno, mmitds, oborst, oborqt, oaortp, oarldt, obdsdt, obposx" when i run it i get a "General OBDC error". Would appreciate any help. Thanks Julie |
WinSQL Query - Syntax for use in Excel
thanks Joel, i will give that a go.
|
All times are GMT +1. The time now is 03:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com