Using MS Acess SQL Passthrough To AS400 to find wholes in number sequence between to files
Below is a SQL statement from an Ms-access pass-through query that is pulling the info from an AS400 system. The Fields marked in bold are the two table that I need help with. This query returns the same part numbers form each of the two columns of cores because there are of differ tables. I know from running queries of each of these tables separately that there are some numbers that are in the INMSP100.MSPRD that are not in the MSPMP100.PRDNO and vise-a-versa, and there are those numbers that are common to both. I need a list (or a SQL statement) that would show me the Is there anyway to set up this query that it would show a blank in the MSPMP100.PRDNO if the the number is in the INMSP100.MSPRD and the reverse too? I need to know what numbers are in the one but not in the other for both. Or in other wards I need to know the Holes in each of the different columns. Also: A second thought is if there anyway that I could have the query look at each of the numbers columns and show me a hole or a blank if the number sequence is missing a number or a series of them?
Select [strong]INMSP100.MSPRD[/strong] [em]as "IN0140"[/em] ,[strong]MSPMP100.PRDNO[/strong] [em]as "MS0802",[/em]INMSP100.MSCLS,INMSP100.MSOEM,INMSP100.MSOM#,INMSP100.MSOM2,INMSP100.MSOM3,INMSP100.MSOM4,INMSP100.MSOM5,INMSP100.MSOM6, MSPMP100.pmcaor as "MDT Ord",MSPMP100.CLASS From RDB.CARF1.INMSP100 INMSP100 join RDB.CARF1.MSPMP100 MSPMP100 on INMSP100.MSCLS=MSPMP100.CLASS and INMSP100.MSPRD= MSPMP100.PRDNO
Software/Hardware used:
Select [strong]INMSP100.MSPRD[/strong] [em]as "IN0140"[/em] ,[strong]MSPMP100.PRDNO[/strong] [em]as "MS0802",[/em]INMSP100.MSCLS,INMSP100.MSOEM,INMSP100.MSOM#,INMSP100.MSOM2,INMSP100.MSOM3,INMSP100.MSOM4,INMSP100.MSOM5,INMSP100.MSOM6, MSPMP100.pmcaor as "MDT Ord",MSPMP100.CLASS From RDB.CARF1.INMSP100 INMSP100 join RDB.CARF1.MSPMP100 MSPMP100 on INMSP100.MSCLS=MSPMP100.CLASS and INMSP100.MSPRD= MSPMP100.PRDNO
Software/Hardware used:
ASKED:
August 13, 2009 5:17 PM
UPDATED:
August 14, 2009 4:29 PM
Answer Wiki:
Yes, with two sql statemetns
If you change the join RDB.CARF1.INMSP100 INMSP100 join RDB.CARF1.MSPMP100 MSPMP100
to RDB.CARF1.INMSP100 INMSP100 LEFT join RDB.CARF1.MSPMP100 MSPMP100
it will show every record from the field on the left RDB.CARF1.INMSP100 INMSP100
even those that aren't matched on the right but you want to eliminate those with a match on the righ
so at the end of the sql add
where MSPMP100.CLASS is NULL
That will give you INMSP100's that aren't matched
For the others you will need to reverse the process
RDB.CARF1.INMSP100 INMSP100 RIGHT join RDB.CARF1.MSPMP100 MSPMP100
and
where INMSP100.MSCLS is NULL
Phil
---------------
Littlepd asked a good question.
You can run this statement on the 400 - in a session use STRSQL
or using Navigator select SQL
either option would be faster.
Phil
----------------------------------------
Not familiar with "pass-though" in Access -- it's been awhile since I've used access (hit computers were made of rocks and had cranks). The pass-through should do just fine. Data Transfer System i is used for coping data from the AS/400 (system i) to your pc. So did the querys with Left and Right join work?
Phil
To see all answers submitted to the Answer Wiki: View Answer History.
Why is this an Access query passthrough to the 400? Why aren’t you just running this SQL on the 400?
I am new in all this..I have found that using the AS400 system is some what overbearing. I have used the AS400 query menus in the past. In do so, I had to go to diferent screens and enter the files in one area then enter the feilds in another section, do the relationships in anothe…. then you need to move to different section to set up your print out or output section. I also have tried the the Iseries and that was some what difficult at the start. There are alot of times that I need to do comparisons of the data and with using the Access it was easer to to the links and comparissions too.
I followed your advice and
By the way, the pass through query does run on the AS400, that is the whole reason for doing it. Pass through queries in Access are commonly sent to the target DBMS through an ODBC connector. If the target tables are large and the result set is a fraction of the target, this avoids the shipping of the whole target table set over the network, as Access would do if the target tables were merely linked to the Access database.
Pass through queries must be in the target DBMS SQL syntax, another clue to it being run in the target server.
He is using Access as a convenient front end because it has QBE and a convenient display interface.
Sorry about the above I hit the wrong key and was not able to proof or continue. What is the best way to pull the info from the AS400 and then be able to manipulate? It seems easier to use Access and do it all in the SQL window? But I looked at the AS400 and found the “Data Transfer System i- PC5250SA.TTO, associated with the AS400 system and there is a “System i”. When you click on the Data Option a “Change Data Options” window appears and it lists different input location titles: Group by, Select, Where, Have, and Order by. There are other options “Return records with missing fields or Enable group functions” that can be checked. This looks like the place that you would enter the SQL statement? Yes or No? I would like to learn whatever I can to be able to interact with both the Access and AS400 and manipulate the data and other database systems. So finding way to go about it to interact with all the different systems is my goal. So I am sure we will be talking a lot. You input and direction would be greatly appreciative.