By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,050 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

nested select question

P: n/a
Tcs
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too long, as there's a limit to the length of the SQL
statement(s). But this works when I don't try to do it from VB.) I THINK my
problem is just a syntax problem, seeing as this is my first experience with
running pass thru queries from VB code. So here goes...

I guess the first question to ask is - Can a pass-thru query be a nested query
as applies to VB code? (When I run my two queries manually, I get the results I
want...)

I have a local table into which I need to append data I retreive from our
AS/400. I initially developed both the local and pass thru queries which work
just fine together...with hardcoded parameters, and run manually (or even from
VB, just not "built" in VB).

The local query code (which Access created):

INSERT INTO tblAcctsRecAging_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBilled,
CurrentUnPaid )

SELECT
qryAcctsRecAging_0010_Current_420_BE.UTCSID,
qryAcctsRecAging_0010_Current_420_BE.UTLCID,
qryAcctsRecAging_0010_Current_420_BE.UTRCLS,
qryAcctsRecAging_0010_Current_420_BE.UTSVC,
qryAcctsRecAging_0010_Current_420_BE.UTPEYY,
qryAcctsRecAging_0010_Current_420_BE.UTPEMM,
qryAcctsRecAging_0010_Current_420_BE.UTAGE,
qryAcctsRecAging_0010_Current_420_BE.UTTTYP,
qryAcctsRecAging_0010_Current_420_BE.UTTDSC,
qryAcctsRecAging_0010_Current_420_BE.UTTAMT,
qryAcctsRecAging_0010_Current_420_BE.UTUNPD

FROM
tblAcctsRecAging_Details RIGHT JOIN qryAcctsRecAging_0010_Current_420_BE ON
tblAcctsRecAging_Details.LocID = qryAcctsRecAging_0010_Current_420_BE.UTLCID;

And the pass thru query (which I created):

SELECT distinct
CXLIB.UT420AP.UTCSID,
CXLIB.UT420AP.UTLCID,
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC,
CXLIB.UT420AP.UTTAMT,
CXLIB.UT420AP.UTUNPD

FROM
CXLIB.UT420AP

WHERE
((CXLIB.UT420AP.UTAGE='C') AND
(((CXLIB.UT420AP.UTPEMM)=7) AND ((CXLIB.UT420AP.UTPEYY)=4)) Or
(((CXLIB.UT420AP.UTPEMM)=8) AND ((CXLIB.UT420AP.UTPEYY)=4)))

ORDER BY
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC;

The problem is that my parameters are variable. So I created this:

' compose SQL string 1 for the back end (BE) - DB2
'
strSQLselect1 = _
"SELECT distinct " & _
"CXLIB.UT420AP.UTCSID, " & _
"CXLIB.UT420AP.UTLCID, " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTAGE, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC, " & _
"CXLIB.UT420AP.UTTAMT, " & _
"CXLIB.UT420AP.UTUNPD "

strSQLfrom1 = _
"FROM CXLIB.UT420AP "

strSQLwhere1 = _
"WHERE " & _
"((CXLIB.UT420AP.UTAGE='" & strAgeGroup & "') AND " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int1stMM & ") AND ((CXLIB.UT420AP.UTPEYY)="
& int1stYY & ")) Or " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int2ndMM & ") AND ((CXLIB.UT420AP.UTPEYY)="
& int2ndYY & "))) "

strSQLorderby1 = _
"ORDER BY " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC; "

strSQLstatement1 = _
strSQLselect1 & _
strSQLfrom1 & _
strSQLwhere1 & _
strSQLorderby1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBilled, " & _
"CurrentUnPaid ) "

strSQLselect2 = _
"SELECT (" & _
strSQLstatement1 & ".UTCSID, " & _
strSQLstatement1 & ".UTLCID, " & _
strSQLstatement1 & ".UTRCLS, " & _
strSQLstatement1 & ".UTSVC, " & _
strSQLstatement1 & ".UTPEYY, " & _
strSQLstatement1 & ".UTPEMM, " & _
strSQLstatement1 & ".UTAGE, " & _
strSQLstatement1 & ".UTTTYP, " & _
strSQLstatement1 & ".UTTDSC, " & _
strSQLstatement1 & ".UTTAMT, " & _
strSQLstatement1 & ".UTUNPD "

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAging_Details RIGHT JOIN " & strSQLstatement1 & " ON " & _
"tblAcctsRecAging_Details.LocID = " & strSQLstatement1 & ".UTLCID; )"

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2

So NOW what's my problem? Access doesn't like this. I'm getting an error msg:

Error # 3075 was generated by MSAcess
Syntax error. in the query expression '(SELECT distinct SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC, CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTAGE, CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC,
CXLIB.UT420AP.UTTAMT, CXLIB.UT420AP.UTUNPD'.

(Remember, I was using Access's code as an example...the code that WORKED.) I
didn't understand what it didn't like, so...

I changed the above to this:

strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
strWhichAmtBilled & "AmtBilled, " & _
strWhichAmtUnpaid & "AmtUnpaid ) "

strSQLselect2 = _
"SELECT " & _
"UTCSID, " & _
"UTLCID, " & _
"UTRCLS, " & _
"UTSVC, " & _
"UTPEYY, " & _
"UTPEMM, " & _
"UTAGE, " & _
"UTTTYP, " & _
"UTTDSC, " & _
"UTTAMT, " & _
"UTUNPD "

strSQLfrom2 = _
"FROM CXLIB.UT420AP "

strSQLwhere2 = _
"WHERE " & _
"tblAcctsRecAging_Details RIGHT JOIN in (" & strSQLstatement1 & ") ON "
& _
"tblAcctsRecAging_Details.LocID = CXLIB.UT420AP.UTLCID; "

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2 & _
strSQLwhere2

I've inpected the SQL statements after they're built, and they appear to be
correct. (Well, COMPLETE, anyway. CORRECT I guess is why I'm here asking for
help.)

The error I'm getting is:

Error # 3075 was generated by MSAccess
Syntax error (missing operator) in query expression 'tblAcctsRecAging_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXL'.

I took out the "in" after "RIGHT JOIN". I'm still getting the same error:

Error # 3075 was generated by MSAccess
Syntax error (missing operator) in query expression 'tblAcctsRecAging_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXLIB.'.

Since I'm still getting an error, I'm obviously still missing something with
regards to my nested query. Can/will anyone hazard a guess?

Access = 2000
OS = XP

Thanks a lot, in advance... Any help is appreciated...

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Let me begin by saying that my familiarity with DB2 on AS/400 is *very*
minimal - a few days several years ago - so I'm not speaking as an expert on
DB2 for AS/400! However, this appears to be mainly an issue of SQL, which I
know reasonably well, and you welcomed *all* replies so I'm going to jump in
and risk making an idiot of myself ;-)

