473,903 Members | 3,329 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

nested select question

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 tblAcctsRecAgin g_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBille d,
CurrentUnPaid )

SELECT
qryAcctsRecAgin g_0010_Current_ 420_BE.UTCSID,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTLCID,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTRCLS,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTSVC,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTPEYY,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTPEMM,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTAGE,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTTYP,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTDSC,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTAMT,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTUNPD

FROM
tblAcctsRecAgin g_Details RIGHT JOIN qryAcctsRecAgin g_0010_Current_ 420_BE ON
tblAcctsRecAgin g_Details.LocID = qryAcctsRecAgin g_0010_Current_ 420_BE.UTLCID;

And the pass thru query (which I created):

SELECT distinct
CXLIB.UT420AP.U TCSID,
CXLIB.UT420AP.U TLCID,
CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY,
CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP,
CXLIB.UT420AP.U TTDSC,
CXLIB.UT420AP.U TTAMT,
CXLIB.UT420AP.U TUNPD

FROM
CXLIB.UT420AP

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

ORDER BY
CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY,
CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TTTYP,
CXLIB.UT420AP.U TTDSC;

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.UT420A P.UTAGE='" & strAgeGroup & "') AND " & _
"(((CXLIB.UT420 AP.UTPEMM)=" & int1stMM & ") AND ((CXLIB.UT420AP .UTPEYY)="
& int1stYY & ")) Or " & _
"(((CXLIB.UT420 AP.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; "

strSQLstatement 1 = _
strSQLselect1 & _
strSQLfrom1 & _
strSQLwhere1 & _
strSQLorderby1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAgin g_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear , " & _
"PeriodMont h, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBill ed, " & _
"CurrentUnP aid ) "

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

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAgi ng_Details RIGHT JOIN " & strSQLstatement 1 & " ON " & _
"tblAcctsRecAgi ng_Details.LocI D = " & strSQLstatement 1 & ".UTLCID; )"

strSQLstatement 2 = _
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.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC, CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TAGE, CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC,
CXLIB.UT420AP.U TTAMT, CXLIB.UT420AP.U TUNPD'.

(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 tblAcctsRecAgin g_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear , " & _
"PeriodMont h, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
strWhichAmtBill ed & "AmtBilled, " & _
strWhichAmtUnpa id & "AmtUnpaid ) "

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

strSQLfrom2 = _
"FROM CXLIB.UT420AP "

strSQLwhere2 = _
"WHERE " & _
"tblAcctsRecAgi ng_Details RIGHT JOIN in (" & strSQLstatement 1 & ") ON "
& _
"tblAcctsRecAgi ng_Details.LocI D = CXLIB.UT420AP.U TLCID; "

strSQLstatement 2 = _
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 'tblAcctsRecAgi ng_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM, CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC, 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 'tblAcctsRecAgi ng_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM, CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC, 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
3 6477
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****@eastpoi ntcity.org> wrote in message
news:nc******** *************** *********@4ax.c om...
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 tblAcctsRecAgin g_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBille d,
CurrentUnPaid )

SELECT
qryAcctsRecAgin g_0010_Current_ 420_BE.UTCSID,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTLCID,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTRCLS,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTSVC,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTPEYY,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTPEMM,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTAGE,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTTYP,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTDSC,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTAMT,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTUNPD

FROM
tblAcctsRecAgin g_Details RIGHT JOIN qryAcctsRecAgin g_0010_Current_ 420_BE ON tblAcctsRecAgin g_Details.LocID = qryAcctsRecAgin g_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.U TCSID,
CXLIB.UT420AP.U TLCID,
CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY,
CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP,
CXLIB.UT420AP.U TTDSC,
CXLIB.UT420AP.U TTAMT,
CXLIB.UT420AP.U TUNPD

FROM
CXLIB.UT420AP

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

ORDER BY
CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY,
CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TTTYP,
CXLIB.UT420AP.U TTDSC;
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.UT420A P.UTAGE='" & strAgeGroup & "') AND " & _
"(((CXLIB.UT420 AP.UTPEMM)=" & int1stMM & ") AND ((CXLIB.UT420AP .UTPEYY)=" & int1stYY & ")) Or " & _
"(((CXLIB.UT420 AP.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; "

strSQLstatement 1 = _
strSQLselect1 & _
strSQLfrom1 & _
strSQLwhere1 & _
strSQLorderby1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAgin g_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear , " & _
"PeriodMont h, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBill ed, " & _
"CurrentUnP aid ) "

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

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAgi ng_Details RIGHT JOIN " & strSQLstatement 1 & " ON " & _ "tblAcctsRecAgi ng_Details.LocI D = " & strSQLstatement 1 & ".UTLCID; )"
strSQLstatement 2 = _
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.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC, CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TAGE, CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC,
CXLIB.UT420AP.U TTAMT, CXLIB.UT420AP.U TUNPD'.
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 strSQLstatement 1,
strSQLstatement 2, 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 tblAcctsRecAgin g_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear , " & _
"PeriodMont h, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
strWhichAmtBill ed & "AmtBilled, " & _
strWhichAmtUnpa id & "AmtUnpaid ) "

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

