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!
LVL 1
Ulices DiazIT Business AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.