The rest of my remarks are interspersed below.

Rhino

"Tcs" <ts****@eastpointcity.org> wrote in message
news:nc********************************@4ax.com...
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know, or that my query names are too long, as there's a limit to the length of the SQL statement(s).
Do you mean that the Access group literally said that they didn't like the
periods in your SQL statements or are you using '.' as a placeholder, like I
might say "such-and-such"? If you are using the period as a placeholder,
what were the specific things they told you about your syntax?

I'm not sure why you are dismissing the remarks people on the Access group
made. If Access really does have specific limits on the length of the
statements or your query names, those would appear to be genuine things that
you need to take into account, unless you plan to abandon Access from your
approach.
But this works when I don't try to do it from VB.)
*Which* things work when you don't do them from VB? Do you mean that all of
your queries work okay when not done through VB or just particular ones?
Which ones?

When you try them without using VB, just exactly how are you trying them? In
other words, what are you using instead of VB?
I THINK my
problem is just a syntax problem, seeing as this is my first experience with running pass thru queries from VB code. So here goes...
I guess the first question to ask is - Can a pass-thru query be a nested query as applies to VB code? (When I run my two queries manually, I get the results I want...)
I don't know VB (Visual Basic) at all and I'm not sure what you mean by a
pass-thru query. I'm just telling you that in case some of my later remarks
seem especially clueless.
I have a local table into which I need to append data I retreive from our
AS/400. I initially developed both the local and pass thru queries which work just fine together...with hardcoded parameters, and run manually (or even from VB, just not "built" in VB).
What kind of machine is your local table sitting on? A PC? Another AS/400?
The local query code (which Access created):

INSERT INTO tblAcctsRecAging_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBilled,
CurrentUnPaid )

SELECT
qryAcctsRecAging_0010_Current_420_BE.UTCSID,
qryAcctsRecAging_0010_Current_420_BE.UTLCID,
qryAcctsRecAging_0010_Current_420_BE.UTRCLS,
qryAcctsRecAging_0010_Current_420_BE.UTSVC,
qryAcctsRecAging_0010_Current_420_BE.UTPEYY,
qryAcctsRecAging_0010_Current_420_BE.UTPEMM,
qryAcctsRecAging_0010_Current_420_BE.UTAGE,
qryAcctsRecAging_0010_Current_420_BE.UTTTYP,
qryAcctsRecAging_0010_Current_420_BE.UTTDSC,
qryAcctsRecAging_0010_Current_420_BE.UTTAMT,
qryAcctsRecAging_0010_Current_420_BE.UTUNPD

FROM
tblAcctsRecAging_Details RIGHT JOIN qryAcctsRecAging_0010_Current_420_BE ON tblAcctsRecAging_Details.LocID = qryAcctsRecAging_0010_Current_420_BE.UTLCID; Nothing jumps out at me in the above query.
And the pass thru query (which I created):

SELECT distinct
CXLIB.UT420AP.UTCSID,
CXLIB.UT420AP.UTLCID,
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC,
CXLIB.UT420AP.UTTAMT,
CXLIB.UT420AP.UTUNPD

FROM
CXLIB.UT420AP

WHERE
((CXLIB.UT420AP.UTAGE='C') AND
(((CXLIB.UT420AP.UTPEMM)=7) AND ((CXLIB.UT420AP.UTPEYY)=4)) Or
(((CXLIB.UT420AP.UTPEMM)=8) AND ((CXLIB.UT420AP.UTPEYY)=4)))

ORDER BY
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC;
Are you sure that the above query is what you really want to do? The keyword
DISTINCT applies to all of the column names that follow it in the SELECT
clause. In effect, you are saying that you want all of the distinct
*combinations* of ALL of those columns: UTCSID, UTLCID, UTRCLS, UTSVC,
UTPEYY, UTPEMM, UTAGE, UTTTYP, UTTDSC, UTTAMT, UTUNPD. I usually see
DISTINCT applied to only 1 or 2 or maybe 3 columns but this is 11 columns.

I don't know if you're a beginner or very experienced but I find that most
beginners with SQL think that DISTINCT only applies to the column name
immediately following the DISTINCT keyword and that all of the other columns
in their SELECT will not be affected by the SELECT. That is simply not true.
The problem is that my parameters are variable. So I created this:

' compose SQL string 1 for the back end (BE) - DB2
'
strSQLselect1 = _
"SELECT distinct " & _
"CXLIB.UT420AP.UTCSID, " & _
"CXLIB.UT420AP.UTLCID, " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTAGE, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC, " & _
"CXLIB.UT420AP.UTTAMT, " & _
"CXLIB.UT420AP.UTUNPD "

strSQLfrom1 = _
"FROM CXLIB.UT420AP "

strSQLwhere1 = _
"WHERE " & _
"((CXLIB.UT420AP.UTAGE='" & strAgeGroup & "') AND " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int1stMM & ") AND ((CXLIB.UT420AP.UTPEYY)=" & int1stYY & ")) Or " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int2ndMM & ") AND ((CXLIB.UT420AP.UTPEYY)=" & int2ndYY & "))) "

strSQLorderby1 = _
"ORDER BY " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC; "

strSQLstatement1 = _
strSQLselect1 & _
strSQLfrom1 & _
strSQLwhere1 & _
strSQLorderby1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBilled, " & _
"CurrentUnPaid ) "

