CREATE VIEW dbo.uv_OpenOrderHeaders
AS

SELECT     dbo.OrderHed.CustNum, dbo.OrderHed.OrderDate, dbo.OrderHed.OrderNum, dbo.OrderHed.PONum, dbo.OrderHed.NeedByDate, 
                      dbo.OrderHed.RequestDate, dbo.SalesRep.Name, dbo.ShipVia.Description AS ShipVia, dbo.Terms.Description AS Terms, dbo.OrderHed.OpenOrder, 
                      dbo.OrderHed.OrderComment, dbo.OrderHed.Character01 AS TopCap, dbo.OrderHed.Character02 AS WSFinish, dbo.OrderHed.Character03 AS WSTrim, 
                      dbo.OrderHed.Character04 AS PanelFab, dbo.OrderHed.Character05 AS FlipFab, dbo.OrderHed.Character06 AS TackFab, 
                      dbo.OrderHed.Character08 AS Job, dbo.OrderHed.Character10 AS CSR, 
                      dbo.OrderHed.CheckBox01 AS CustAppr, dbo.OrderHed.ShortChar01 AS Class, dbo.OrderHed.ShortChar02 AS Paint, 
                      dbo.OrderHed.ShortChar03 AS Base, dbo.OrderHed.ShortChar04 AS ShelfPaint, dbo.OrderHed.ShortChar05 AS ShelfStyle, 
                      dbo.OrderHed.ShortChar06 AS Electric, dbo.OrderHed.ShortChar07 AS RailStyle, dbo.OrderHed.ChangeDate, dbo.OrderHed.ChangeTime, 
                      CASE dbo.OrderHed.UseOTS WHEN 0 THEN dbo.ShipTo.Name ELSE dbo.OrderHed.OTSName END AS ShipName, 
                      CASE dbo.OrderHed.UseOTS WHEN 0 THEN dbo.ShipTo.Address1 ELSE dbo.OrderHed.OTSAddress1 END AS ShipAdd1, 
                      CASE dbo.OrderHed.UseOTS WHEN 0 THEN dbo.ShipTo.Address2 ELSE dbo.OrderHed.OTSAddress2 END AS ShipAdd2, 
                      CASE dbo.OrderHed.UseOTS WHEN 0 THEN dbo.ShipTo.Address3 ELSE dbo.OrderHed.OTSAddress3 END AS ShipAdd3, 
                      CASE dbo.OrderHed.UseOTS WHEN 0 THEN dbo.ShipTo.City ELSE dbo.OrderHed.OTSCity END AS ShipCity, 
                      CASE dbo.OrderHed.UseOTS WHEN 0 THEN dbo.ShipTo.State ELSE dbo.OrderHed.OTSState END AS ShipState, 
                      CASE dbo.OrderHed.UseOTS WHEN 0 THEN dbo.ShipTo.ZIP ELSE dbo.OrderHed.OTSZIP END AS ShipZip                      
FROM         dbo.OrderHed LEFT OUTER JOIN dbo.ShipTo ON dbo.OrderHed.CustNum = dbo.ShipTo.CustNum AND dbo.OrderHed.ShipToNum = dbo.ShipTo.ShipToNum LEFT OUTER JOIN
                      dbo.Terms ON dbo.OrderHed.TermsCode = dbo.Terms.TermsCode LEFT OUTER JOIN dbo.SalesRep ON dbo.OrderHed.SalesRepList = dbo.SalesRep.SalesRepCode
                      LEFT OUTER JOIN dbo.ShipVia ON dbo.OrderHed.ShipViaCode = dbo.ShipVia.ShipViaCode
WHERE        dbo.OrderHed.OpenOrder = 1

quote:

---

Originally posted by SBinVA

The SELECT DISTINCT worked but in my opinion it's more of a workaround and there's something not right with the query.

mmarovic - I'm not sure I follow you, how to I do that?

---

select ...,
       st.Name as shipName,
       ...
  from dbo.orderHed oh
  join dbo.shipto   st on oh.custNum = st.custNo and oh.shipToNum = st.shipToNum
  ...
  where oh.openOrder = 1 and
        oh.useOTS = 0
union
  select ...,
         oh.OTSName as shipName,
         ...
  from dbo.orderHed oh
  left join dbo.terms t on ...
  ...
  where oh.openOrder = 1 and
        oh.useOTS = 1

dbo.OrderHed LEFT OUTER JOIN dbo.ShipTo ON dbo.OrderHed.CustNum = dbo.ShipTo.CustNum AND dbo.OrderHed.ShipToNum = dbo.ShipTo.ShipToNum

quote:

---

Originally posted by SBinVA
mmarovic - Thanks for your useful suggestion, I hadn't thought of using UNION but will give it a try. All of the keys I use out of OrderHed are unique to the foreign key in the joining table. However, it is possible for the key to be null in OrderHed, hence the outer joins.

Logically the only place where this could return records at a precise 2:1 ratio is something to do with the CASE statement and/or the join to the ShipTo table. In looking at it, is it possible that the two-part key from OrderHed (CustNum and ShipToNum) to the ShipTo table is causing this? Is this syntax correct?

dbo.OrderHed LEFT OUTER JOIN dbo.ShipTo ON dbo.OrderHed.CustNum = dbo.ShipTo.CustNum AND dbo.OrderHed.ShipToNum = dbo.ShipTo.ShipToNum

Regardless, thanks for taking your time to look at it. I truly appreciate it.

SB

---