473,320 Members | 1,949 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Joining two fields with different lengths in data

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,
Feb 27 '14 #1
7 2353
zmbd
5,501 Expert Mod 4TB
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?
Feb 27 '14 #2
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
Feb 27 '14 #3
Rabbit
12,516 Expert Mod 8TB
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
Feb 27 '14 #4
NeoPa
32,556 Expert Mod 16PB
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.
Mar 1 '14 #5
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.
Mar 3 '14 #6
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.
Mar 3 '14 #7
Rabbit
12,516 Expert Mod 8TB
Did you implement my suggestions in post #4? Show us the SQL that you're using.
Mar 3 '14 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Alistair Hopkins | last post by:
Hi, I am trying to write a generic audit-trail trigger function which will record changes on a field-by-field basis to a single table for all audited tables. However, I find that I can only...
12
by: veaux | last post by:
Question about joins in queries. I have 2 tables with a field called "ID". Table 1 Rec1 = Jan12FredFlintstone Rec2 = Feb01WilmaRubble Table 2 Rec1 = Jan12BarneyRubble Rec2 = Mar03SamSlate
2
Chittaranjan
by: Chittaranjan | last post by:
Hi All, I have a problem and hope I can get the better solution from here. I have a form written in HTML and I need to write that in perl so the main problem I am facing is that I need to...
3
by: atiq | last post by:
I am tyring to join two join fields together in a Report in the follwoing format: Date: Time: i have attempted this using the following code, but doesnt work: ="Date:" & " " & & " " &...
5
theaybaras
by: theaybaras | last post by:
Hi, I'm still getting a feel for some things in Access. What I have is a database of scientific articles. I have an unbound memo field in which I want to have the APA style citation concatenated...
12
by: stretchtack | last post by:
Hey Troops, I'm not sure if this is allowed, but I'm trying to keep my SP generic and reduce duplicate code. Depending on a condition, I need to JOIN to different tables, which slightly changes...
4
by: tasawer | last post by:
Hi, in the underlying query of a form and to the field 'manu_number', I have added the criteria, Like "*" & !! & "*" this lists all records on startup, except, if manu_number field is blank...
1
by: Alisha Cane | last post by:
How do you calculate the sum of two fields in a data base (access)-to produce a report?
1
by: Steve Cooke | last post by:
Hi, I'm new to SQL and been trying different queries on a test database. I have been having issues with a join command. In my DB there are several tables from our current help desk ticketing...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.