Create query from ODBC linked Tables

[Guest]

Hi,

I have two Linked ODBC tables, and I need to join them in a query, the
problem is that the linked fields have different formats.
Table 1 field is numeric ans table 2 field is text.

The tables are from COBOL database, so changing the format from de tables is
not an option...

Could anyone help me???


--
Thanks,

Sorry if my english isn''t correct, but I''m from Potugal

Emanuel Violante Galeano

 

[Rick Brandt]

Hi,

I have two Linked ODBC tables, and I need to join them in a query, the
problem is that the linked fields have different formats.
Table 1 field is numeric ans table 2 field is text.

The tables are from COBOL database, so changing the format from de
tables is not an option...

Could anyone help me???

You can join a field in the first table to an *expression* using the field from
the second table, bit you can't create such a query in the graphical query
builder. Build the query you want with a standard join and then switch to SQL
view and change the join to something like...

SELECT *
FROM Table1
INNER JOIN Table2
ON CStr(Table1.FieldName) = Table2.FieldName

Such a query will not be efficient because of the conversion required because it
will eliminate the use on an y indexes on that field.

This is a case where writing a Passthrough query to allow the server to perform
the query (particularly the join) will likely perform much better. Generally
you don't want to create local join queries using ODBC linked tables as inputs
unless the tables are fairly small.

 

[Guest]

You can join a field in the first table to an *expression* using the field
from

the second table, bit you can't create such a query in the graphical query
builder. Build the query you want with a standard join and then switch to SQL
view and change the join to something like...

SELECT *
FROM Table1
INNER JOIN Table2
ON CStr(Table1.FieldName) = Table2.FieldName

Thanks verry much, that's almost what I want, but i have a little problem...

my Table2.FieldName is 000000 format, so for exemple the value 26 appears as
000026 and 95029 appears as 095029...

Is it possible to "ignore" the left zeros, and return the 26 and 95029 values?




--
Thanks,

Sorry if my english isn''t correct, but I''m from Potugal

Emanuel Violante Galeano

 

[Rick Brandt]

Thanks verry much, that's almost what I want, but i have a little
problem...

my Table2.FieldName is 000000 format, so for exemple the value 26
appears as 000026 and 95029 appears as 095029...

Is it possible to "ignore" the left zeros, and return the 26 and
95029 values?

Well you can go the other way and convert the text field to a number, but
that is more dangerous because you might encounter a value that cannot be
converted to a number. If the text field is always 6 characters long then
you can replace...

CStr(Table1.FieldName)
with
Format(Table1.FieldName, "000000")