How do I left outer join on different different data types?

[#1]

I have a Crystal Report where I need to do a left outer join to another table. I need to display contents from the aropen table and only those values from the second table (inv-hdr-his) when there is a match. However, I'm trying to connect on two different data types. The aropen table has a 12 character string field called "doc-no" that I am trying to link to a 9 digit integer field called "inv-num".

I found a post on a Sql Server blog that said that if I place the sql statement in the Command field, it will work, but so far I have only received syntax errors. Please help, my sql is very limited and strung together by what I can find online.

Current sql query:

SELECT "aropen1"."comp-no", "aropen1"."balance", "aropen1"."doc-no", "aropen1"."doc-date", "aropen1"."due-date", "aropen1"."po", "aropen1"."amount", "aropen1"."doc-type", "aropen1"."descrip", "aropen1"."doc-pre", "aropen1"."cust-id", "aropen1"."stat", "aropen1"."freight", "inv_hdr_his"."invoice-num" FROM "PAPER"."PUB"."aropen" "aropen1" LEFT OUTER JOIN "inv-hdr-his" "inv-hdr-his" ON "aropen1"."doc-no"= CAST("inv_hdr_his"."invoice-num" AS char(12)) AS "doc-no" ) WHERE "aropen1"."comp-no"=""inv_hdr_his"."comp-no"

Current error:

Failed to retrive data from the database. Details: 42000:[DataDirect}{ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error (7587) [Database Vendor Code: -20003]

[former_member681242]

Hi Cathy,

that does not look like a problem with the join, you should double check Error 42000.
Can you do a simple select on both of the tables without a problem (and without the join) ?

Kind regards

Tobias