SQL Server - Invalid column name

Question

Please help, I have a problematic query to display the column OrderNo2. the following is the query:

SELECT 
    V.id, V.TypeApv, V.CreateDate, 
    P.Requestor, VE.VendorName, V.InvoiceNo, V.Hawb, 
    PA.PaymentFor, V.Amount, V.Curr, V.DueDate, V.Remarks, V.OrderNo,
    (SELECT 
         STUFF((SELECT DISTINCT ', ' + CASE WHEN [e-SAM Case]='Subsequent' OR [e-SAM Case]='DDT' AND [Local SAP PO] LIKE '5%' OR [e-SAM Case]='FBS 4'
        THEN PoNo ELSE [Local SAP PO] END
                FROM v_copo VC
                WHERE VC.AWB1 = V.Hawb
                FOR XML PATH('')), 1, 1, '')) AS OrderNo2, 
    (SELECT ISNULL(OrderNo2, V.OrderNo)) AS OrderNoFinal
FROM
    APV_AF V
LEFT JOIN 
    APV_Person P ON P.id = V.Requestor
LEFT JOIN 
    APV_Vendor VE ON VE.IDVendor = V.VendorName
LEFT JOIN 
    APV_Payment PA ON PA.IDPayment = V.PaymentFor
ORDER BY 
    V.CreateDate DESC

Answer 1

You cannot reference an alias in the same scope where it was defined (apart from the order by clause). Typical work arounds include a subquery or CTE.

In SQL Server though, a simple option is a lateral join:

SELECT 
    V.id, V.TypeApv, V.CreateDate, 
    P.Requestor, VE.VendorName, V.InvoiceNo, V.Hawb, 
    PA.PaymentFor, V.Amount, V.Curr, V.DueDate, V.Remarks, V.OrderNo,
    X.OrderNo2, 
    ISNULL(X.OrderNo2, V.OrderNo) AS OrderNoFinal
FROM APV_AF V
LEFT JOIN APV_Person P ON P.id = V.Requestor
LEFT JOIN APV_Vendor VE ON VE.IDVendor = V.VendorName
LEFT JOIN APV_Payment PA ON PA.IDPayment = V.PaymentFor
OUTER APPLY (
    SELECT 
        STUFF((SELECT DISTINCT ', ' + CASE WHEN [e-SAM Case]='Subsequent' OR [e-SAM Case]='DDT' AND [Local SAP PO] LIKE '5%' OR [e-SAM Case]='FBS 4'
        THEN PoNo ELSE [Local SAP PO] END
                FROM v_copo VC
                WHERE VC.AWB1 = V.Hawb
        FOR XML PATH('')), 1, 1, '') AS OrderNo2
) X
ORDER BY V.CreateDate DESC