Join problem - 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 2008 Forums

Transact-SQL (2008)

Join problem

Author

Topic

dosteroid
Starting Member

29 Posts

Posted - 2011-11-14 : 05:56:57

Hi

I have a statement where I'm joining two two tables
left join F4311 on F0911.GLPO=F4311.PDDOCO (document number)
but I have found out that in my fact tables these two doesn't match...

When year is 2009, then numbers in PDDOCO is 9xxxxxx and in GLPO it's 09xxxxxx

And again for 2008 then numbers in PDDOCO is 8xxxxxx and in GLPO it's 08xxxxxx

How can I concatenate a '0' to PDDOCO when year < 2010 and join F0911 with that value...

Like left join F4311 on F0911.GLPO='0'+F4311.PDDOCO when year < 2010 else F0911.GLPO=F4311.PDDOCO (document number)

Hope it makes sense...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-14 : 06:27:01

left join F4311 on F0911.GLPO = case when year < 2010 then '0' else '' end + F4311.PDDOCO

It won't use an index though.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 06:30:51

if its just leading zero that matters, why not convert it to int and do join?

on F0911.GLPO*1=F4311.PDDOCO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dosteroid
Starting Member

29 Posts

Posted - 2011-11-14 : 07:12:45

Seems to work, but it slows down my query alot(with 10minutes) because it wont use an index I assume...? Anyway to sort this?

dosteroid
Starting Member

29 Posts

Posted - 2011-11-14 : 07:14:29

I cannot convert to int cause Im concatenating the GLPO with a text field somewhere else in my query, I just haven't mentioned it here...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 07:17:28

quote:
Originally posted by dosteroid

I cannot convert to int cause Im concatenating the GLPO with a text field somewhere else in my query, I just haven't mentioned it here...

nope convert it to int only in join not in select where you do concatenation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dosteroid
Starting Member

29 Posts

Posted - 2011-11-14 : 07:17:32

(GLPO+'.'+GLPDCT+'.'+GLPKCO+'.'+cast(GLLNID as varchar(10))) end as PurchaseOrder

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 07:22:48

quote:
Originally posted by dosteroid

(GLPO+'.'+GLPDCT+'.'+GLPKCO+'.'+cast(GLLNID as varchar(10))) end as PurchaseOrder

not here
cast it only in join condition
ie. at

left join F4311 on F0911.GLPO*1 = F4311.PDDOCO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dosteroid
Starting Member

29 Posts

Posted - 2011-11-14 : 07:24:05

Hmm just found out it's not consistant... There are actually some records on PDDOCO that have the zero in front...
So now Im thinking it might be better to substract the zero if exists from GLPO instead?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 07:29:29

quote:
Originally posted by dosteroid

Hmm just found out it's not consistant... There are actually some records on PDDOCO that have the zero in front...
So now Im thinking it might be better to substract the zero if exists from GLPO instead?

in such case what would be corresponding values in GLPO ? it will also have leading 0's?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dosteroid
Starting Member

29 Posts

Posted - 2011-11-14 : 07:33:59

Oops it's me messing around now... PDDOCO is consistant, there is never a zero infront when year < 2010
but in GLPO where year < 2010 then it can be both 0xxxxxxx or xxxxxxx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 07:36:26

quote:
Originally posted by dosteroid

Oops it's me messing around now... PDDOCO is consistant, there is never a zero infront when year < 2010
but in GLPO where year < 2010 then it can be both 0xxxxxxx or xxxxxxx

so only in those cases you need to add 0 to PDDOCO? ALl other cases there're consistent?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dosteroid
Starting Member

29 Posts

Posted - 2011-11-14 : 07:41:49

Yeah I would need to add a zero to PDDOCO when GLPO like '0%' (and year < 2010)

dosteroid
Starting Member

29 Posts

Posted - 2011-11-14 : 07:53:12

I assume this would work, but it's not ideal, cause the query is taking ages...?

left join F4311 on F0911.GLPO = case when PDDGL < 110001 and GLPO like '0%' then '0' else '' end + F4311.PDDOCO

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-14 : 08:24:56

You could try

myquery
left join F4311 on
(F0911.GLPO = F4311.PDDOCO and year >= 2010)
or
(F0911.GLPO = '0' + F4311.PDDOCO and year < 2010)

Could also split into two subqueries with a union - then you could optimise separately.
You might also consider doing this join before the main query and using a temp table. Best of course is to have a column in the correct format - maybe an indexed view or - presuming that pre 2010 is static data then a separate table with those values and the PK.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources