Join on columns with different data types

[crystalnewbie]

I'm trying to do an inner join between two tables from two separate database but the column i'm doing a join on have different data types.

I went ahead and tried it and CR8.5 gave me a message saying more than one datasource has been used and to make sure that no sql expression is added and no server side grouping is performed.

I'm getting over a million rows instead of 14 records. I think it did a cartesion join.

Using sql syntax, i normally do a data type conversion first on one of the join fields. Is there anything similar to CR 8.5?

 

[lbass]

I'm not sure whether a SQL solution is possible here, but another approach would be to use one table in the main report and the other in a subreport, and then link the subreport to the main report on a formula that converts the field of one table to match (and link to) the field in the other.

-LB

 

[crystalnewbie]

I tried doing what you suggested and didn't quite get the result that I wanted.

Here's what I did:
On main report, created a variable that converted the column i'm doing a join on (originally an integer type) to string. I placed it into the report and its got commas in it (eg. 150,102).

I then created a subreport with the join field as one of the report fields and linked it to the variable I created and placed it on the details section. It didn't do a cartesian join this time but the subreport does not contain any data.

Even if the data showed up, can I used the data in the subreport as if it was a part of the main report (ie. be able to group and do summary on it?

 

[lbass]

If the value in the subreport has no commas, then change the formula in the main report to:

totext({table.number},0,"")

Whichever table you want to do the grouping on should be in the main report. To use a value in the subreport in calculations in the main report, you would need to create a shared variable in the subreport like:

whileprintingrecords;
shared numbervar amt := {subtable.amt};

You would place this on the subreport and then in the main report, in a section somewhere below where the subreport is located, you would add another formula that you created in the main report that references the shared variable and does a calculation like the following grand total accumulation:

whileprintingrecords;
shared numbervar amt;
numbervar grandtot := grandtot + amt;

Then when if you wanted to display the grand total, you would place a display formula in the report footer:

whileprintingrecords;
numbervar grandtot;

-LB