WinSQL Query - Syntax for use in Excel

[julied]

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

 

[Joel]

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

 

[RB Smissaert]

For starters I would try without the double-quoted aliases, so oqrout as
Route etc.

RBS

 

[julied]

thanks Joel, i will give that a go.