Question Re: How do I left outer join on different different data types? in Technology Q&A

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

Former Member — Fri, 23 Apr 2021 03:00:31 GMT

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]

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

former_member681242 — Fri, 23 Apr 2021 05:20:43 GMT

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

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

DellSC — Fri, 23 Apr 2021 13:28:32 GMT

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

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

Former Member — Tue, 27 Apr 2021 23:31:02 GMT

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

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

DellSC — Wed, 28 Apr 2021 15:57:45 GMT

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

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

Former Member — Thu, 13 May 2021 01:22:19 GMT

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"