Nested Select Statement on LEFT JOIN

Question

I am having an issue with the filtering my table F0005 properly. I have tried this query many different ways. What I want to accomplish is having the F0005 filtered before I join it.

This is how I want it filtered and the results are perfect on just that table.

SELECT LTRIM(F0005.DRKY), F0005.DRDL01, F0005.DRRT, F0005.DRSY
    FROM
            SENCOM.F0005 F0005
            WHERE LTRIM(F0005.DRKY) != '' AND F0005.DRRT IN ('W1','08') AND F0005.DRSY NOT IN ('30','32','98')

Here is my whole query and my attempt at filtering the table before joining it.

SELECT
    FSALES2011.SXAN8,
    FSALES2011.SXCO   AS Company,
    FSALES2011.SXMCU  AS BuisinessUnit,
    FSALES2011.SXLITM AS ItemNumber,
    FSALES2011.SXSLSM AS SalesPersonCode,
    FSALES2011.SXDCTO AS OrderType,
    FSALES2011.SXSLD1 AS SoldTo,
    FSALES2011.SXADD1 AS Address,
    FSALES2011.SXRP01 AS Division,
    FSALES2011.SXRP02 AS Location,
    FSALES2011.SXCI13+FSALES2011.SXCI14+FSALES2011.SXCI15+FSALES2011.SXCI16+FSALES2011.SXCI17+
    FSALES2011.SXCI18+FSALES2011.SXCI19+FSALES2011.SXCI20+FSALES2011.SXCI21+FSALES2011.SXCI22+
    FSALES2011.SXCI23+FSALES2011.SXCI24 AS PurchasePrice,
    FSALES2011.SXAS13+FSALES2011.SXAS14+FSALES2011.SXAS15+FSALES2011.SXAS16+FSALES2011.SXAS17+
    FSALES2011.SXAS18+FSALES2011.SXAS19+FSALES2011.SXAS20+FSALES2011.SXAS21+FSALES2011.SXAS22+
    FSALES2011.SXAS23+FSALES2011.SXAS24 AS SalesPrice,
    F4801.WAAN8,
    F4801.WAWR01         AS WoType,
    DIGITS(F4801.WADOCO) AS F4801ItemNumber,
    F0101.ABAN8,
    F0101.ABAC15              AS F0101CustomerType,
    F0006.MCRP08              AS JobType,
    SUBSTR(F0006.MCMCU,6,7)   AS F0006BuisnessUnit,
    LTRIM(F0005Filtered.DRKY) AS UDC,
    F0005Filtered.DRDL01      AS Description,
    F0005Filtered.DRRT,
    F0005Filtered.DRSY
  FROM
    SENDTA.F0101 F0101
  JOIN
    JDEMOD.FSALES2011 FSALES2011
  ON
    FSALES2011.SXAN8 = F0101.ABAN8
  LEFT OUTER JOIN
    SENDTA.F0006 F0006
  ON
    FSALES2011.SXLITM = SUBSTR(F0006.MCMCU,6,7)
  LEFT OUTER JOIN
    SENDTA.F4801 F4801
  ON
    FSALES2011.SXLITM = DIGITS(F4801.WADOCO)
  LEFT OUTER JOIN
    (
        SELECT
            LTRIM(F0005.DRKY),
            F0005.DRDL01,
            F0005.DRRT,
            F0005.DRSY
            FROM
            SENCOM.F0005 F0005
        WHERE
            LTRIM(F0005.DRKY) != ''
        AND F0005.DRRT IN ('W1',
                           '08')
        AND F0005.DRSY NOT IN ('30',
                               '32',
                               '98')) F0005Filtered
  ON
    ABAC15 = F0005Filtered.DRKY

When I try this I get Column errors, SQL0205] Column DRKY not in table F0005FILTERED in *N.

Thanks for any help I am kind of new to this and running out of ideas!! :)

Huge Thanks to Everyone, my problem was the formatting of my nested select but also something totally different. I was joining on the wrong field, so there were no matches and thats why i was getting the NULLs! thanks again!!

