Joining two fields with different lengths in data

[rodfuentes]

Hello,
I currently have two linked tables in my DB2 Database:

Table1: Reservations
Fields:
Reservation_Number (ABC123456)
Status

Table2: Docs
Doc Number:
Reservation_Number (ABC123456000)

I'm trying to match Reservation Number in both tables but table 2 has added three zeros at the very end therefore brings back no matches.

I've tried converting Reservation Number in Table2 by creating a new field doing the following:

NEW_NBR: Mid([Reservation_Number],1,9)

However if it does bring back any results it takes a HUGE amount of time.

Is there any other way I can try to do this so it can give me quick results?

Limitations: I cannot save the tables as local tables as I need to have live information from customers as they arrive or are updated.

Please help.

Regards,

[zmbd]

You suffer from a lack of normalization[*]> Database Normalization and Table Structures.

Also, this forum is for Access, not DB2; however, the concept will hold true regadless of the database.

Now if you are using Access as your front end, we can use some vba to determine the length of the reservation number in table 1 and then append enough zeros to match the length in table 2 - I'm making a guess here that the appended 0's are because the field in table 2 is a fixed width... we have an old legacy db that had these fixed width text fields that we manually appended 0's to the entries. From there we can return a recordset to a form that shows your required records.


If you can alter any of the other table information the I would suggest you normalize the table structure.
--VERY simple normalization---
So, in a hotel, very simple, we could have the following tables

tbl_rooms : room details, Minibar, pets, non-smoking, etc...
tbl_reservation : see following
tbl_client : this could be broken down but for simplicity think address book
etc...

Each table would have a primay key.

Now say we need to relate the information as you are tring to do in your OP
PK = Primary Key
FK = Forgein Key

tbl_reservation
[reservation_pk]
[reservation_fk_customer]
[reservation_datemade]
[reservation_datestart]
[reservation_duration]

tbl_roombooking
[booking_pk]
[booking_date]
[booking_fk_reservation]
[booking_fk_rooms]

See how the bulk of the information is stored in only one table for each concept?

[rodfuentes]

Thank you zmbd however this is the enterprise database, I cannot modify the tables therefore need to find a way around it to make it work

[Rabbit]

Three areas for optimization, do one, two, or all three.

1. Use a pass through query, you will need to format the SQL to the DB2 standard which can be slightly different
   
2. Put an index on the fields in DB2
   
3. Use LIKE with a wildcard at the end

[NeoPa]

While linking table fields always requires that the fields match in type and length particulars, this is not true for queries (or QueryDefs). If you take the table with fewer records in it and represent it instead by a query that returns all the records (or a filtered subset if that is a requirement) in the required order and with the linked field updated then this will allow you to link the two tables on those fields.

Naturally, as you're not taking advantage of the design of the table itself, this can fall foul of much poorer performance. Nevertheless, by playing with the order of the returned data you can sometimes minimise this.

Alternatively, you can try to get something designed on the DB engine itself, in your case DB2, which does that for you and returns a recordset that's already been matched up. Clearly that relies on DB2 SQL and access to the server.

[rodfuentes]

I've tried so far to create isolated queries to match the fields based on calculated fields created on each one of those isolated queries, the best response time I've had is about 4 minutes 50 seconds.

[rodfuentes]

Thank you all for your responses, I understand the flaw in the database design but since it's tables built by the company itself I don't have access to edit the layout on them.

I'm still looking forward to create an optimized type of queue report with those fields included.

Any ideas are welcome.

[Rabbit]

Did you implement my suggestions in post #4? Show us the SQL that you're using.