× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: Re: ODBC peculiarity
  • From: "Rob Ward" <rward@xxxxxxxxx>
  • Date: Wed, 24 Feb 1999 14:25:36 -0600

First thing to do is get the latest CA service pack and apply it.  The
problem could be with the ODBC driver in Client Access.  You may also want
to get the latest CUME PTF for DB2/400.

Rob Ward
Director of I.S.
M-C Industries, Inc.
Sunflower Marketing/Polo Plastics

-----Original Message-----
From: Buck Calabro/commsoft <mcalabro@commsoft.net>
To: midrange-l@midrange.com <midrange-l@midrange.com>
Date: Wednesday, February 24, 1999 2:06 PM
Subject: ODBC peculiarity


>I'm relaying this question from our client-server group.  When the
>PC-centric folks come up with an oddity, they tend to ask me "why does the
>AS/400 do that?"  This one has me stumped...
>
>If we run this SQL statement on an interactive SQL display, it runs fast
>and returns accurate results.
>
>SELECT NHVNXT PAY_DESC, SUM(LOMBNB) TOT_AMT,
>  SUM(LODLVE) CURR_AMT, SUM(LOMCNB) PAST30_AMT,
>  SUM(LOMDNB) PAST60_AMT, SUM(LOMENB) PAST90_AMT,
>  SUM(LOM7NR) PAST120_AMT, SUM(LOM8NR) PAST150_AMT,
>  SUM(LOM9NR) PAST180_AMT
>FROM ACTPHY01 a, ASBPHY01, PCOPHY01
>WHERE B1LKNB = 16808
>AND B1HXCD = '  1'
>AND B1LWNB = LOLWNB
>AND LONUCG = NHNUCG
>AND LOADNS = (SELECT MAX(LOADNS) FROM ASBPHY01
>              WHERE LOLWNB = a.B1LWNB)
>group by NHVNXT
>
>When using the IBM Client Access 95 ODBC driver over TCP/IP, this statement
>does a strange thing.
>It runs properly the first time it executes.
>It runs properly the second time it executes.
>The third and subsequent executions return incorrect amounts for the
>SUM()s; rather than return the SUM(), they return the SUM() plus the value
>of the column in the first pass.
>
>Here are the results for TOT_AMT, but all the other columns work the same
>way...
>Pass 1   62.54
>Pass 2   62.54
>Pass 3  125.08
>Pass 4  187.62 and so on.
>
>If we remove the subselect and hard-wire a number it works properly every
>time:
>
>SELECT NHVNXT PAY_DESC, SUM(LOMBNB) TOT_AMT,
>  SUM(LODLVE) CURR_AMT, SUM(LOMCNB) PAST30_AMT,
>  SUM(LOMDNB) PAST60_AMT, SUM(LOMENB) PAST90_AMT,
>  SUM(LOM7NR) PAST120_AMT, SUM(LOM8NR) PAST150_AMT,
>  SUM(LOM9NR) PAST180_AMT
>FROM ACTPHY01 a, ASBPHY01, PCOPHY01
>WHERE B1LKNB = 16808
>AND B1HXCD = '  1'
>AND B1LWNB = LOLWNB
>AND LONUCG = NHNUCG
>AND LOADNS = 2
>group by NHVNXT
>
>We are working around this by avoiding the subselect, but this can't be
>working as designed.
>
>Looking at the QZDASOINIT job, I can see that there are open files in
>QTEMP:
>*SUBQUERY  *SUBQUERY  *SUBQUERY  FORMAT0002 PHY      19  I   NO
>ACTPHY01   COMDEV200  ACTPHY01   FORMAT0001 PHY      38  I   NO 17081
>*QUERY0003 QTEMP      *QUERY0003 *QUERY0001 PHY      76  O   NO 12
>
>It almost appears that the DB manager on the 400 is caching the results for
>me (some sort of performance enhancement?)
>
>Has anyone seen this before?  This is happening on all the PC's here.  My
>machine is Win95 OSR2, CA V3R2 Service level None
>It is not limited to these particular DB files, either.  Any subselect
>causes SUM() to do this.
>
>Ideas?
>
>+---
>| This is the Midrange System Mailing List!
>| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
>| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
>| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
>| Questions should be directed to the list owner/operator:
david@midrange.com
>+---
>

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.