strSQLselect2 = _
"SELECT (" & _
strSQLstatement1 & ".UTCSID, " & _
strSQLstatement1 & ".UTLCID, " & _
strSQLstatement1 & ".UTRCLS, " & _
strSQLstatement1 & ".UTSVC, " & _
strSQLstatement1 & ".UTPEYY, " & _
strSQLstatement1 & ".UTPEMM, " & _
strSQLstatement1 & ".UTAGE, " & _
strSQLstatement1 & ".UTTTYP, " & _
strSQLstatement1 & ".UTTDSC, " & _
strSQLstatement1 & ".UTTAMT, " & _
strSQLstatement1 & ".UTUNPD "

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAging_Details RIGHT JOIN " & strSQLstatement1 & " ON " & _ "tblAcctsRecAging_Details.LocID = " & strSQLstatement1 & ".UTLCID; )"
strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2
The syntax for the above queries looks *very* odd to me. I'm especially
perplexed by the "& _" tokens that are at the end of most - but not *all* -
of the lines. What do the ampersands and underscores mean in these queries?
At first glance, I thought they might be continuation characters but that
doesn't make sense: why do some lines have both ampersand and underscore,
some have only the underscore, and some have neither?

Did you really have to write the statements like this just to get some
variables into the join? It's a lot easier in most languages. Again, I don't
know VB or DB2 on AS/400 so maybe this is a standard approach to statements
in that environment....

So NOW what's my problem? Access doesn't like this. I'm getting an error msg:
Error # 3075 was generated by MSAcess
Syntax error. in the query expression '(SELECT distinct SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC, CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTAGE, CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC,
CXLIB.UT420AP.UTTAMT, CXLIB.UT420AP.UTUNPD'.
Have you copied this correctly? I can certainly understand that it wouldn't
like "SELECT distinct" *twice* at the beginning of the query; the keyword
"DISTINCT" can only be in each SELECT clause *ONCE*. And, of course, the
keyword "SELECT" can only appear once in that context too.

Also, are you saying that the error message is for strSQLstatement1,
strSQLstatement2, or both? I'm really not following the way that you are
generating and executing these statements so forgive my confusion.
(Remember, I was using Access's code as an example...the code that WORKED.) I didn't understand what it didn't like, so...

I changed the above to this:

strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
strWhichAmtBilled & "AmtBilled, " & _
strWhichAmtUnpaid & "AmtUnpaid ) "

strSQLselect2 = _
"SELECT " & _
"UTCSID, " & _
"UTLCID, " & _
"UTRCLS, " & _
"UTSVC, " & _
"UTPEYY, " & _
"UTPEMM, " & _
"UTAGE, " & _
"UTTTYP, " & _
"UTTDSC, " & _
"UTTAMT, " & _
"UTUNPD "

strSQLfrom2 = _
"FROM CXLIB.UT420AP "

strSQLwhere2 = _
"WHERE " & _
"tblAcctsRecAging_Details RIGHT JOIN in (" & strSQLstatement1 & ") ON " & _
"tblAcctsRecAging_Details.LocID = CXLIB.UT420AP.UTLCID; "
Your join definitely looks wrong to me. It needs to be something more like:

FROM EMPLOYEE e RIGHT JOIN DEPARTMENT D on e.workdept in ('D11', 'E21')
AND e.workdept = d.deptno

[Personally, I only put join conditions in the FROM clause and put local
predicates like 'e.workdept in ('D11', 'E21')' in my WHERE clause; I find
that easier to read. However, I'm not saying that it is *wrong* to put local
predicates in your FROM clause.]
strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2 & _
strSQLwhere2

I've inpected the SQL statements after they're built, and they appear to be correct. (Well, COMPLETE, anyway. CORRECT I guess is why I'm here asking for help.)

The error I'm getting is:

Error # 3075 was generated by MSAccess
Syntax error (missing operator) in query expression 'tblAcctsRecAging_Details RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXL'.

I took out the "in" after "RIGHT JOIN".
See my remarks about your join, above.

Putting a SELECT statement within an IN clause is not remotely valid syntax
so I can see why Access doesn't like that!
I'm still getting the same error:

Error # 3075 was generated by MSAccess
Syntax error (missing operator) in query expression 'tblAcctsRecAging_Details RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXLIB.'.

Since I'm still getting an error, I'm obviously still missing something with regards to my nested query. Can/will anyone hazard a guess?
Again, I'm still a little confused about which precise statement each of
these error message refers to. I also don't understand why you've written
the statements the way you have with all of this concatenation of strings.
But, again, maybe this is the only way to do these things in your
environment so I'll assume you've done it in the only way possible.
Personally, if I had to write this program, I'd do it using Java.
PreparedStatement objects would do the job quite nicely and a lot more
simply; but that's just me ;-)
Access = 2000
OS = XP

Thanks a lot, in advance... Any help is appreciated...

I probably haven't been much help but maybe something I've said will ring a
bell and help you see what's wrong. Or, more likely, someone with more
AS/400 and VB experience will see what you're doing wrong and set you
straight.

Good luck!

Rhino
Nov 12 '05 #2

P: n/a
Tcs
Sorry for the delay in my reply. I've been out the past several days. (Back
problems.)

On Thu, 19 Aug 2004 16:26:03 -0400, "Rhino" <rh****@NOSPAM.sympatico.ca> wrote:
Let me begin by saying that my familiarity with DB2 on AS/400 is *very*
minimal - a few days several years ago - so I'm not speaking as an expert on
DB2 for AS/400! However, this appears to be mainly an issue of SQL, which I
know reasonably well, and you welcomed *all* replies so I'm going to jump in
and risk making an idiot of myself ;-)
ABSOLUTELY! GREAT! THANK YOU!

The rest of my remarks are interspersed below.

Rhino

"Tcs" <ts****@eastpointcity.org> wrote in message
news:nc********************************@4ax.com.. .
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, Ihaven't
loaded it yet. I'm still using MS Access. And no, I don't believe this

is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I

know,
or that my query names are too long, as there's a limit to the length of

the SQL
statement(s).