Here is the final version. Id be open to some optimization suggestions. Otherwise glad its done!

    SELECT
    FSALES2011.SXAN8,
    FSALES2011.SXCO   AS Company,
    FSALES2011.SXMCU  AS BuisinessUnit,
    FSALES2011.SXLITM AS ItemNumber,
    FSALES2011.SXSLSM AS SalesPersonCode,
    FSALES2011.SXDCTO AS OrderType,
    FSALES2011.SXSLD1 AS SoldTo,
    FSALES2011.SXADD1 AS Address,
    FSALES2011.SXRP01 AS Division,
    FSALES2011.SXRP02 AS Location,
    FSALES2011.SXCI13+FSALES2011.SXCI14+FSALES2011.SXCI15+FSALES2011.SXCI16+FSALES2011.SXCI17+
    FSALES2011.SXCI18+FSALES2011.SXCI19+FSALES2011.SXCI20+FSALES2011.SXCI21+FSALES2011.SXCI22+
    FSALES2011.SXCI23+FSALES2011.SXCI24 AS PurchasePrice,
    FSALES2011.SXAS13+FSALES2011.SXAS14+FSALES2011.SXAS15+FSALES2011.SXAS16+FSALES2011.SXAS17+
    FSALES2011.SXAS18+FSALES2011.SXAS19+FSALES2011.SXAS20+FSALES2011.SXAS21+FSALES2011.SXAS22+
    FSALES2011.SXAS23+FSALES2011.SXAS24 AS SalesPrice,
    F4801.WAAN8,
    F4801.WAWR01         AS WoType,
    DIGITS(F4801.WADOCO) AS F4801ItemNumber,
    F0101.ABAN8,
    F0101.ABAC15            AS F0101CustomerType,
    F0006.MCRP08            AS JobType,
    SUBSTR(F0006.MCMCU,6,7) AS F0006BuisnessUnit,
    LTRIM(UDC.DRKY)         AS Code,
    --UDC.DRDL01              AS JobDescription,
    --UDC2.DRDL01             AS WODescription,
    COALESCE(UDC.DRDL01,UDC2.DRDL01) AS WorkPerformed,
    UDC3.DRDL01                      AS CustomerDescription
FROM
    SENDTA.F0101 F0101
JOIN
    JDEMOD.FSALES2011 FSALES2011
ON
    FSALES2011.SXAN8 = F0101.ABAN8
LEFT OUTER JOIN
    SENDTA.F0006 F0006
ON
    FSALES2011.SXLITM = SUBSTR(F0006.MCMCU,6,7)
LEFT OUTER JOIN
    SENDTA.F4801 F4801
ON
    FSALES2011.SXLITM = DIGITS(F4801.WADOCO)
LEFT OUTER JOIN
    SENCOM.F0005 UDC
ON
    F0006.MCRP08 = LTRIM(UDC.DRKY)
AND LTRIM(UDC.DRKY) != ''
AND UDC.DRRT IN ('W1',
                 '08')
AND UDC.DRSY NOT IN ('30',
                     '32',
                     '98')
LEFT OUTER JOIN
    SENCOM.F0005 UDC2
ON
    F4801.WAWR01 = LTRIM(UDC2.DRKY)
AND LTRIM(UDC2.DRKY) != ''
AND UDC2.DRRT IN ('W1',
                  '08')
AND UDC2.DRSY NOT IN ('30',
                      '32',
                      '98')
LEFT OUTER JOIN
    SENCOM.F0005 UDC3
ON
    F0101.ABAC15 = LTRIM(UDC3.DRKY)
AND LTRIM(UDC3.DRKY) != ''
AND UDC3.DRRT IN ('15')

Answer 1

In your nested query you are missing the alias for DRKY:

SELECT
            LTRIM(F0005.DRKY) as DRKY,
            F0005.DRDL01,
            F0005.DRRT,
            F0005.DRSY
            FROM SENCOM.F0005 F0005
        WHERE LTRIM(F0005.DRKY) != ''
        AND F0005.DRRT IN ('W1',
                           '08')
        AND F0005.DRSY NOT IN ('30',
                               '32',
                               '98')) F0005Filtered

If you don't have an alias when it return no column name for the LTRIM(F0005.DRKY)

Answer 2

If you wrap a field in a function, you need to name it afterwards.

Change LTRIM(F0005.DRKY), to LTRIM(F0005.DRKY) AS DRKY,