strSQLfrom2 = _
"FROM CXLIB.UT420AP "

strSQLwhere2 = _
"WHERE " & _
"tblAcctsRecAgi ng_Details RIGHT JOIN in (" & strSQLstatement 1 & ") ON " & _
"tblAcctsRecAgi ng_Details.LocI D = CXLIB.UT420AP.U TLCID; "
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.]
strSQLstatement 2 = _
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 'tblAcctsRecAgi ng_Details RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM, CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC, 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 'tblAcctsRecAgi ng_Details RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM, CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC, 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.
PreparedStateme nt 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
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****@eastpoi ntcity.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.RunS QL". 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
"qryAcctsRecAgi ng_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 tblAcctsRecAgin g_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBille d,
CurrentUnPaid )

SELECT
qryAcctsRecAgin g_0010_Current_ 420_BE.UTCSID,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTLCID,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTRCLS,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTSVC,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTPEYY,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTPEMM,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTAGE,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTTYP,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTDSC,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTAMT,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTUNPD

FROM
tblAcctsRecAgin g_Details RIGHT JOIN qryAcctsRecAgin g_0010_Current_ 420_BEON
tblAcctsRecAgin g_Details.LocID =

qryAcctsRecAgi ng_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.U TCSID,
CXLIB.UT420AP.U TLCID,
CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY,
CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP,
CXLIB.UT420AP.U TTDSC,
CXLIB.UT420AP.U TTAMT,
CXLIB.UT420AP.U TUNPD

FROM
CXLIB.UT420AP

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

ORDER BY
CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY,
CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TTTYP,
CXLIB.UT420AP.U TTDSC;

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
*combination s* 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.UT420A P.UTAGE='" & strAgeGroup & "') AND " & _
"(((CXLIB.UT420 AP.UTPEMM)=" & int1stMM & ") AND((CXLIB.UT420A P.UTPEYY)="
& int1stYY & ")) Or " & _
"(((CXLIB.UT420 AP.UTPEMM)=" & int2ndMM & ") AND

((CXLIB.UT420A P.UTPEYY)="
& int2ndYY & "))) "

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

strSQLstatement 1 = _
strSQLselect1 & _
strSQLfrom1 & _
strSQLwhere1 & _
strSQLorderby1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAgin g_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear , " & _
"PeriodMont h, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBill ed, " & _
"CurrentUnP aid ) "

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

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAgi ng_Details RIGHT JOIN " & strSQLstatement 1 & " ON "

& _
"tblAcctsRecAgi ng_Details.LocI D = " & strSQLstatement 1 &

".UTLCID; )"

strSQLstatement 2 = _
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:

strSQLstatement 2 = _
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.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC, CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TAGE, CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC,
CXLIB.UT420AP.U TTAMT, CXLIB.UT420AP.U TUNPD'.

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 strSQLstatement 1,
strSQLstatemen t2, 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 tblAcctsRecAgin g_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear , " & _
"PeriodMont h, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
strWhichAmtBill ed & "AmtBilled, " & _
strWhichAmtUnpa id & "AmtUnpaid ) "

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

strSQLfrom2 = _
"FROM CXLIB.UT420AP "

strSQLwhere2 = _
"WHERE " & _
"tblAcctsRecAgi ng_Details RIGHT JOIN in (" & strSQLstatement 1 & ")

ON "
& _
"tblAcctsRecAgi ng_Details.LocI D = CXLIB.UT420AP.U TLCID; "

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.]

strSQLstatement 2 = _
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

'tblAcctsRecAg ing_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM, CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC, 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'tblAcctsRecAg ing_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM, CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC, 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.
PreparedStatem ent 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
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****@eastpoi ntcity.org> wrote in message
news:al******** *************** *********@4ax.c om...
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****@eastpoi ntcity.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.RunS QL". 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
"qryAcctsRecAgi ng_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 tblAcctsRecAgin g_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBille d,
CurrentUnPaid )

SELECT
qryAcctsRecAgin g_0010_Current_ 420_BE.UTCSID,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTLCID,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTRCLS,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTSVC,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTPEYY,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTPEMM,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTAGE,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTTYP,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTDSC,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTTAMT,
qryAcctsRecAgin g_0010_Current_ 420_BE.UTUNPD

FROM
tblAcctsRecAgin g_Details RIGHT JOIN
qryAcctsRecAgin g_0010_Current_ 420_BEON
tblAcctsRecAgin g_Details.LocID =

