calculated key value for union, is it possible? 2

[1DMF]

Hi,

I have a 3rd party DB system I'm trying to integrate, they have a table structure where they call the key a composite link which is a calculation...

table2.CaseId = (table1.CompanyId x 1,000,000 + table1.CaseId)

Is it possible to do a join where it isn't a simple case of table1.CaseId = table2.CaseId

Is it only possible to create unions where table1.column = table2.column ?

Cheers,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhulbert]

That sort of join should work, Not exactly pretty and it does tend to slow the query down a fair bit.

if you had the option to add a column to tabel 1 and put the key into it it would work a lot beetter but it whould work this way.

also be carefult aht the 2 data types are the same I've seen a few joins like this where they have failed beacuse of differign data types.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[1DMF]

could you give me an example of the syntax.

I was assuming it wasn't as simple as ...

SELECT x,y,z FROM table1 JOIN table2 ON table2.CaseId = (table1.CompanyId x 1,000,000 + table1.CaseId)

or is it?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[gmmastros]

It is. You need to use valid syntax, like this:

SELECT x,y,z 
FROM   table1 
       JOIN table2 
         ON table2.CaseId = (table1.CompanyId * 1000000 + table1.CaseId)

If you get bad performance from this, which is likely if you have really big tables, let me know. What you could do is create a computed column in table1 and then put an index on it which will improve performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

cool, thanks guys, the script will form part of an overnight job and there won't be too may records for each pass so I am hoping performance isn't going to be an issue.

I could always just get a separate recordset based on the calucalation if it is an issue, but was hoping to join as many tables in one hit as possible.

I'm going to have to pass the record and transform / map across to our DB schema anyway.

Really appreciate the help!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts