Advertisement

07.18.2008 at 07:47AM PDT, ID: 23577015
[x]
Attachment Details

Please review this SQL string. Errors when passed to DB2 via ODBC

Asked by Harrzack in Visual Basic Programming, Microsoft Access Database, DB2 Database

Tags: Microsoft, Access & DB2, Access 2003

The enclosed SQL (created with the great help of Kdo) works nicely when connected to the AS400 via Universal SQL Editor (ODBC). The enclosed code is the line-extension version for Access 2003/VBA.

When executed as an ADO RecordSet.Open, it fails with "Token MAX was not valid..." "A syntax erroor was detected at token MAX..."  The (very meaningful) error number is -2147467259).

I've poured over this code looking for edit errors and can't find any. Could this be a thinly veiled CONNECTION issue?   My connection string (with sensitive parts x'd out) is:

Provider=IBMDASQL.DataSource.1;Persist Security Info=False;User ID=xxxx;Data Source=xxxx400

Sheeshe - it is bad enough to have to pound out the SQL - now to have to fight Access,ADO,ODBC, VB... Yeowza!!Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:

sql = "SELECT REFA,SCHDSHIP,CARR,SHPTRM,SRVLVL,ORDNUM," _
    & "ORDTYP,ORDPRI,SUM (lines) as LINES,SUM (eachqty) as EQTY," _
    & "sum (fullcases) as FULCASE,sum (eachcount) as EACT,sum (EFP) as ESTFULPLT," _
    & "GROSWT,GROVOL,ESTPAL,CustAdr1,CustAdr2,CustAdr3,CustAdr4,CustAdr5,CustAdr6," _
    & "ZIP,CustAcct,Bill2Acct,Ship2Acct,ArrInPool,CustPO,SampOrd" _
    & "FROM (" _
    & "SELECT MAX(t3.POREFA) AS REFA,MAX(t3.POSCHD) AS SCHDSHIP,MAX(t3.pocarr) AS CARR," _
    & "MAX(t3.POSTRM) AS SHPTRM,MAX(t3.POSRVL) AS SRVLVL,MAX(t3.POORDN) AS ORDNUM,MAX(t3.POORTY) AS ORDTYP," _
    & "MAX(t3.POOPRI) AS ORDPRI,DECIMAL(COUNT(t2.ipordn),7,0) AS LINES,SUM(t2.IPORDQ) AS EACHQTY," _
    & "SUM(t2.IPORDQ) / t1.ITCSPK as FULLCASES,mod(SUM(t2.IPORDQ), T1.ITCSPK) as EACHCOUNT," _
    & "DECIMAL(max(t2.IPORDQ)/(max(t1.ITCSPK)*max(t1.ITCSPL)),7,2) as EFP,MAX(t3.POGWGT) AS GROSWT," _
    & "MAX(t3.POGVOL) AS GROVOL,DECIMAL(MAX(t3.POGVOL)/61440,7,2) AS ESTPAL,MAX(t3.POCAD1) AS CustAdr1," _
    & "MAX(t3.POCAD2) AS CustAdr2,MAX(t3.POCAD3) AS CustAdr3,MAX(t3.POCAD4) AS CustAdr4,MAX(t3.POCAD5) AS CustAdr5," _
    & "MAX(t3.POCAD6) AS CustAdr6,MAX(t3.POZIPC) AS ZIP,MAX(t3.POCACT) AS CustAcct,MAX(t3.POBACT) AS Bill2Acct," _
    & "MAX(t3.POSACT) AS Ship2Acct,MAX(t3.POAPDT) AS ArrInPool,MAX(t3.POCPO) AS CustPO,MAX(t3.POSMPO) AS SampOrd " _
    & "FROM hbcprodfl.witmb as t1 " _
    & "JOIN hbcprodfl.wopib AS t2 on T2.IPSKU = T1.ITSKU " _
    & "JOIN HBCPRODFL.WOPHB AS t3 ON t2.IPORDN = t3.POORDN WHERE t1.ITCSPK > 0 " _
    & "GROUP BY t2.IPORDN,t2.IPORDQ,t1.ITCSPK " _
    & ") t0 " _
    & "GROUP BY ORDNUM,REFA,SCHDSHIP,CARR,SHPTRM,SRVLVL,ORDTYP,ORDPRI,GROSWT,GROVOL,ESTPAL," _
    & "CustAdr1,CustAdr2,CustAdr3,CustAdr4,CustAdr5,CustAdr6,ZIP," _
    & "CustAcct,Bill2Acct,Ship2Acct,ArrInPool,CustPO,SampOrd ORDER BY ORDNUM"
[+][-]07.18.2008 at 08:05AM PDT, ID: 22036572

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.18.2008 at 08:13AM PDT, ID: 22036673

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.18.2008 at 09:28AM PDT, ID: 22037498

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.18.2008 at 09:38AM PDT, ID: 22037600

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.19.2008 at 10:48AM PDT, ID: 22043193

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.19.2008 at 11:00AM PDT, ID: 22043222

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.19.2008 at 11:51AM PDT, ID: 22043348

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.19.2008 at 11:56AM PDT, ID: 22043360

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.19.2008 at 08:54PM PDT, ID: 22044535

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Visual Basic Programming, Microsoft Access Database, DB2 Database
Tags: Microsoft, Access & DB2, Access 2003
Sign Up Now!
Solution Provided By: aikimark
Participating Experts: 3
Solution Grade: A
 
 
[+][-]07.20.2008 at 03:30AM PDT, ID: 22045182

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628