Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: linking two tables with different data types Post Reply Post New Topic
Author Message
TEOA
Newbie
Newbie
Avatar

Joined: 05 Dec 2010
Location: United Kingdom
Online Status: Offline
Posts: 3
Quote TEOA Replybullet Topic: linking two tables with different data types
    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 :-)
 
 
IP IP Logged
TEOA
Newbie
Newbie
Avatar

Joined: 05 Dec 2010
Location: United Kingdom
Online Status: Offline
Posts: 3
Quote TEOA Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.