Do you mean that the Access group literally said that they didn't like the
periods in your SQL statements or are you using '.' as a placeholder, like I
might say "such-and-such"? If you are using the period as a placeholder,
what were the specific things they told you about your syntax?


Yes, they said that Access doesn't like '.'. And I know this. When I link a
DB2 table to Access it replaces the "." with '_'. So CXLIB.UT420AP becomes
CXLIB_UT420AP. But for a "PASS-THRU" query, which Access does NOT process, this
shouldn't make any difference. Besides, DB2 doesn't want '_'. Instead, IT
wants '.'.

I'm not sure why you are dismissing the remarks people on the Access group
made. If Access really does have specific limits on the length of the
statements or your query names, those would appear to be genuine things that
you need to take into account, unless you plan to abandon Access from your
approach.
I don't mean to dismiss them out of hand, but I don't see how they apply, in
this case, at least. (Read next portion.) I'll confess that my names ten to be
long. And in the right circumstance, too long. But so far...Access hasn't
barfed on my names being too long.
But this works when I don't try to do it from VB.)
*Which* things work when you don't do them from VB? Do you mean that all of
your queries work okay when not done through VB or just particular ones?
Which ones?


My two queries. My "PASS-THRU" (backend) query, and my local (frontend) query.
I created both with Access's Query Builder grid. I can run the pass-thru query
interactively, and it returns the data I want. IT WORKS.

(Note: "Pass-thru" query to Access means a query native to the backend which
will NOT be processed by Access. (Access has its syntax and the BE has its
syntax.) It will be sent to the BE, and the returned data used.)

My "Local" (frontend) query. When I run it interactively, it uses the pass-thru
query as input, and goes and retrieves the data, just like it should. IT WORKS.

In VB, I'm building the SQL statements for each. Then I run an Access command
"DoCmd.RunSQL". This takes my SQL statement(s) and runs them. THIS is what
appears to be NOT working. But I think it's my syntax. (I know it may NOT be.)
I haven't dealt with nested queries before. But what I'm asking Access to do
isn't anything that it CAN'T do. IT'S ALREADY done it. Just not thru VB.

And NO ONE...seems to want to comment on my SQL statement. Of the couple
responses I've received, it's been the "Access doesn't like '>', or the "Your
names are too long". And this name, isn't even USED by Access when I try to run
it thru VB.

Look at the SQL statement below. Notice the
"qryAcctsRecAging_0010_Current_420_BE" query name. Too long? Perhaps. BUT IT
DOES WORK. (The "BE" on the end means that this is my pass-sthru, or backend,
query.) This is what Access has built. APPARENTLY it does more processing with
this, as this is NOT a usable statement, in this form. So I've tried to modify
it as I BELIEVE it needs to be modified. And I'm getting an error.

If someone wants to say that "CXLIB.UT420AP.UTCSID" is too long, well then, I
guess I'm SOL. DB2 won't understand anything less. This is the file, table,
and field that I want. On the other hand, if this is too long, then why does it
work when i don't try thru VB?

When you try them without using VB, just exactly how are you trying them? In
other words, what are you using instead of VB?
I THINK my
problem is just a syntax problem, seeing as this is my first experiencewith
running pass thru queries from VB code. So here goes...

I guess the first question to ask is - Can a pass-thru query be a nested

query
as applies to VB code? (When I run my two queries manually, I get the

results I
want...)

I don't know VB (Visual Basic) at all and I'm not sure what you mean by a
pass-thru query. I'm just telling you that in case some of my later remarks
seem especially clueless.


Not a problem. (See above.) I understand.
I have a local table into which I need to append data I retreive from our
AS/400. I initially developed both the local and pass thru queries whichwork
just fine together...with hardcoded parameters, and run manually (or even

from
VB, just not "built" in VB).

What kind of machine is your local table sitting on? A PC? Another AS/400?


2.6GHz P4, OS = XP, RAM = 1gb.
The local query code (which Access created):

INSERT INTO tblAcctsRecAging_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBilled,
CurrentUnPaid )

SELECT
qryAcctsRecAging_0010_Current_420_BE.UTCSID,
qryAcctsRecAging_0010_Current_420_BE.UTLCID,
qryAcctsRecAging_0010_Current_420_BE.UTRCLS,
qryAcctsRecAging_0010_Current_420_BE.UTSVC,
qryAcctsRecAging_0010_Current_420_BE.UTPEYY,
qryAcctsRecAging_0010_Current_420_BE.UTPEMM,
qryAcctsRecAging_0010_Current_420_BE.UTAGE,
qryAcctsRecAging_0010_Current_420_BE.UTTTYP,
qryAcctsRecAging_0010_Current_420_BE.UTTDSC,
qryAcctsRecAging_0010_Current_420_BE.UTTAMT,
qryAcctsRecAging_0010_Current_420_BE.UTUNPD

FROM
tblAcctsRecAging_Details RIGHT JOIN qryAcctsRecAging_0010_Current_420_BEON
tblAcctsRecAging_Details.LocID =

qryAcctsRecAging_0010_Current_420_BE.UTLCID;

Nothing jumps out at me in the above query.
And the pass thru query (which I created):

SELECT distinct
CXLIB.UT420AP.UTCSID,
CXLIB.UT420AP.UTLCID,
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC,
CXLIB.UT420AP.UTTAMT,
CXLIB.UT420AP.UTUNPD

FROM
CXLIB.UT420AP

WHERE
((CXLIB.UT420AP.UTAGE='C') AND
(((CXLIB.UT420AP.UTPEMM)=7) AND ((CXLIB.UT420AP.UTPEYY)=4)) Or
(((CXLIB.UT420AP.UTPEMM)=8) AND ((CXLIB.UT420AP.UTPEYY)=4)))

ORDER BY
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC;

Are you sure that the above query is what you really want to do? The keyword
DISTINCT applies to all of the column names that follow it in the SELECT
clause. In effect, you are saying that you want all of the distinct
*combinations* of ALL of those columns: UTCSID, UTLCID, UTRCLS, UTSVC,
UTPEYY, UTPEMM, UTAGE, UTTTYP, UTTDSC, UTTAMT, UTUNPD. I usually see
DISTINCT applied to only 1 or 2 or maybe 3 columns but this is 11 columns.


