July 9, 2009 at 7:48 am
I am supposed to create a join condition between two tables.
1) This one with nvarchar data type and has real number like ...9944156
2) This one is varchar and has the same field with zeros added up in from so it becomes 10 char..
for instance the same field shown in (1) becomes 0009944156.
How can I compare these two to make it a.order_no=b.order_no
There is no other field to really use in the join condition. Please help
thanks
Su
July 9, 2009 at 8:04 am
How big are the tables? Are the lengths of the numbers in the table that doesn't have leading zeroes consistent?
Three thoughts, you can convert both to int with this CONVERT(int,a.order_no)=CONVERT(int,b.order_no) this is pretty explicit but for a large set of data in the tables it will be a COSTLY operation. THe other thought is dependent in the length of the table without leading zeroes. If the numbers are always 7 digits then you can do something like '000' + a.order_no=b.order_no but if it doesn't then you need do something like RIGHT('000' + a.order_no,10)=b.order_no. Last idea, create a calculated column on the table with the leading zeroes, this column takes those zeroes off and you join on that column, the calculated column should have the Is Persisted set to Yes.
Either of the first 2 ways you definitely want to limit both tables to joining on as few records as possible, the third way would be a natural join so nothing special needs to be done.
Also keep in mind I think you will have to change the join structure a little
SELECT a.order_no, b.order_no
FROM sometable a, someothertable b
WHERE CONVERT(int,a.order_no)=CONVERT(int,b.order_no)
I don't think you can do these operations in the ON clause, but I could be wrong, I've not tried it..
CEWII
July 9, 2009 at 8:24 am
Thank you,
Table a is not that big, Table b has hhhhhhuge data.. I am only allowed to play around with A, I can not do anything with the data or datatype in order_no of Table B.
Also the number of digits is not consistent in A it is sometimes 5, sometimes 6 or whatever. This is one of my main concerns.
July 9, 2009 at 8:52 am
Then the RIGHT('00000' + a.order_no,10)=b.order_no comes to mind. I also thought of something else, but I'm not sure I would do it, you could create a table that had the 10 digit number as its key and a converted to non 1o digit number as a secondary column, you could populate it with a trigger on the master table on the insert operation, new record in one adds new record in the other. Then you join through it a.order_no = c.TenDigitOrder_no and c.OnlyOrder_no = b.order_no.
I would have to stew on this for a while before I made a decision, it would totally work but the trigger might degrade performance..
Ok, my big recommendation is to add a computed (persisted) column on table "a" that is essentially
RIGHT('0000000000' + order_no,10)
You will join on this column and all the other stuff that uses the other fields shouldn't even care about it.
CEWII
July 9, 2009 at 9:37 am
Thank you,
It helped.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply