sql - LEFT JOIN 上的嵌套选择语句

我在正确过滤表 F0005 时遇到问题。我已经尝试了很多不同的方式来查询这个查询。我想要完成的是在我加入之前过滤 F0005。

这就是我希望它过滤的方式，并且结果在该表上是完美的。

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')

这是我的整个查询以及我在加入表之前过滤表的尝试。

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

当我尝试此操作时，出现列错误，SQL0205] 列 DRKY 不在 *N 中的表 F0005FILTERED 中。

感谢您的任何帮助，我对此还是陌生的! :)

非常感谢大家，我的问题是嵌套选择的格式，但也完全不同。我加入了错误的领域，所以没有匹配项，这就是为什么我得到 NULL!再次感谢!!

这是最终版本。我愿意接受一些优化建议。否则很高兴它完成了!

    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')

最佳答案

如果将字段包装在函数中，则需要在之后为其命名。

将 LTRIM(F0005.DRKY), 更改为 LTRIM(F0005.DRKY) AS DRKY,

关于sql - LEFT JOIN 上的嵌套选择语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9295944/