Until you asked...I THOUGHT so. Without 'distinct' I get erroneous errors,
duplications. Access has no provision for individual 'distinct's, or at least
none that I'm aware of. I just normally add it after my select statement. When
I've done so, my errors disappear.

Access does not generate a SQL statement with the word 'distinct'. I've only
added it after I see the returned data, and see that it's not correct.

I don't know if you're a beginner or very experienced but I find that most
beginners with SQL think that DISTINCT only applies to the column name
immediately following the DISTINCT keyword and that all of the other columns
in their SELECT will not be affected by the SELECT. That is simply not true.
Beginner? Guilty.

Actually, I don't ever recall thinking of it as applying to ANY particular
column.
The problem is that my parameters are variable. So I created this:

' compose SQL string 1 for the back end (BE) - DB2
'
strSQLselect1 = _
"SELECT distinct " & _
"CXLIB.UT420AP.UTCSID, " & _
"CXLIB.UT420AP.UTLCID, " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTAGE, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC, " & _
"CXLIB.UT420AP.UTTAMT, " & _
"CXLIB.UT420AP.UTUNPD "

strSQLfrom1 = _
"FROM CXLIB.UT420AP "

strSQLwhere1 = _
"WHERE " & _
"((CXLIB.UT420AP.UTAGE='" & strAgeGroup & "') AND " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int1stMM & ") AND((CXLIB.UT420AP.UTPEYY)="
& int1stYY & ")) Or " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int2ndMM & ") AND

((CXLIB.UT420AP.UTPEYY)="
& int2ndYY & "))) "

strSQLorderby1 = _
"ORDER BY " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC; "

strSQLstatement1 = _
strSQLselect1 & _
strSQLfrom1 & _
strSQLwhere1 & _
strSQLorderby1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBilled, " & _
"CurrentUnPaid ) "

strSQLselect2 = _
"SELECT (" & _
strSQLstatement1 & ".UTCSID, " & _
strSQLstatement1 & ".UTLCID, " & _
strSQLstatement1 & ".UTRCLS, " & _
strSQLstatement1 & ".UTSVC, " & _
strSQLstatement1 & ".UTPEYY, " & _
strSQLstatement1 & ".UTPEMM, " & _
strSQLstatement1 & ".UTAGE, " & _
strSQLstatement1 & ".UTTTYP, " & _
strSQLstatement1 & ".UTTDSC, " & _
strSQLstatement1 & ".UTTAMT, " & _
strSQLstatement1 & ".UTUNPD "

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAging_Details RIGHT JOIN " & strSQLstatement1 & " ON "

& _
"tblAcctsRecAging_Details.LocID = " & strSQLstatement1 &

".UTLCID; )"

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2

The syntax for the above queries looks *very* odd to me.


I was just following Access's lead.
I'm especially
perplexed by the "& _" tokens that are at the end of most - but not *all* -
of the lines. What do the ampersands and underscores mean in these queries?
Ampersand is contenation character. "Horse " & "Hockey" equals "Horse Hockey".
The underscore is a line conituation character.
At first glance, I thought they might be continuation characters but that
doesn't make sense: why do some lines have both ampersand and underscore,
some have only the underscore, and some have neither?
Because I'm building the statement IN PIECES. The "INSERT INTO " is separate.
The "SELECT" is separate. The "FROM is separate. Then I pull them all together
at the end, with:

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2
Did you really have to write the statements like this just to get some
variables into the join? It's a lot easier in most languages. Again, I don't
know VB or DB2 on AS/400 so maybe this is a standard approach to statements
in that environment....
I have formatted the statements the way I did for legibility. It's easier for
me to read and understand. Also, using separate pieces to make one whole
statment is necessary as VB has a '_' character limit of 25 per occurrence. (So
I had to split them up.)

So NOW what's my problem? Access doesn't like this. I'm getting an errormsg:

Error # 3075 was generated by MSAcess
Syntax error. in the query expression '(SELECT distinct SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC, CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTAGE, CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC,
CXLIB.UT420AP.UTTAMT, CXLIB.UT420AP.UTUNPD'.

Have you copied this correctly? I can certainly understand that it wouldn't
like "SELECT distinct" *twice* at the beginning of the query; the keyword
"DISTINCT" can only be in each SELECT clause *ONCE*. And, of course, the
keyword "SELECT" can only appear once in that context too.

Also, are you saying that the error message is for strSQLstatement1,
strSQLstatement2, or both? I'm really not following the way that you are
generating and executing these statements so forgive my confusion.
(Remember, I was using Access's code as an example...the code that

WORKED.) I
didn't understand what it didn't like, so...

I changed the above to this:

strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
strWhichAmtBilled & "AmtBilled, " & _
strWhichAmtUnpaid & "AmtUnpaid ) "

strSQLselect2 = _
"SELECT " & _
"UTCSID, " & _
"UTLCID, " & _
"UTRCLS, " & _
"UTSVC, " & _
"UTPEYY, " & _
"UTPEMM, " & _
"UTAGE, " & _
"UTTTYP, " & _
"UTTDSC, " & _
"UTTAMT, " & _
"UTUNPD "

strSQLfrom2 = _
"FROM CXLIB.UT420AP "

strSQLwhere2 = _
"WHERE " & _
"tblAcctsRecAging_Details RIGHT JOIN in (" & strSQLstatement1 & ")

ON "
& _
"tblAcctsRecAging_Details.LocID = CXLIB.UT420AP.UTLCID; "

Your join definitely looks wrong to me. It needs to be something more like:

FROM EMPLOYEE e RIGHT JOIN DEPARTMENT D on e.workdept in ('D11', 'E21')
AND e.workdept = d.deptno

[Personally, I only put join conditions in the FROM clause and put local
predicates like 'e.workdept in ('D11', 'E21')' in my WHERE clause; I find
that easier to read. However, I'm not saying that it is *wrong* to put local
predicates in your FROM clause.]

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2 & _
strSQLwhere2

I've inpected the SQL statements after they're built, and they appear tobe
correct. (Well, COMPLETE, anyway. CORRECT I guess is why I'm here asking

for
help.)

The error I'm getting is:

Error # 3075 was generated by MSAccess
Syntax error (missing operator) in query expression

'tblAcctsRecAging_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXL'.

I took out the "in" after "RIGHT JOIN".


See my remarks about your join, above.

Putting a SELECT statement within an IN clause is not remotely valid syntax
so I can see why Access doesn't like that!


But I've seen other messages in the Access group that appear the same when
they're talking about nested queries. That's why I did, what I did. Perhaps
it's a 'backend' vs 'frontend' thing.
I'm still getting the same error:

Error # 3075 was generated by MSAccess
Syntax error (missing operator) in query expression'tblAcctsRecAging_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXLIB.'.

Since I'm still getting an error, I'm obviously still missing something

with
regards to my nested query. Can/will anyone hazard a guess?

Again, I'm still a little confused about which precise statement each of
these error message refers to. I also don't understand why you've written
the statements the way you have with all of this concatenation of strings.
But, again, maybe this is the only way to do these things in your
environment so I'll assume you've done it in the only way possible.
Personally, if I had to write this program, I'd do it using Java.
PreparedStatement objects would do the job quite nicely and a lot more
simply; but that's just me ;-)


I don't know Java.
Access = 2000
OS = XP

Thanks a lot, in advance... Any help is appreciated...

I probably haven't been much help but maybe something I've said will ring a
bell and help you see what's wrong. Or, more likely, someone with more
AS/400 and VB experience will see what you're doing wrong and set you
straight.

Good luck!

Rhino


Thank you!!!

Nov 12 '05 #3

P: n/a
Sorry Tcs but I don't have anything significant to add that would help you.
Even with your explanations, I don't have any more to contribute.

I have only a few days AS/400 experience and that was several years and
versions of DB2 ago. I have done a few hours of work with Access but that
was over two years ago. I don't have access to either so there is no way I
can figure out what is going wrong for you.

Here are some options that might help:

1. Others may see this post and offer to help. I'm not optimistic about that
happening; I was the only one that tried helping with your original post in
several days.

2. The manuals for the products that you are using may help. Perhaps some
examples in those manuals or in the Help screens will help you see what you
are doing wrong. You might try doing some very very simply queries with the
tools you are using first, just to make sure you have the technique down
right, before you try a very complicated query.

3. There are probably others who use the combination of tools that you are
using, maybe even within your shop. Ask around, maybe someone else at your
office has has more experience with these tools. After all, someone chose
and purchased those tools; presumably that person has some idea how they
work. If that doesn't pan out, you might try the vendors of those products
directly: you may already be paying for tech support on these products, in
which case you might as well use it. If that doesn't work out, try user
groups. You may very well have DB2, AS/400, or Access user groups in your
area where one of the members has experience with your combination of tools.
Also, consider doing Google web searches for the tool combination that you
are using; maybe someone has written an FAQ or posted some examples of how
to use that combination.

I'm not sure what else to suggest.

Rhino

"Tcs" <ts****@eastpointcity.org> wrote in message
news:al********************************@4ax.com...
Sorry for the delay in my reply. I've been out the past several days. (Back problems.)

On Thu, 19 Aug 2004 16:26:03 -0400, "Rhino" <rh****@NOSPAM.sympatico.ca> wrote:
Let me begin by saying that my familiarity with DB2 on AS/400 is *very*
minimal - a few days several years ago - so I'm not speaking as an expert onDB2 for AS/400! However, this appears to be mainly an issue of SQL, which Iknow reasonably well, and you welcomed *all* replies so I'm going to jump inand risk making an idiot of myself ;-)
ABSOLUTELY! GREAT! THANK YOU!