qryAcctsRecAgi ng_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.U TCSID,
CXLIB.UT420AP.U TLCID,
CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY,
CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP,
CXLIB.UT420AP.U TTDSC,
CXLIB.UT420AP.U TTAMT,
CXLIB.UT420AP.U TUNPD

FROM
CXLIB.UT420AP

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

ORDER BY
CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY,
CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TTTYP,
CXLIB.UT420AP.U TTDSC;

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
*combination s* 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.UT420A P.UTAGE='" & strAgeGroup & "') AND " & _
"(((CXLIB.UT420 AP.UTPEMM)=" & int1stMM & ") AND((CXLIB.UT420A P.UTPEYY)="
& int1stYY & ")) Or " & _
"(((CXLIB.UT420 AP.UTPEMM)=" & int2ndMM & ") AND

((CXLIB.UT420A P.UTPEYY)="
& int2ndYY & "))) "

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

strSQLstatement 1 = _
strSQLselect1 & _
strSQLfrom1 & _
strSQLwhere1 & _
strSQLorderby1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAgin g_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear , " & _
"PeriodMont h, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBill ed, " & _
"CurrentUnP aid ) "

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

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAgi ng_Details RIGHT JOIN " & strSQLstatement 1 & "

ON "& _
"tblAcctsRecAgi ng_Details.LocI D = " & strSQLstatement 1 &

".UTLCID; )"

strSQLstatement 2 = _
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:

strSQLstatement 2 = _
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.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC, CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM,
CXLIB.UT420AP.U TAGE, CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC,
CXLIB.UT420AP.U TTAMT, CXLIB.UT420AP.U TUNPD'.
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 strSQLstatement 1,
strSQLstatemen t2, 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 tblAcctsRecAgin g_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear , " & _
"PeriodMont h, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
strWhichAmtBill ed & "AmtBilled, " & _
strWhichAmtUnpa id & "AmtUnpaid ) "

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

strSQLfrom2 = _
"FROM CXLIB.UT420AP "

strSQLwhere2 = _
"WHERE " & _
"tblAcctsRecAgi ng_Details RIGHT JOIN in (" & strSQLstatement 1 &
")ON "
& _
"tblAcctsRecAgi ng_Details.LocI D = CXLIB.UT420AP.U TLCID; "

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.]

strSQLstatement 2 = _
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

'tblAcctsRecAg ing_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM, CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC, 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

'tblAcctsRecAg ing_Details
RIGHT JOIN in (SELECT distinct
CXLIB.UT420AP.U TCSID, CXLIB.UT420AP.U TLCID, CXLIB.UT420AP.U TRCLS,
CXLIB.UT420AP.U TSVC,
CXLIB.UT420AP.U TPEYY, CXLIB.UT420AP.U TPEMM, CXLIB.UT420AP.U TAGE,
CXLIB.UT420AP.U TTTYP, CXLIB.UT420AP.U TTDSC, 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.
PreparedStatem ent 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
4361
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 to the event_id. For Q? and Comments I am viewing/updating in a different table than I am question and how_to_answer. The stored proc is populated by a view that I'm using to get all these values from two tables. My quandry is, I am getting the...
3
16952
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 create a parameter query in a stored procedure, but how do I use the result set of a parameter query in a select query (in the same or another sp)? In short, if a select query contains a result table that is generated as a parameter query, how do I...
5
2356
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 found the logic is easier to implement (and later modify when the code is no longer freshly remembered), by implementing the processing layers in nested views, and functions that call sub-functions (UDFs), not too unlike object based programming...
7
5682
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 invalid, but we are doing no such thing... After banging my head on the wall for a bit I noticed that the two stored procedures that are experiencing this behavior are procedures that are called from within another procedure (they're not both...
2
10642
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 execute the top-level Stored Procedure, it executes succesfully, but without any resultset. ========================================================== CREATE PROCEDURE sp_executesql(sqltxt CLOB(2M))
25
2271
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 more restrictive filters so that only the desired items can fall all the way through. This method is so obvious and common it must have a name. What is it or at least, what is the best (short) way to describe it? For Each In If Then If Then
2
2420
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 answer dialog. The user answers a question, and the next subquestion appears (using dynamic html display:none|block) depending on his answer.
1
11829
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 XmlNodeList. I am trying to deserialize the nodes using a foreach as follows: foreach(XmlNode lineItem in LineItemsNodeList) An abbreviated example of the nested lineItem node looks like this:
9
4401
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 with only one of the duplicates issue, but I got to the point where an update query with a nested select subquery would work wonderfully, if only it would work! I have several fields in a master Access 2000 table, some of which are id, fname,...
0
10003
marktang
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11291
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10882
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10504
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9692
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8055
isladogs
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5897
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6099
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3327
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.