Join expression not supported

[rodfuentes]

I'm trying to do a left join on two fields which don't have the same amount of characters.

I'm using the following:

SELECT TABLE1.BKGNBR, TABLE2.DOCNBR
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.BKGNBR = LEFT(TABLE2.DOCNBR,9);

When doing this I'm getting JOIN expression not supported.

I really need to take all records on TABLE1 and only the records in TABLE2 taking the first 9 characters to make this join.

Can you help me?

[zmbd]

why?
There is a very serious design flaw in your database if you are attempting this method of matching...

Your sentence might look like:

SELECT TABLE1.BKGNBR, TABLE2.DOCNBR 
FROM TABLE1, TABLE2
WHERE ((Left$([BKGNBR],9)=Left$([DOCNBR],9)));

Although I've not ran this so it may not return exactly what you want....

What I would do, is create two queries with calculated field that returns just the left-9, and then make the join using these queries... you can do this all in one go; however, this might be easier to follow.

How is this different from your other thread: http://bytes.com/topic/access/answer...t-lengths-data

[rodfuentes]

Thank you for your answer.
I had lost track of that thread (I apologize I'm very new at this).
It is not different at all, the result I'm trying to achieve is the same.
However the tables were created a long time ago and therefore I don't think they considerated communicating these 2 tables together even though they share crucial data.

Now, I have tried creating queries with calculated fields as you suggested but the response time is way over 180 secs which I'm trying to optimize.

Any ideas?

[zmbd]

I don't see any solution for you beyond what I've offered other than the most obvious, and perhaps not even possible for you.... start from scratch and rebuild the database properly.

I know the pain... I'm in a company now that due to regulations has to keep the old legacy databases, so as they've grown and moved from one to the next, the old mules have stayed hooked to the train... requiring that often the data is "transferre d" in batches from one system to another using mapping, triggers, and code to get things in the right places.

That is perhaps what you might have to do, pull the information from the older system to the new, properly designed database and then push your data back out to the old one.... sort of a kludge I know - but the best I have for you right now.

BTW: Go to your profile settings, there you can set how emails are sent to you when threads that you subscribe to are updated and when you receive PM in your Bytes.com account.

BOL
-z