cancel
Showing results for 
Search instead for 
Did you mean: 

Linking tables with different data types

Former Member
0 Kudos

Hi,

I have an app that exports data to AccPac.  When it does this, it converts the Customer ID from a numeric to a string.

I there a method to link these two in Crystal (ver 14), even though they're two different data types?

Alternately, any tips on a SQL command that can handle this?

Matt

Accepted Solutions (1)

Accepted Solutions (1)

JWiseman
Active Contributor
0 Kudos

hi Matt,

a command would probably work...but what's the back end to the accpac system? oracle, sql server?

depending on what the back end is, you could do something like

WITH
T1 AS
(
SELECT TO_NUMBER(textfield) AS NOWNUM, * FROM TABLE1
),
T2 AS
(
   SELECT * FROM TABLE2
)

SELECT * FROM T1, T2
WHERE T1.NOWNUM = T2.NUMBERFIELD

i hope this helps,

jamie

Former Member
0 Kudos

Hi, thank you Jamie,

I guess I really zoned out on that one, and forgot some additional details

Both DB are MS 2008,      but...

They're in two separate instances on two servers (same network).

I've created a Linked Server to the AccPac server from my Production server, and queries work very tidy (currently using Cast as Int), this is gonna be a report that I will certainly want to tidy up in Crystal.

So now I'm wondering what kind of glitchery I'm I might possibly run into using an ODBC connector...  Your suggestion certainly looks adaptable.  I like the uniqueness of the link field, where the field is only used for linking, and all the original data keeps its native data type.

Any thoughts are welcome!

Matt

JWiseman
Active Contributor
0 Kudos

i've never used a linked server with sql server, but found this

SELECT local.name AS LocalLogins, linked.name AS LinkedLogins

FROM master.sys.server_principals AS local

LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked

    ON local.name = linked.name ;

from here and in theory defining your second / linked table as such should work aok inside a command object....you'll have to let us know...i don't have sql server.

Former Member
0 Kudos

Will do, Jamie.

Thank you.

Answers (0)