0

I'm working on a SQL query that returns a SUM of a value in a column/table based off a related value in another table's column being met. I'm getting an error:

The multi-part identifier "Charges.JR_LocalSEllAmt" could not be bound. The multi-part identifier "Filtered.ChargeFAF" could not be bound.

Are you able to see anything clearly wrong with my code below? I'm unsure about how to access the AccChargeCode tables column values to check the condition before returning a SUM from a column within the Job Charge table.

SELECT  Charges.Filtered.ChargeFAF AS ChargeFAF
LEFT JOIN dbo.JobHeader ON LTC_PK = JH_ParentID
LEFT JOIN dbo.JobCharge ON JH_PK = JR_JH AND JR_OSSellAmt > 0
LEFT JOIN dbo.AccChargeCode ON JR_AC = AC_PK

LEFT JOIN 
    (
        SELECT
            JR_JH,
            JR_LocalSellAmt,
            JR_AC
        FROM
            dbo.JobCharge
            
            LEFT JOIN
            (
                SELECT
                AC_PK,
                AC_Code,
                SUM(CASE WHEN AC_Code = 'LTFAF' THEN Charges.JR_LocalSellAmt ELSE 0 END) AS ChargeFAF       
                FROM
                    dbo.AccChargeCode
                GROUP BY
                    AC_PK,
                    AC_Code
            ) AS Filtered ON Filtered.AC_PK = JR_AC
        GROUP BY
            JR_JH
    )  AS Charges ON Charges.JR_JH = JH_PK
1
  • What database system and version?
    – J.D.
    Commented Sep 21, 2022 at 12:13

1 Answer 1

1

This should do it. See code for comments.

SELECT 
    ChargeFAF = 
        Charges.ChargeFAF /* Remove .Filter */
FROM dbo.SomeTable /* You were missing a FROM here */
LEFT JOIN dbo.JobHeader 
    ON LTC_PK = JH_ParentID
LEFT JOIN dbo.JobCharge 
    ON  JH_PK = JR_JH 
    AND JR_OSSellAmt > 0
LEFT JOIN dbo.AccChargeCode 
    ON JR_AC = AC_PK
LEFT JOIN 
(
    SELECT
        JR_JH,
        JR_LocalSellAmt,
        JR_AC,
        ChargeFAF 
        /* You need to select this 
           column here to reference 
           it in the outer SELECT */
    FROM dbo.JobCharge        
    LEFT JOIN
    (
        SELECT
        AC_PK,
        AC_Code,
        ChargeFAF = 
            SUM
            (
                CASE 
                    WHEN AC_Code = 'LTFAF' 
                    THEN Charges.JR_LocalSellAmt 
                    ELSE 0 
                END
            )       
        FROM dbo.AccChargeCode
        GROUP BY
            AC_PK,
            AC_Code
    ) AS Filtered 
      ON Filtered.AC_PK = JR_AC
    GROUP BY
        JR_JH
)  AS Charges 
   ON Charges.JR_JH = JH_PK;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.