The rest of my remarks are interspersed below.

Rhino

"Tcs" <ts****@eastpointcity.org> wrote in message
news:nc********************************@4ax.com.. .
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I

haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this
is an
Access question. (But who knows? I COULD be wrong... :) I've tried
the access group...twice...and all I get is "Access doesn't like ".", which Iknow,
or that my query names are too long, as there's a limit to the length
ofthe SQL
statement(s).


Do you mean that the Access group literally said that they didn't like theperiods in your SQL statements or are you using '.' as a placeholder, like Imight say "such-and-such"? If you are using the period as a placeholder,
what were the specific things they told you about your syntax?


Yes, they said that Access doesn't like '.'. And I know this. When I

link a DB2 table to Access it replaces the "." with '_'. So CXLIB.UT420AP becomes CXLIB_UT420AP. But for a "PASS-THRU" query, which Access does NOT process, this shouldn't make any difference. Besides, DB2 doesn't want '_'. Instead, IT wants '.'.

I'm not sure why you are dismissing the remarks people on the Access groupmade. If Access really does have specific limits on the length of the
statements or your query names, those would appear to be genuine things thatyou need to take into account, unless you plan to abandon Access from yourapproach.
I don't mean to dismiss them out of hand, but I don't see how they apply,

in this case, at least. (Read next portion.) I'll confess that my names ten to be long. And in the right circumstance, too long. But so far...Access hasn't barfed on my names being too long.
But this works when I don't try to do it from VB.)
*Which* things work when you don't do them from VB? Do you mean that all ofyour queries work okay when not done through VB or just particular ones?
Which ones?


My two queries. My "PASS-THRU" (backend) query, and my local (frontend)

query. I created both with Access's Query Builder grid. I can run the pass-thru query interactively, and it returns the data I want. IT WORKS.

(Note: "Pass-thru" query to Access means a query native to the backend which will NOT be processed by Access. (Access has its syntax and the BE has its syntax.) It will be sent to the BE, and the returned data used.)

My "Local" (frontend) query. When I run it interactively, it uses the pass-thru query as input, and goes and retrieves the data, just like it should. IT WORKS.
In VB, I'm building the SQL statements for each. Then I run an Access command "DoCmd.RunSQL". This takes my SQL statement(s) and runs them. THIS is what appears to be NOT working. But I think it's my syntax. (I know it may NOT be.) I haven't dealt with nested queries before. But what I'm asking Access to do isn't anything that it CAN'T do. IT'S ALREADY done it. Just not thru VB.

