Nested select query in LEFT JOIN

Question

I have a problem with filtering my F0005 table correctly. I tried this request in many ways. I want to filter the F0005 before joining it.

This is how I want it to be filtered out, and the results are perfect only for this 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 entire query and my attempt to filter a 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 to do this, I get column errors, SQL0205] DRKY column is not in table F0005FILTERED in * N.

Thanks for any help. I'm kind of new to this and end ideas! :)

Many thanks to everyone, my problem was the formatting of my nested selection, as well as something completely different. I joined the wrong field, so there were no matches, and therefore I was getting NULL! Thanks again!

Here is the final version. Id should be open to some optimization suggestions. Otherwise glad that it is 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

If you transfer a field to a function, you need to call it later.

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

Answer 2

Your subquery is missing an 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 do not have an alias when it does not return the column name for LTRIM(F0005.DRKY)

Answer 3

You need to name your computed columns in the filtered table.

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

Please note that AS DRKY

Answer 4

You really need to reformat your question, but I believe that I know where you did wrong.

Your table alias, F0005Filtered, is not an alias in the JDE user code table. You have created a subselect that actually represents F0005Filtered.

Also, inside this sub-selection, you do not have a column called DRKY.

You use the TRIM function in this field and do not specify a column name.

To fix, just recreate the alias: -

 LTRIM(F0005.DRKY) AS DRKY 

EDIT

In response to a user question.

Why not make your IN instructions part of the JOIN clause?

 LEFT OUTER JOIN SENCOM.F0005 UDC ON LTRIM(UDC.DRKY) != '' AND ABAC15 = TRIM(UDC.DRKY) AND UDC.DRRT IN ('W1','08') AND UDC.DRSY NOT IN ('30', '32','98') 

Custom code tables are not so great. I do some work for a large multinational, and we have about 70 thousand lines.