Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WinSQL Query - Syntax for use in Excel
thanks Joel, i will give that a go.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VB - WHERE syntax error in Access web query. | Excel Programming | |||
Excel 2007 MS Query prompt yields Syntax error | Excel Discussion (Misc queries) | |||
MS Query import from Excel - Syntax Error | Excel Discussion (Misc queries) | |||
Syntax Error in Excel Query for DATE field | Excel Discussion (Misc queries) | |||
problem in connecting to winsql | Excel Discussion (Misc queries) |