Thought to be simple query

[tmcrouse]

I have two tables with multiple columns and the primary key is the same but not the same length. 1 is only 8000 rows and the other 10.7 million. I am trying to take the 8000 and compare it to the 10.7 million and get all the data from the 10.7 million based on the primary key in the 8000 row table. The key looks like this:

table1 table2

CustNbr1 CustNbr2
00800900 0800900
00810900 0810900
00811900 0811900
00912480 3912480

I have tried to do a join query and the query looks like this and it comes up with no results every time.

select right([custnbr1],6) as expr1, right([custnbr2],6) as expr2, table2.subid, table2.ind from table1 inner join table2 on table1.custnbr1=table2.custnbr2;

Now remember I typed this by looking at my work laptop and just manually typing so I can't copy and paste and if I missed some () or [] I apologize. The SQL is right in my work laptop. I just can't go to sites on my work laptop like this. They are not allowed.

I have already tried doing an update query on table 1 to make the length only 7 however if you notice the last number in the line, 1 has 00 in the beginning and the other 3. The digit prior to the 6 numbers is called our obligor ID and it can vary from 0 to 6. The last 6 digits are always the same and are always the custnbr. it is just some systems put 00 before every custnbr and some put the obligor ID before the custnbr. I have a work around by updating each table to be only the 6 digits and then query but the 10.7 million row table2 takes like 4 hours to run the update query and I was hoping for something a bit faster.

[weekend00]

Since you had finished updating the table2 to only 6 digits (it took 4 hours), why do you care how long it took? It had been finished already, and it's one time update.

If you are adding new records, just need to update those new added, do need to update the whole table, it won't take long.

Another way is to modify the join condition, but this may make you query run slowly.
select right([custnbr1],6) as expr1, right([custnbr2],6) as expr2, table2.subid, table2.ind from table1 inner join table2 on Right(table1.custnbr1,6)=table2.custnbr2;

[tmcrouse]

This is a monthly query I have to run on brand new data every month. so this months hasn't been done yet and I am trying to find a faster way besides the update taking 4 hours because it wastes time. I have been doing the work around with updating both tables thus the 4 hours for the past several months and now am working 14 hour days and trying to find ways to shorten query times thus shorten my days

[tmcrouse]

let me see if I can shed some light to those that are trying to help. every month i get a brand spaking new table1 with new custnbrs and it could be 8000 to 15000 records. never same custnbr always same length and always 00 before the 6 digit custnbr. table2 is always 10.7 million records or more depending on new subscribers we have provided insurance to or ones we lost. the 6 digit custnbr is always the same unless brand new policy and it will have anywhere from a 0 to a 6 before the 6 digits. this is cuz table1 comes from myuhc.com oracle DB and table2 comes from our customer eligibility dept and they are on db2. the systems do not communicate with one another in any way shape or form. so i get raw files over in the form of a .bat. i have to open with .txt for the small table and import and could import how i choose. chopping the 00 off the front and just making it 6 digits or whatever. it is the 10+ million sub table that has the issues due to size. i have to first open with ultraedit cuz too large for .txt. then import to access because that is the only tool the co. allows me to have cuz it costs them zero $$$$. but the format of this is always the same. 0800900 or 1800900 or 2800900 etc. cuz we have obligor id 1 to 6. so...if i run an update query on the 10+ mil table it takes 4 hours cuz i am chopping off that 1 digit prior to the file. so i am trying to find a way around this that is faster than 4 hours. it isn't that i just get some new custnbr and update solely that. can't do that cuz i get a new raw file each month and this file might have the same custnbr but they have different custnbr settings. meaning if last month 10000 of our subscribers had paper delivery of their EOBs and healthstatemtns, this month we will have 20000. this is why the file is different every month. i am just looking for someone to tell me the correct query to use to i can take table1 and compare to table2 without running update queries to make the primary keys the same number of digits.

[weekend00]

If you don't want to update, use following query, but it may take 4 hours to run because no index can be used.

select right([custnbr1],6) as expr1, right([custnbr2],6) as expr2, table2.subid, table2.ind from table1 inner join table2 on Right(table1.custnbr1,6)=right(table2.custnbr2,6);

[weekend00]

you may think about cutting the dight when you import the data.

[NTC]

you might try this approach:

make a query of CustNbr1 (with only 8k records) include all the needed field plus make a calculated field that results in the CustNbr2 key. (from your post it seems you have the logic to do this....Lets call this new field 'CNbr1A' and save this query as 'Q1A'.

Then make a new select query with Q1A and Table 2. Make the join from CNbr1A field to CustNbr2 field.

10M is alot of records but I think this approach would be as fast as is possible. One should also check that that the CustNbr2 field is set to be indexed.

Hope this helps.

[weekend00]

Good idea NTC,

Addition point:

because obligor ID can vary from 0 to 6, you need to add "0" to "6" in front of right(table1.curnbr1,6) to make up 7*8000=56000 records to match table2. still much faster than changing 10M.

[tmcrouse]

select right([custnbr1],6) as expr1, right([custnbr2],6) as expr2, table2.subid, table2.ind from table1 inner join table2 on Right(table1.custnbr1,6)=right(table2.custnbr2,6);

running this as a select took like 30 seconds. very wierd.

[weekend00]

Did you get the right result?