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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 outer join conversion from 2000 not working the sa
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 08/23/2014 :  07:42:32  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000