And NO ONE...seems to want to comment on my SQL statement. Of the couple
responses I've received, it's been the "Access doesn't like '>', or the "Your names are too long". And this name, isn't even USED by Access when I try to run it thru VB.

Look at the SQL statement below. Notice the
"qryAcctsRecAging_0010_Current_420_BE" query name. Too long? Perhaps. BUT IT DOES WORK. (The "BE" on the end means that this is my pass-sthru, or backend, query.) This is what Access has built. APPARENTLY it does more processing with this, as this is NOT a usable statement, in this form. So I've tried to modify it as I BELIEVE it needs to be modified. And I'm getting an error.

If someone wants to say that "CXLIB.UT420AP.UTCSID" is too long, well then, I guess I'm SOL. DB2 won't understand anything less. This is the file, table, and field that I want. On the other hand, if this is too long, then why does it work when i don't try thru VB?

When you try them without using VB, just exactly how are you trying them? Inother words, what are you using instead of VB?
I THINK my
problem is just a syntax problem, seeing as this is my first experiencewith
running pass thru queries from VB code. So here goes...

I guess the first question to ask is - Can a pass-thru query be a
nestedquery
as applies to VB code? (When I run my two queries manually, I get the

results I
want...)

I don't know VB (Visual Basic) at all and I'm not sure what you mean by apass-thru query. I'm just telling you that in case some of my later remarksseem especially clueless.


Not a problem. (See above.) I understand.
I have a local table into which I need to append data I retreive from
our AS/400. I initially developed both the local and pass thru queries whichwork
just fine together...with hardcoded parameters, and run manually (or
evenfrom
VB, just not "built" in VB).

What kind of machine is your local table sitting on? A PC? Another AS/400?
2.6GHz P4, OS = XP, RAM = 1gb.
The local query code (which Access created):

INSERT INTO tblAcctsRecAging_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBilled,
CurrentUnPaid )

SELECT
qryAcctsRecAging_0010_Current_420_BE.UTCSID,
qryAcctsRecAging_0010_Current_420_BE.UTLCID,
qryAcctsRecAging_0010_Current_420_BE.UTRCLS,
qryAcctsRecAging_0010_Current_420_BE.UTSVC,
qryAcctsRecAging_0010_Current_420_BE.UTPEYY,
qryAcctsRecAging_0010_Current_420_BE.UTPEMM,
qryAcctsRecAging_0010_Current_420_BE.UTAGE,
qryAcctsRecAging_0010_Current_420_BE.UTTTYP,
qryAcctsRecAging_0010_Current_420_BE.UTTDSC,
qryAcctsRecAging_0010_Current_420_BE.UTTAMT,
qryAcctsRecAging_0010_Current_420_BE.UTUNPD

FROM
tblAcctsRecAging_Details RIGHT JOIN
qryAcctsRecAging_0010_Current_420_BEON
tblAcctsRecAging_Details.LocID =

qryAcctsRecAging_0010_Current_420_BE.UTLCID;

Nothing jumps out at me in the above query.
And the pass thru query (which I created):

SELECT distinct
CXLIB.UT420AP.UTCSID,
CXLIB.UT420AP.UTLCID,
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC,
CXLIB.UT420AP.UTTAMT,
CXLIB.UT420AP.UTUNPD

FROM
CXLIB.UT420AP

WHERE
((CXLIB.UT420AP.UTAGE='C') AND
(((CXLIB.UT420AP.UTPEMM)=7) AND ((CXLIB.UT420AP.UTPEYY)=4)) Or
(((CXLIB.UT420AP.UTPEMM)=8) AND ((CXLIB.UT420AP.UTPEYY)=4)))

ORDER BY
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC;

Are you sure that the above query is what you really want to do? The keywordDISTINCT applies to all of the column names that follow it in the SELECT
clause. In effect, you are saying that you want all of the distinct
*combinations* of ALL of those columns: UTCSID, UTLCID, UTRCLS, UTSVC,
UTPEYY, UTPEMM, UTAGE, UTTTYP, UTTDSC, UTTAMT, UTUNPD. I usually see
DISTINCT applied to only 1 or 2 or maybe 3 columns but this is 11 columns.
Until you asked...I THOUGHT so. Without 'distinct' I get erroneous errors, duplications. Access has no provision for individual 'distinct's, or at least none that I'm aware of. I just normally add it after my select statement. When I've done so, my errors disappear.

Access does not generate a SQL statement with the word 'distinct'. I've only added it after I see the returned data, and see that it's not correct.

I don't know if you're a beginner or very experienced but I find that
mostbeginners with SQL think that DISTINCT only applies to the column name
immediately following the DISTINCT keyword and that all of the other columnsin their SELECT will not be affected by the SELECT. That is simply not true.
Beginner? Guilty.

Actually, I don't ever recall thinking of it as applying to ANY particular
column.
The problem is that my parameters are variable. So I created this:

' compose SQL string 1 for the back end (BE) - DB2
'
strSQLselect1 = _
"SELECT distinct " & _
"CXLIB.UT420AP.UTCSID, " & _
"CXLIB.UT420AP.UTLCID, " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTAGE, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC, " & _
"CXLIB.UT420AP.UTTAMT, " & _
"CXLIB.UT420AP.UTUNPD "

strSQLfrom1 = _
"FROM CXLIB.UT420AP "

strSQLwhere1 = _
"WHERE " & _
"((CXLIB.UT420AP.UTAGE='" & strAgeGroup & "') AND " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int1stMM & ") AND((CXLIB.UT420AP.UTPEYY)="
& int1stYY & ")) Or " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int2ndMM & ") AND

