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...
3  6414 
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
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!!!
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
thisis 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!!!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: John Wilson |
last post by:
Hello,
I have the following code which populates as table data from a SQL
Server 2000 stored proc (RSByDemoID2). Below that is the view and
stored procedure which takes @DemoID as input to match...
|
by: WGW |
last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost!
everything I need. Maybe not efficiently, but usefully. However, I have
a problem -- a complex query problem...
I can...
|
by: Matt |
last post by:
I am working in a project where the business model is complex enough
that many common retrieval functions become difficult to develop and
maintain in single query statements or functions.
I have...
|
by: Anthony Robinson |
last post by:
Have been encountering an odd issue. Every now and again, certain
packages of stored procedures just become invalid. I'm aware that
dropping or altering an underlying table would render a package...
|
by: Twan Kennis |
last post by:
Question: How do I pass a returning resultset from a nested Stored
Procedure (which opens a cursor including option "WITH RETURN TO
CALLER") as a returning resultset from it's own?
When I...
|
by: GY2 |
last post by:
I writing some documentation and I want to describe a common code structure
which is used to step through all the items in a collection (e.g. each file
in a subdirectory) while applying more and...
|
by: brad |
last post by:
Group,
I'm using Visual Studio 2003 to create an ASP.NET 1.1 project which
contains nested server user controls in order to create a tree-like
hierarchy.
The tree is a sort of question and...
|
by: =?Utf-8?B?SmVyZW15X0I=?= |
last post by:
I am working on an order entry program and have a question related to
deserializing nodes with nested elements.
The purchase order contains multiple line items which I select using an...
|
by: P3Eddie |
last post by:
Hello all!
I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same.
My problem may be a simple find duplicates / do something...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
