sql sql-server sql-server-express outer-apply

Add a 3rd table after an 1st table OUTER APPLY 2nd table

I have a SQL script written by someone that uses outer apply on a function. I think a code generator WYSIWYG was used , I thought that all the CAST's was making it more stable, but apparently just slowed it down .

But I want to add a 3rd table that has a value I want but can't understand what or how to do it.

With the current code (shown below) I get

Incorrect syntax near ')'

at the line containing the last OUTER APPLY.

This runs on SQL Server Express and I run the script in a vendor app and an Excel spreadsheet on a PC that can't have 3rd part apps installed.

I have made most of the changes suggested not because I think I know better but it is in my skill level and I want to keep using it to understand it better.

declare @currentweek int
select @currentweek = datepart(week, getdate())
declare @currentyear int
select @currentyear = datepart(year, getdate())

select @currentweek as Current_Week , @currentyear as Current_Year

SELECT
        d.DocRef as DocRef,    
        h.GRN_Number AS Int_Wbill,
        h.DelNote AS Delivery_Notes,     
        h.CustomerCode AS [Producer ID],
        tbl_M_Customer.Customfield1 AS GGN, -- ← HERE how can I access a column of the 3rd table?
        h.CustomerName AS Name,
        h.Run_Date AS [Date],
        DATEPART( WEEK, h.Run_Date ) AS [Week],
        h.CultivarCode AS Commodity,
        h.VarietyCode AS Variety,
        h.Orchard AS [Orchard No],
        h.Containers_Tipped AS TotBins,    
        h.Kilograms_Tipped AS TotWeight,
        h.Container_Average AS AvgBinWeight,
        h.BinType AS [Bin Type] 
FROM
         tbl_Doc d 
         OUTER APPLY fn_Producer_Packout_Run_HEADER ( d.DocID ) h 
         OUTER APPLY ( select Customfield1 from tbl_M_Customer where tbl_M_Customer.CustomerCode = h.CustomerCode )
         -- ← HERE how can I join the 3rd table?

WHERE
        d.DocType = 'PHJ' 
       
        AND DATEPART( WEEK, h.Run_Date ) = @currentweek
        AND DATEPART(YEAR, h.Run_Date ) = @currentyear

Solution

Preamble: please try to improve your question, as asked in the comments: only use a minimal set of columns to focus on the problem, give a few rows of example data with your expected output, cite your sources (where did you get the OUTER APPLY? What documentation did you read about it?), add a tag to the question expliciting your RDBMS (sql-server with 95 % probability, but still we want you to tell us).
Now with the few elements I have I'll try to answer:

Your query will work as intended with just one twist: name every one of your OUTER APPLY s.

Thus as you intend to access tbl_M_Customer.Customfield1,
you should OUTER APPLY ( select Customfield1 … ) AS tbl_M_Customer:

SELECT
    …
    tbl_M_Customer.Customfield1, -- Or CAST(Customfield1 AS NVARCHAR ( 60 )), but not Customfield1 AS NVARCHAR ( 60 ): AS <column alias> and CAST( AS <type>) are not the same!
    …
FROM
    tbl_Doc d
    OUTER APPLY fn_Producer_Packout_Run_HEADER ( d.DocID ) h
    OUTER APPLY ( select Customfield1 from tbl_M_Customer where tbl_M_Customer.CustomerCode = h.CustomerCode ) tbl_M_Customer

…

However, OUTER APPLY is not necessary here:

if you intend to fetch only 1 column of tbl_M_Customer, you can "inline" the subselect as a column:
SELECT …, ( select Customfield1 from tbl_M_Customer where tbl_M_Customer.CustomerCode = h.CustomerCode ) AS Customfield1, … FROM tbl_Doc d OUTER APPLY fn_Producer_Packout_Run_HEADER ( d.DocID ) h WHERE …
if you have multiple columns to fetch, yes technically the second OUTER APPLY will do, however your first OUTER APPLY's result h is now considered as a "normal" table that can simply be LEFT JOINed to:

SELECT
    …
    tbl_M_Customer.Customfield1, -- Or CAST(Customfield1 AS NVARCHAR ( 60 )), but not Customfield1 AS NVARCHAR ( 60 ): AS <column alias> and CAST( AS <type>) are not the same!
    …
FROM
    tbl_Doc d
    OUTER APPLY fn_Producer_Packout_Run_HEADER ( d.DocID ) h
    LEFT JOIN tbl_M_Customer ON tbl_M_Customer.CustomerCode = h.CustomerCode

…

You'll find both demoed in a fiddle.

Additional cleanup

`SELECT CAST()`

All those SELECT CAST ( x AS NVARCHAR ( n ) ) AS x look strange: can't you just SELECT x? Is it a way to LEFT(x, n) to make it fit a fixed width columns output format? Else you could remove those CAST which cost a bit to the RDBMS and a lot to readability.

Week filter

DATEPART( WEEK, h.Run_Date ) =datepart(ww, getdate()) makes it unclear that both WEEK and ww return the same: to improve readability you could use the same case and unit alias on both sides.

But beware that:

you will get documents from the same week of the previous years, as you didn't match the year too
from a performance point of view, unless you have a function-based index on that week number, it will be more efficient to compare the (probably indexed) Run_Date to a value precomputed once for all, than to extract the week number of each Run_Date of your table to compare it to the (precomputed once for all) DATEPART( WEEK, GETDATE() ):

AND h.Run_Date >= DATETRUNC(WEEK, GETDATE()) AND h.Run_Date < DATEADD(WEEK, 1, DATETRUNC(WEEK, GETDATE()));

(you can even remove the h.Run_Date < … part if all your dates are in the past)

Even if SQL Server uses the index to read only the Run_Date column, it does so with an Index Scan (must read all entries of the index) instead of an Index Seek (directly knows which index values to get).

(demoed in this dedicated fiddle to show they return the same results, this one with the query plan)