Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.access.externaldata,microsoft.public.access.queries,microsoft.public.data.ado,microsoft.public.excel.programming
|
|||
|
|||
SQL against AS400
I'm using ADO to pull a recordset from AS400. Everything works fine except my SQL. This SQL was generated using Microsoft query in
Excel, and it works fine, obviously, when using MSQuery. It does not work when I put it in code. The rest of my code is fine, and has been tested successfully with a different, simpler SQL. I'm posting to a couple of Access groups because more Access users are knowledgeable about SQL than Excel users. Thanks in advance for your help. mySQL = "SELECT CUST.CCMPNY, CUST.CSPCL4, CUST.CCYCLE, CUST.CCUST#, CPRD.CPDESC, CPRD.CPCHG " & _ "FROM {oj ARWF.JGFTYT.CUST CUST LEFT OUTER JOIN ARWF.JGFTYT.CPRD CPRD ON CUST.CCUST# = CPRD.CPCST#} " & _ "WHERE (CPRD.CPCHG In ('CW','DF','MC','MK','CK','WE','LI','GA','GR','CO' ,'BE','BS')) AND (SUBSTR(CSPCL4,1,2) In ('CW','DF'," & _ "'MC','MK','CK','WE','LI','GA','GR','CO','BE','BS' )) AND (CUST.CCYCLE In ('RW','RR','RC','TR','WR','SB','SC','ZB','1','2',' 10'," & _ "'11','12','13','14','15','16')) AND (CUST.CCMPNY In ('1','3','4','5','6'))" & ";" -- RMC,CPA |
#2
Posted to microsoft.public.access.externaldata,microsoft.public.access.queries,microsoft.public.data.ado,microsoft.public.excel.programming
|
|||
|
|||
SQL against AS400
"R. Choate" wrote in message
... I'm using ADO to pull a recordset from AS400. Everything works fine except my SQL. This SQL was generated using Microsoft query in Excel, and it works fine, obviously, when using MSQuery. It does not work when I put it in code. The rest of my code is fine, and has been tested successfully with a different, simpler SQL. I'm posting to a couple of Access groups because more Access users are knowledgeable about SQL than Excel users. Thanks in advance for your help. mySQL = "SELECT CUST.CCMPNY, CUST.CSPCL4, CUST.CCYCLE, CUST.CCUST#, CPRD.CPDESC, CPRD.CPCHG " & _ "FROM {oj ARWF.JGFTYT.CUST CUST LEFT OUTER JOIN ARWF.JGFTYT.CPRD CPRD ON CUST.CCUST# = CPRD.CPCST#} " & _ "WHERE (CPRD.CPCHG In ('CW','DF','MC','MK','CK','WE','LI','GA','GR','CO' ,'BE','BS')) AND (SUBSTR(CSPCL4,1,2) In ('CW','DF'," & _ "'MC','MK','CK','WE','LI','GA','GR','CO','BE','BS' )) AND (CUST.CCYCLE In ('RW','RR','RC','TR','WR','SB','SC','ZB','1','2',' 10'," & _ "'11','12','13','14','15','16')) AND (CUST.CCMPNY In ('1','3','4','5','6'))" & ";" I'm not familiar with any SQL that uses braces "{ }" so try removing those. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#3
Posted to microsoft.public.access.externaldata,microsoft.public.access.queries,microsoft.public.data.ado,microsoft.public.excel.programming
|
|||
|
|||
SQL against AS400
"R. Choate" wrote in message ... I'm using ADO to pull a recordset from AS400. Everything works fine except my SQL. This SQL was generated using Microsoft query in Excel, and it works fine, obviously, when using MSQuery. It does not work when I put it in code. The rest of my code is fine, and has been tested successfully with a different, simpler SQL. I'm posting to a couple of Access groups because more Access users are knowledgeable about SQL than Excel users. Thanks in advance for your help. mySQL = "SELECT CUST.CCMPNY, CUST.CSPCL4, CUST.CCYCLE, CUST.CCUST#, CPRD.CPDESC, CPRD.CPCHG " & _ "FROM {oj ARWF.JGFTYT.CUST CUST LEFT OUTER JOIN ARWF.JGFTYT.CPRD CPRD ON CUST.CCUST# = CPRD.CPCST#} " & _ "WHERE (CPRD.CPCHG In ('CW','DF','MC','MK','CK','WE','LI','GA','GR','CO' ,'BE','BS')) AND (SUBSTR(CSPCL4,1,2) In ('CW','DF'," & _ "'MC','MK','CK','WE','LI','GA','GR','CO','BE','BS' )) AND (CUST.CCYCLE In ('RW','RR','RC','TR','WR','SB','SC','ZB','1','2',' 10'," & _ "'11','12','13','14','15','16')) AND (CUST.CCMPNY In ('1','3','4','5','6'))" & ";" It the JOIN clause is not in a correct format, I could be wrong. Try SELECT CUST.CCMPNY, CUST.CSPCL4, CUST.CCYCLE, CUST.CCUST#, CPRD.CPDESC, CPRD.CPCHG FROM ARWF.JGFTYT.CUST LEFT OUTER JOIN ARWF.JGFTYT.CPRD ON CUST.CCUST# = CPRD.CPCST# WHERE <rest of where Do you get any descriptive errors? |
#4
Posted to microsoft.public.access.externaldata,microsoft.public.access.queries,microsoft.public.data.ado,microsoft.public.excel.programming
|
|||
|
|||
SQL against AS400
Drop the trailing semicolon.
"R. Choate" wrote in message ... I'm using ADO to pull a recordset from AS400. Everything works fine except my SQL. This SQL was generated using Microsoft query in Excel, and it works fine, obviously, when using MSQuery. It does not work when I put it in code. The rest of my code is fine, and has been tested successfully with a different, simpler SQL. I'm posting to a couple of Access groups because more Access users are knowledgeable about SQL than Excel users. Thanks in advance for your help. mySQL = "SELECT CUST.CCMPNY, CUST.CSPCL4, CUST.CCYCLE, CUST.CCUST#, CPRD.CPDESC, CPRD.CPCHG " & _ "FROM {oj ARWF.JGFTYT.CUST CUST LEFT OUTER JOIN ARWF.JGFTYT.CPRD CPRD ON CUST.CCUST# = CPRD.CPCST#} " & _ "WHERE (CPRD.CPCHG In ('CW','DF','MC','MK','CK','WE','LI','GA','GR','CO' ,'BE','BS')) AND (SUBSTR(CSPCL4,1,2) In ('CW','DF'," & _ "'MC','MK','CK','WE','LI','GA','GR','CO','BE','BS' )) AND (CUST.CCYCLE In ('RW','RR','RC','TR','WR','SB','SC','ZB','1','2',' 10'," & _ "'11','12','13','14','15','16')) AND (CUST.CCMPNY In ('1','3','4','5','6'))" & ";" |
#5
Posted to microsoft.public.access.externaldata,microsoft.public.access.queries,microsoft.public.data.ado,microsoft.public.excel.programming
|
|||
|
|||
SQL against AS400
No, I don't get any descriptive errors. It generates an error from the AS400 that just says the server generated an error, and it
gives an error number of 0036 or something like that. I've had the error before and it isn't very helpful, other than to know it means you have a messed up SQL. Thanks, -- RMC,CPA "John" <bez74 at hotmail dot com wrote in message ... "R. Choate" wrote in message ... I'm using ADO to pull a recordset from AS400. Everything works fine except my SQL. This SQL was generated using Microsoft query in Excel, and it works fine, obviously, when using MSQuery. It does not work when I put it in code. The rest of my code is fine, and has been tested successfully with a different, simpler SQL. I'm posting to a couple of Access groups because more Access users are knowledgeable about SQL than Excel users. Thanks in advance for your help. mySQL = "SELECT CUST.CCMPNY, CUST.CSPCL4, CUST.CCYCLE, CUST.CCUST#, CPRD.CPDESC, CPRD.CPCHG " & _ "FROM {oj ARWF.JGFTYT.CUST CUST LEFT OUTER JOIN ARWF.JGFTYT.CPRD CPRD ON CUST.CCUST# = CPRD.CPCST#} " & _ "WHERE (CPRD.CPCHG In ('CW','DF','MC','MK','CK','WE','LI','GA','GR','CO' ,'BE','BS')) AND (SUBSTR(CSPCL4,1,2) In ('CW','DF'," & _ "'MC','MK','CK','WE','LI','GA','GR','CO','BE','BS' )) AND (CUST.CCYCLE In ('RW','RR','RC','TR','WR','SB','SC','ZB','1','2',' 10'," & _ "'11','12','13','14','15','16')) AND (CUST.CCMPNY In ('1','3','4','5','6'))" & ";" It the JOIN clause is not in a correct format, I could be wrong. Try SELECT CUST.CCMPNY, CUST.CSPCL4, CUST.CCYCLE, CUST.CCUST#, CPRD.CPDESC, CPRD.CPCHG FROM ARWF.JGFTYT.CUST LEFT OUTER JOIN ARWF.JGFTYT.CPRD ON CUST.CCUST# = CPRD.CPCST# WHERE <rest of where Do you get any descriptive errors? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dropping decimal places whern transfering to AS400? | Excel Worksheet Functions | |||
Toolbar buttons -- AS400 iSeries Transfer | Excel Discussion (Misc queries) | |||
as400 |
Excel Discussion (Misc queries) | |||
Import accounts data from AS400 system, how do I change 10- to (1. | Excel Worksheet Functions |