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

 All Forums
 Site Related Forums
 Article Discussion
 outer join conversion from 2000 not working the sa

Author  Topic 

Starting Member

2 Posts

Posted - 2014-08-23 : 07:42:32
can someone help me to convert these joins. I have tried and tried, but cannot get the same results that this query produces when I try to convert to ansi OUTER joins: basically, I need a row for every order that is in Order and/or EA or and one row for each of the 3 returned by cdef = 1 (there are 3 values so if there is an order in Order with no matching OrderNumber in EA, I should get 3 rows back for each value in cdef (alt1, alt2, ex)

o.OrderNumber, o.companyname Company, cdef.shortname RegClass,
isnull(xa.Allotment,0) Allotment,
IsNull((select sum(qty) from tab_item where OrderNumber = xa.OrderNumber and ItemCode = cdef.Purchase_Allotment_Item and ItemStatus = 'A'),0) Purchased,
IsNull((select count(*) from tab_reg where regclass = xa.regclass and OrderNumber = xa.OrderNumber),0) Used

Order o,
EA xa,
ClassDef cdef

(cdef.exhibitor = 1)
and xa.regclass =* cdef.shortname
and o.ordernumber *= xa.ordernumber
and o.ordernumber <> 30000
and o.OrderType = 1

Order By

- Advertisement -