((CXLIB.UT420AP.UTPEYY)="
& int2ndYY & "))) "

strSQLorderby1 = _
"ORDER BY " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC; "

strSQLstatement1 = _
strSQLselect1 & _
strSQLfrom1 & _
strSQLwhere1 & _
strSQLorderby1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBilled, " & _
"CurrentUnPaid ) "

strSQLselect2 = _
"SELECT (" & _
strSQLstatement1 & ".UTCSID, " & _
strSQLstatement1 & ".UTLCID, " & _
strSQLstatement1 & ".UTRCLS, " & _
strSQLstatement1 & ".UTSVC, " & _
strSQLstatement1 & ".UTPEYY, " & _
strSQLstatement1 & ".UTPEMM, " & _
strSQLstatement1 & ".UTAGE, " & _
strSQLstatement1 & ".UTTTYP, " & _
strSQLstatement1 & ".UTTDSC, " & _
strSQLstatement1 & ".UTTAMT, " & _
strSQLstatement1 & ".UTUNPD "

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAging_Details RIGHT JOIN " & strSQLstatement1 & "

ON "& _
"tblAcctsRecAging_Details.LocID = " & strSQLstatement1 &

".UTLCID; )"

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2

The syntax for the above queries looks *very* odd to me.


I was just following Access's lead.
I'm especially
perplexed by the "& _" tokens that are at the end of most - but not *all* -of the lines. What do the ampersands and underscores mean in these queries?
Ampersand is contenation character. "Horse " & "Hockey" equals "Horse Hockey". The underscore is a line conituation character.
At first glance, I thought they might be continuation characters but that
doesn't make sense: why do some lines have both ampersand and underscore,
some have only the underscore, and some have neither?
Because I'm building the statement IN PIECES. The "INSERT INTO " is

separate. The "SELECT" is separate. The "FROM is separate. Then I pull them all together at the end, with:

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2

Did you really have to write the statements like this just to get some
variables into the join? It's a lot easier in most languages. Again, I
don'tknow VB or DB2 on AS/400 so maybe this is a standard approach to statementsin that environment....


I have formatted the statements the way I did for legibility. It's easier

for me to read and understand. Also, using separate pieces to make one whole
statment is necessary as VB has a '_' character limit of 25 per occurrence. (So I had to split them up.)

So NOW what's my problem? Access doesn't like this. I'm getting an
errormsg:

Error # 3075 was generated by MSAcess
Syntax error. in the query expression '(SELECT distinct SELECT
distinct CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC, CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTAGE, CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC,
CXLIB.UT420AP.UTTAMT, CXLIB.UT420AP.UTUNPD'.
Have you copied this correctly? I can certainly understand that it

wouldn'tlike "SELECT distinct" *twice* at the beginning of the query; the keyword
"DISTINCT" can only be in each SELECT clause *ONCE*. And, of course, the
keyword "SELECT" can only appear once in that context too.

Also, are you saying that the error message is for strSQLstatement1,
strSQLstatement2, or both? I'm really not following the way that you are
generating and executing these statements so forgive my confusion.
(Remember, I was using Access's code as an example...the code that

WORKED.) I
didn't understand what it didn't like, so...

I changed the above to this:

strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
strWhichAmtBilled & "AmtBilled, " & _
strWhichAmtUnpaid & "AmtUnpaid ) "

strSQLselect2 = _
"SELECT " & _
"UTCSID, " & _
"UTLCID, " & _
"UTRCLS, " & _
"UTSVC, " & _
"UTPEYY, " & _
"UTPEMM, " & _
"UTAGE, " & _
"UTTTYP, " & _
"UTTDSC, " & _
"UTTAMT, " & _
"UTUNPD "

strSQLfrom2 = _
"FROM CXLIB.UT420AP "

strSQLwhere2 = _
"WHERE " & _
"tblAcctsRecAging_Details RIGHT JOIN in (" & strSQLstatement1 &
")ON "
& _
"tblAcctsRecAging_Details.LocID = CXLIB.UT420AP.UTLCID; "

Your join definitely looks wrong to me. It needs to be something more like:
FROM EMPLOYEE e RIGHT JOIN DEPARTMENT D on e.workdept in ('D11', 'E21')
AND e.workdept = d.deptno

[Personally, I only put join conditions in the FROM clause and put local
predicates like 'e.workdept in ('D11', 'E21')' in my WHERE clause; I find
that easier to read. However, I'm not saying that it is *wrong* to put localpredicates in your FROM clause.]

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2 & _
strSQLwhere2

I've inpected the SQL statements after they're built, and they appear
tobe
correct. (Well, COMPLETE, anyway. CORRECT I guess is why I'm here
askingfor
help.)

The error I'm getting is:

Error # 3075 was generated by MSAccess
Syntax error (missing operator) in query expression

'tblAcctsRecAging_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXL'.

I took out the "in" after "RIGHT JOIN".


See my remarks about your join, above.

Putting a SELECT statement within an IN clause is not remotely valid syntaxso I can see why Access doesn't like that!


But I've seen other messages in the Access group that appear the same when
they're talking about nested queries. That's why I did, what I did.

Perhaps it's a 'backend' vs 'frontend' thing.
I'm still getting the same error:

Error # 3075 was generated by MSAccess
Syntax error (missing operator) in query expression

'tblAcctsRecAging_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.UTCSID, CXLIB.UT420AP.UTLCID, CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY, CXLIB.UT420AP.UTPEMM, CXLIB.UT420AP.UTAGE,
CXLIB.UT420AP.UTTTYP, CXLIB.UT420AP.UTTDSC, CXLIB.'.

Since I'm still getting an error, I'm obviously still missing something

with
regards to my nested query. Can/will anyone hazard a guess?

Again, I'm still a little confused about which precise statement each of
these error message refers to. I also don't understand why you've written
the statements the way you have with all of this concatenation of strings.But, again, maybe this is the only way to do these things in your
environment so I'll assume you've done it in the only way possible.
Personally, if I had to write this program, I'd do it using Java.
PreparedStatement objects would do the job quite nicely and a lot more
simply; but that's just me ;-)


I don't know Java.
Access = 2000
OS = XP

Thanks a lot, in advance... Any help is appreciated...

I probably haven't been much help but maybe something I've said will ring abell and help you see what's wrong. Or, more likely, someone with more
AS/400 and VB experience will see what you're doing wrong and set you
straight.

Good luck!

Rhino


Thank you!!!

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.