Data Connectivity: linking two tables with different data types

linking two tables with different data types

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14468
Printed Date: 22 Apr 2025 at 4:06pm

Topic: linking two tables with different data types

Posted By: TEOA
Subject: linking two tables with different data types
Date Posted: 23 Sep 2011 at 2:15am

Im trying to link two dfferent tables with different data types.

Oper_nbr in one table shown as 05 10 15 20 etc but stored as text

and

OPER_NBR in another table shown as 05 10 15 20 etc but stored as a number

im struggling, it works in access using this code

SELECT dbo_SEMIFINISHED_COST.*
FROM dbo_SEMIFINISHED_COST INNER JOIN dbo_wireact ON cstr(dbo_SEMIFINISHED_COST.OPER_NBR)= dbo_wireact.oper_nbr;

I tried creating a command (using add command section )

using this code, however it still wont link the data.

im bound to have made a school boy error, so rather than pulling my hair out any more, thought id ask the experts for your advice :-)

Replies:

Posted By: TEOA
Date Posted: 27 Sep 2011 at 12:18pm

Update:

I think im about there now, just got to test tomorow, heres what I had to do. I had to write the whole thing in SQL not just the two tables i wanted to link:

SELECT dbo_wireact.crtd_date, dbo_wireact.mo_nbr, dbo_wireact.prod_desc, dbo_wireact.mach_nbr, dbo_ROUTING_OPERATION.COST_CENT_NBR, ([RUN_RATE]*1000)/60 AS [COSTED MPM], [dbo_wireact]![good_qty]/([dbo_wireact]![run_hrs]*60) AS [ACTUAL MPM], [ACTUAL MPM]/[COSTED MPM]*100 AS diff, dbo_wireact.good_qty, [dbo_wireact]![scrp_setup]+[dbo_wireact]![scrp_ip]+[dbo_wireact]![scrp_cut] AS SCRAP, dbo_users.forename, dbo_users.surname, dbo_ROUTING_OPERATION.COST_TYPE
FROM ((dbo_wireact INNER JOIN dbo_PRODUCT ON dbo_wireact.prod_desc = dbo_PRODUCT.PROD_DESC) INNER JOIN dbo_ROUTING_OPERATION ON (dbo_PRODUCT.PROD_ID = dbo_ROUTING_OPERATION.PROD_ID) AND (dbo_wireact.wcnt_nbr = dbo_ROUTING_OPERATION.COST_CENT_NBR) AND (dbo_wireact.oper_nbr = CSTR(dbo_ROUTING_OPERATION.OPER_NBR))) INNER JOIN dbo_users ON dbo_wireact.oper_id = dbo_users.user_id
WHERE (((dbo_wireact.crtd_date) Between #1/1/2009# And #1/1/2010#) AND ((dbo_ROUTING_OPERATION.COST_CENT_NBR)="523" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="520" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="546" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="502" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="505" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="509" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="503") AND ((dbo_ROUTING_OPERATION.COST_TYPE)="B"));

Unfortunatly when I came to try this I forgot that two of the tables are comming from different ODBC sources.

so im going to add this to the top

FROM [odbc;dsn=datasource;database=pubs;Username= <username>;PWD=<strong password>].ROUTING_OPERATION,[ [odbc;dsn=datasource;database=pubs;Username= <username>;PWD=<strong password>].wire_act

ill let you all know how I get on