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

[Former Member]

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]

[DellSC]

It looks like the problem is in the Where clause - there's an extra quote. There's another problem in your Cast statement - you don't need the "as" part of it.

Also, your Where clause looks like it could also be part of the join instead. So, I would change the SQL to

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))
	AND "aropen1"."comp-no" = "inv_hdr_his"."comp-no"

Also, you should read this blog to get more information about working with Commands:

https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell

[Former Member]

This was very helpful and I now don't get errors regarding syntax, however I was able to discover that I can't cast into numeric because this field is sometimes alphanumeric. Is there a way to join on a conditional cast and left join on the alphanumeric data?

Example:

AR10000 = Null

175572 = 175572

[DellSC]

Instead of casting the String (inv_hdr_his"."invoice-num") to a Number, you could cast the Number ("aropen1"."doc-no") to a String. The left join would then match only the numeric strings in inv_hdr_his"."invoice-num" and filter out the alpha-numeric strings.

-Dell

[Former Member]

Hi Dell, I was able to get it to work like a charm. Turns out the exact character count was 24, so once I cast to that amount the link worked. Thanks for all your help! Final formula wound up being:

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", "inv_hdr_his"."ord-num", "inv_hdr_his"."cust-po" FROM "PAPER"."PUB"."aropen" "aropen1" LEFT OUTER JOIN "PAPER"."PUB"."inv-hdr-his" "inv_hdr_his" ON "aropen1"."doc-no" = CAST("inv_hdr_his"."invoice-num" as char(24)) WHERE "aropen1"."comp-no"=1 AND "aropen1"."stat"<>'C' ORDER BY "aropen1"."doc-date", "aropen1"."doc-no"

[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