joining product codes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

joining product codes

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2009-11-17 : 10:51:58

Hi am having a problem with joining product codes where the data i received in an extract has 000237 while in my product table the prod_code is 237.

so when i try to join the ext_tbl to the prd_tbl via the prod_code i get an error. because they don't match. any ideas no how to table this ?

ext_tbl prd_tbl
000237 237

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-17 : 10:56:28

If values for prod_code in both tables are always numeric then you can convert them:

...
join ext_tbl on convert(int,ext_tbl.prod_code) = convert(int,prd_tbl.prod_code)
...

No, you're never too old to Yak'n'Roll if you're too young to die.

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-19 : 06:25:24

IF the values in both the tables are numeric, you can try the following..

SELECT *
FROM prd_tbl
JOIN ext_tbl ON ROUND(ext_tbl.prod_code) = ROUND(prd_tbl.prod_code)

Balaji.K

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-19 : 07:23:35

or

join ext_tbl on ext_tbl.prod_code like prd_tbl.prod_code+'%'

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-19 : 07:31:57

quote:
Originally posted by madhivanan

or

join ext_tbl on ext_tbl.prod_code like prd_tbl.prod_code+'%'

Madhivanan

Failing to plan is Planning to fail

Really?


DECLARE @ext_tbl TABLE ([prod_code] VARCHAR(255))
DECLARE @prd_tbl TABLE ([prod_code] VARCHAR(255))

INSERT @ext_tbl SELECT '00256'
INSERT @prd_tbl SELECT 256

SELECT *
FROM
	@prd_tbl prd_tbl
	join @ext_tbl ext_tbl on ext_tbl.prod_code like prd_tbl.prod_code+'%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-19 : 07:42:05

quote:
Originally posted by Transact Charlie

quote:
Originally posted by madhivanan

or

join ext_tbl on ext_tbl.prod_code like prd_tbl.prod_code+'%'

Madhivanan

Failing to plan is Planning to fail

Really?

DECLARE @ext_tbl TABLE ([prod_code] VARCHAR(255))
DECLARE @prd_tbl TABLE ([prod_code] VARCHAR(255))

INSERT @ext_tbl SELECT '00256'
INSERT @prd_tbl SELECT 256

SELECT *
FROM
@prd_tbl prd_tbl
join @ext_tbl ext_tbl on ext_tbl.prod_code like prd_tbl.prod_code+'%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

I thought it has leading zeroes. In this case


SELECT *, ext_tbl.prod_code ,prd_tbl.prod_code
FROM
	@prd_tbl prd_tbl
	join @ext_tbl ext_tbl on 
ext_tbl.prod_code*1 =prd_tbl.prod_code

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-19 : 07:47:09

Interesting.

Is there any performance difference between an implicit cast and an explicit one?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-19 : 07:55:04

quote:
Originally posted by Transact Charlie

Interesting.

Is there any performance difference between an implicit cast and an explicit one?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

IMO, it would be same. The only difference is less typing

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-19 : 08:15:42

That, and Implicit casting = naughty.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources