May 11, 2010 at 4:27 am
Hi all,
am trying to join 3 tables here, when i query the requested columns seperately it works fine..but when i run them in a join gives me an error :angry: (I think am doing something wrong while joining the tables)
Error is 'Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.'
Query is as follows;
select
a.MODAT [Order date],
a.MBUSU [Busu],
a.MORDN [Order],
b.PO71 [J_PO],
a.MLINE [Line- sales],
a.MPROD [Product],
a.MQTY [JQty],
a.MSQT [J O Qty],
a.MSAL [JSales],
a.MTGPB [JGP],
c.Order [KOrder],
c.part [KProduct],
c.branch [KBranch],
c.Line [KLine],
c.OStatus [KStatus],
c.AdviceNo [KNumber],
c.ADate [KDate],
c.status [KStatus]
from order a
left outer join OrderRes b
on a.CONO = b.CONO
left outer join OrderPart c
on b.PO71 = c.LinkfileLineSeq
where a.CONO = '10' and c.Branch= '43'
can anyone please help me in this:unsure:
Thanks
Shree
May 11, 2010 at 4:31 am
At a guess, the likely reason is that one of the columns that you are joining on contains Char values and the other column in the join conatins Numerics.
Check the contents on the join columns to see if the data types match.
May 11, 2010 at 6:12 am
-Shree (5/11/2010)
where a.CONO = '10' and c.Branch= '43'
try where a.CONO = 10 and c.Branch= 43
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 11, 2010 at 7:49 am
Thanks for the replies 🙂
the problem was with the on clause
"on b.PO71 = c.LinkfileLineSeq"
PO71 was varchar while LinkfileLineSeq was decimal!!
the query did started running..but it is taking ages!!
Seems like its gonna be a long day!!!!
May 12, 2010 at 6:03 am
-Shree (5/11/2010)
the query did started running..but it is taking ages!!Seems like its gonna be a long day!!!!
Also check the exec plan to see how indexes are playing there.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 13, 2010 at 8:21 am
Since your WHERE clause specifies a value for the 'c' table, your LEFT JOINs effectively become INNER JOINs, in that the value from the 'c' table MUST exist. You may see some performance improvement from changing to INNER joins.
May 13, 2010 at 9:09 am
Hi all,
Yes, i did that!!
and have also added one more join but still it was taking bit longer
so stored the 1st 3 joins result into a temp table and joined it with 4th table..and guess what..and it is working like a treat!!
Thanks for all the replies 🙂
- Shree
May 13, 2010 at 11:34 pm
-Shree (5/13/2010)
so stored the 1st 3 joins result into a temp table and joined it with 4th table
Try to add indexes on temp table( i would go with clustered one ) then go for join.you can get more optimal results.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply