• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 42
  • Last Modified:

Problem adding a Top 1 join

I have a complex script which is pulling from various tables for an internal page.

The new requirement is to join the OrderAudit Table with only the latest time and date (last activity) for a particular order.

BEGIN     TRAN
    Select
                    Ltrim(Rtrim(H.ord_no)) AS TrimmedOrderNo
                  , a1.aud_dt
                  , Ltrim(h.cus_no)                 AS CustomerNo
                  , C.cmp_name                      as CustomerName
                  , Convert(varchar(50),DD.Account) as CustomerGuid
                  , h.ship_to_name
                  , h.ship_to_addr_1 AS Address1
                  , h.ship_to_addr_2 AS Address2
                  , h.ship_to_addr_3 AS Address3
                  , h.ship_to_addr_4 AS Address4
                  , h.ship_to_city   AS City
                  , h.ship_to_state  AS State
                  , CASE
                                    When h.status='C'
                                                    then 'Credit Hold'
                                    WHEN h.status = '1'
                                                    THEN 'Booked'
                                    WHEN h.status = '4'
                                                    THEN 'Picking Ticket Printed'
                                    WHEN h.status = '5'
                                                    THEN 'Picked'
                                    WHEN h.status = '6'
                                                    THEN 'Pack Slip Printed'
                                    WHEN h.status = '7'
                                                    THEN 'Shipped'
                                    WHEN h.status = '8'
                                                    THEN 'Billed'
                                                    ELSE 'Invoice Printed'
                    END          AS OrdStatus
                  , s.code_desc  AS ShipMethodDesc
                  , h.mfg_loc    AS Location
                  , i.pur_or_mfg AS MFG
                  , lo.loc_desc  AS LocationDesc
                  , l.req_ship_dt
                  , CM.Notes
                  , CASE
                                    WHEN ISNULL(CM.ID,0) = 0
                                                    THEN 0
                                                    ELSE 1
                    END                                                         as HasComment
                  , l.line_no                                                   AS LineItem
                  , l.item_no                                                   AS ItemNumber
                  , l.item_desc_1                                               AS ItemDesc
                  , l.qty_ordered                                               AS QtyOrdered
                  , l.qty_to_ship                                               AS QtyToShip
                  , Dateadd(dd, 1 - Datepart(dw, l.req_ship_dt), l.req_ship_dt) AS ReqShipWeek
                  , l.loc                                                       AS LineLocation
                  , lo2.loc_desc                                                AS LineLocationDescription
                  , h.status
                  , h.oe_po_no
                  , h.job_no
                  , L.ID
                  , I.prod_cat
    from
                    oeordhdr_sql h (NOLOCK)
                    Inner Join
                                    OrderLines l
                                    on
                                                    h.ord_type   = l.ord_type
                                                    AND h.ord_no = l.ord_no
                    Inner Join
                                    Inventory I
                                    on
                                                    l.item_no = I.item_no
                    Left Outer Join
                                    CustomerTable c
                                    on
                                                    h.cus_no = c.debcode
                    Left Outer Join
                                    [synergy].dbo.divisiondebtors AS DD
                                    on
                                                    LTrim(RTRIM(H.cus_no)) = LTRIM(RTRIM(DD.debcode)) Collate Database_Default
                    Left Outer Join
                                    ShippingCodes S
                                    on
                                                    h.ship_via_cd = s.sy_code
                    Left Outer Join
                                    Warehouses  Lo
                                    on
                                                    h.mfg_loc = lo.loc
                    Left Outer Join
                                    WAREHOUSES lo2
                                    on
                                                    l.loc = lo2.loc
                    Left Outer Join
                                    OrderComments Cm
                                    on
                                                    LTRIM(RTRIM(H.ord_no)) = ltrim(rtrim(cm.ord_no))
                                                    and cm.ordertype       ='OE'
                                                    and cm.line_no         =0
                    Left Outer Join
                                    (
                                             select
                                                      top 1 aud.ord_no
                                                    , aud.aud_dt
                                                    , aud.aud_tm
                                             from
                                                      OrderAudit aud
                                             where
                                                      h.ord_no= aud.ord_no
                                             order by
                                                      aud.aud_tm desc
                                    )
                                    q1
    WHERE
                    (
                                    h.ord_type       in ('O')
                                    And H.status NOT IN ('L'
                                                       ,'C')
                    )
                    and
                    (
                                    h.Status not in ('L')
                                    AND s.cd_type = 'V'
                    )
END TRAN

Open in new window


I've tried several variation on scripting this, but I've hit a wall and cannot seem to get it working.

Any thoughts or advice would be greatly appreciated.

Thank you!
0
Ulices Diaz
Asked:
Ulices Diaz
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
Use OUTER APPLY instead of OUTER JOIN.  Btw, newer syntax calls for the top value to be enclosed in parens:

Left Outer Apply
                                    (
                                             select
                                                      top (1) aud.ord_no
                                             ...
0
 
Ulices DiazIT Business AnalystAuthor Commented:
Did not resolve the issue
0
 
Scott PletcherSenior DBACommented:
My comment is accurate, and there were no other comments.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now