AS400 Query 1

[zrazzaq]

hi I have the following sql statement linking to AS400 table:

SELECT           
ALL SUBSTR(DSRFLAT,71,9) AS SSN, SUBSTR(DSRFLAT,21,2) AS LOANNBR,SUBSTR(DSRFLAT,342,1) AS DISBNBR, SUBSTR(DSRFLAT,4,19) AS CLIENTID, SUBSTR(DSRFLAT,334,8) AS RLSDATE1, SUBSTR(DSRFLAT,383,7) AS 
DISBAMT1, SUBSTR(DSRFLAT,390,7) AS ORIGFEE,SUBSTR(DSRFLAT,397,7) AS GUARFEE, SUBSTR(DSRFLAT,444,7) AS  
FEESPAID, SUBSTR(DSRFLAT,404,7) AS NETDISB,SUBSTR(DSRFLAT,296,2) AS LOANTYPE FROM AGIDATA.AGSVPFDSR.AG06023DW1 T01 WHERE SUBSTR(DSRFLAT,71,9) > '000000' ORDER BY  SSN ASC, LOANNBR ASC, DISBNBR ASC

The issue I am having is that AGIDATA.AGSVPFDSR is the table and AG06023DW1 is the member. This is giving me an error for connection. If I do it without AG06023DW1 and just do AGIDATA.AGSVPFDSR, I have no problems.
I am doing this in access as a Pass-through query.
Can someone please tell me how do I add the member name which has all the data in it to the query.
Thanks
Zishan

 

[Mercury2]

1/ Create an alias over the table to point to the member and then use this alias in the SELECT stm.

CREATE ALIAS AGIDATA/MYALIAS FOR AGIDATA/AGSVPFDSR (AG06023DW1)

2/ or OVRDBF AGIDATA/AGSVPFDSR MBR( AG06023DW1 )
and then use STRSQL

 

[zrazzaq]

It doesn't work in access pass through query

SELECT                                                                  
ALL SUBSTR(DSRFLAT,71,9) AS SSN, SUBSTR(DSRFLAT,21,2) AS LOANNBR, 
SUBSTR(DSRFLAT,342,1) AS DISBNBR, SUBSTR(DSRFLAT,4,19) AS CLIENTID, 
SUBSTR(DSRFLAT,334,8) AS RLSDATE1, SUBSTR(DSRFLAT,383,7) AS 
DISBAMT1, SUBSTR(DSRFLAT,390,7) AS ORIGFEE,                 
SUBSTR(DSRFLAT,397,7) AS GUARFEE, SUBSTR(DSRFLAT,444,7) AS  
FEESPAID, SUBSTR(DSRFLAT,404,7) AS NETDISB,                 
SUBSTR(DSRFLAT,296,2) AS LOANTYPE                           
FROM 
OVRDBF AGIDATA.AGSVPFDSR MBR (AG06026DW1)  T01
WHERE SUBSTR(DSRFLAT,71,9) > '000000'

I even tried placing it up on top
the error I get here is
[IBM][iseries Access ODBC Driver][DB2 UDB]SQL0104 - Token was not valid, Valid Tokens: FOR WITH FETCH ORDER UNION OPTIMIZE (#-104).

Thanks for helping have any other ideas
Zishan

 

[Mercury2]

1/ STRSQL

2/ CREATE ALIAS AGIDATA/MYALIAS FOR AGIDATA/AGSVPFDSR(AG06023DW1)

3/ SELECT
ALL SUBSTR(DSRFLAT,71,9) AS SSN, SUBSTR(DSRFLAT,21,2) AS LOANNBR,
SUBSTR(DSRFLAT,342,1) AS DISBNBR, SUBSTR(DSRFLAT,4,19) AS CLIENTID,
SUBSTR(DSRFLAT,334,8) AS RLSDATE1, SUBSTR(DSRFLAT,383,7) AS
DISBAMT1, SUBSTR(DSRFLAT,390,7) AS ORIGFEE,
SUBSTR(DSRFLAT,397,7) AS GUARFEE, SUBSTR(DSRFLAT,444,7) AS
FEESPAID, SUBSTR(DSRFLAT,404,7) AS NETDISB,
SUBSTR(DSRFLAT,296,2) AS LOANTYPE
FROM AGIDATA/MYALIAS
WHERE SUBSTR(DSRFLAT,71,9) > '000000'

 

[zrazzaq]

Ohh My GOD it worked. You are the best. Now how do I drop the table I created at the end.
Thanks
Z

 

[Mercury2]

DROP ALIAS AGIDATA/MYALIAS