September 6, 2011 at 12:42 pm
I am stumped on this. Trying to use a field specified in my select clause in the where clause of a subquery. Does not seem to be able to reference the fields.
Selecta.supplier,
a.branch_plant,
a.item_number,
a.long_item_number,
a.item_description,
SUM(a.consign_on_hand) as consign_on_hand,
SUM(a.warn_on_hand) as warn_on_hand,
(Select SUM(qty) from (Select (WOA.WMUORG - WOA.WMQNTA) * .0001 as qty
FROM PRODDTA.F3111 WOA
INNER JOIN PRODDTA.F4801 WO ON WOA.WMDOCO = WO.WADOCO
LEFT OUTER JOIN PRODDTA.F3412 MP ON MP.MWDOCO = WO.WADOCO and WOA.WMCPIT = MP.MWITM
where WO.WASRST < '90' and WO.WADCTO in ('WO','WE','WP','WF','WR') and MP.MWDCTO = 'FO' and WOA.WMCPIT = a.item_number and WOA.WMCMCU = a.branch_plant
UNION
Select MWUORG *.0001 as qty
from PRODDTA.F3412
where MWDCTO in ('WO','WP') and MWMCU = a.branch_plant and MWITM = a.item_number
and Convert(varchar,[JDE_PRODUCTION].[dbo].[JDEDateCodeToDate](MWDRQJ),111) <= Convert(varchar,Dateadd(dd,28,getdate()),111)
) dq) as demand_qty
From
(SELECT IB.ibvend as supplier
, IB.IBMCU as branch_plant
, IB.IBITM as item_number
, IB.IBLITM as long_item_number
, IM.imdsc1 as item_description
, CASE when LTRIM(IL.LIMCU) = 'KP' then lipqoh * 0.0001 else 0 end AS consign_on_hand
, CASE when LTRIM(IL.LIMCU) <> 'KP' then lipqoh * 0.0001 else 0 end AS warn_on_hand
FROM PRODDTA.F41021 IL
INNER JOIN PRODDTA.F4102 IB ON IL.LIITM = IB.IBITM and IL.LIMCU = IB.IBMCU
INNER JOIN PRODDTA.F4101 IM ON IB.IBITM = IM.IMITM
Where IB.IBSHCN = 'Y') a
Group by a.supplier, a.branch_plant, a.long_item_number, a.item_number, a.item_description
Msg 207, Level 16, State 3, Line 21
Invalid column name 'item_number'.
Msg 207, Level 16, State 3, Line 21
Invalid column name 'branch_plant'.
Msg 207, Level 16, State 3, Line 21
Invalid column name 'branch_plant'.
Msg 207, Level 16, State 3, Line 21
Invalid column name 'item_number'.
September 6, 2011 at 1:07 pm
A subquery does not know what is outside of it. It only knows what is in itself. Only the larger query can read into a subquery, not the other way around.
Thanks,
Jared
Jared
CE - Microsoft
September 6, 2011 at 2:12 pm
I couldn't test the code but it seems to me that the problem happens because you're using a subquery inside another subquery while the fields displayed in the error message belong to the outer query.
I can't recommend a solution because I can't run the code here but you could try to write your SUM aggregation in a different way.
Hope that helps.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
September 6, 2011 at 2:22 pm
This is really an over excessive use of sub-queries. There should be no reason for this. Let me "detangle" this and make some sense of it and I will present you with an optimized query.
Jared
Jared
CE - Microsoft
September 6, 2011 at 2:45 pm
Try this:
CREATE TABLE #TEMP (branch_plant int, item_number int, qty int)
CREATE TABLE #TEMP2 (supplier int, branch_plant int, item_number int, long_item_number bigint, item_description varchar(255), consign_on_hand int, warn_on_hand int)
--Insert data into first temporary table
INSERT INTO #TEMP
SELECT woa.WMCMCU, woa.MWITM, (woa.WMUORG - woa.WMQNTA) * .0001
FROM PRODDTA.F3111 WOA
INNER JOIN PRODDTA.F4801 WO
ON WOA.WMDOCO = WO.WADOCO
LEFT OUTER JOIN PRODDTA.F3412 MP
ON MP.MWDOCO = WO.WADOCO
AND WOA.WMCPIT = MP.MWITM
WHERE WO.WASRST < '90'
AND WO.WADCTO IN ('WO','WE','WP','WF','WR')
AND MP.MWDCTO = 'FO'
INSERT INTO #TEMP
SELECT MWMCU, MWITM, MWUORG *.0001 as qty
FROM PRODDTA.F3412
WHERE MWDCTO IN ('WO','WP')
AND Convert(varchar,[JDE_PRODUCTION].[dbo].[JDEDateCodeToDate](MWDRQJ),111) <= Convert(varchar,Dateadd(dd,28,getdate()),111)
--Insert data into second temporary table
INSERT INTO #TEMP2
SELECT
ib.ibvend AS supplier
, ib.IBMCU AS branch_plant
, ib.IBITM AS item_number
, ib.IBLITM AS long_item_number
, im.imdsc1 AS item_description
, CASE
WHEN LTRIM(il.LIMCU) = 'KP' THEN lipqoh * 0.0001
ELSE 0
END AS consign_on_hand
, CASE
WHEN LTRIM(il.LIMCU) <> 'KP' THEN lipqoh * 0.0001
ELSE 0
END AS warn_on_hand
FROM PRODDTA.F41021 il
INNER JOIN PRODDTA.F4102 ib
ON il.LIITM = ib.IBITM
AND il.LIMCU = ib.IBMCU
AND ib.IBSHCN = 'Y'
INNER JOIN PRODDTA.F4101 im
ON ib.IBITM = im.IMITM
--Final query
SELECT
a.supplier
, a.branch_plant
, a.item_number
, a.long_item_number
, a.item_description
, SUM(a.consign_on_hand) AS consign_on_hand
, SUM(a.warn_on_hand) AS warn_on_hand
, SUM(dq.qty) AS demand_qty
FROM #TEMP2 a
INNER JOIN #TEMP dq
ON a.branch_plant = dq.branch_plant
AND a.item_number = dq.branch_plant
GROUP BY a.supplier, a.branch_plant, a.long_item_number, a.item_number, a.item_description
This is the best that I can do, not knowing your tables and the data. Please alter the data types of the temp tables to fit your DDL.
Thanks,
Jared
Jared
CE - Microsoft
September 7, 2011 at 8:10 am
I really appreciate the feedback on this. I suspected the issue might be scoping of the selection values but was not sure if there was a way around it. I would prefer to use this as a view rather than a stored proc using temp tables but it looks like I am stuck doing so.
Thanks again for the feedback.
September 7, 2011 at 8:52 am
You could still modify what I gave you by inserting the queries used for the temp tables into the from of the final query as subqueries. Union the first 2 and give column names. If I knew your database, I'm sure I could find a way to do this without subqueries, but without the DDL, it is a bit difficult.
Thanks,
Jared
Jared
CE - Microsoft
September 10, 2011 at 5:39 pm
schillingt,
Let's make sure we all agree on what we're talking about. I've seen what are more properly called derived tables called sub-queries. A derived table is what you are using in your from statement with the alias 'a'. You also have a correlated sub-query as one of the columns in the main query. A correlated sub-query can reference the columns in the tables in the FROM clause of the main query.
Aside from that, I've found that I almost never use correlated sub-queries any more since we now have the APPLY operator. APPLY can work in a similar fashion to a correlated sub-query, but it is treated as a record set and can have more than 1 column in it that can be referenced by the main query. It can also have multiple rows returned where a correlated sub-query will blow up on multiple values returned.
Here's an example of your query re-done using OUTER APPLY. The reason for OUTER APPLY rather than CROSS APPLY is that if there are no rows returned from the CROSS APPLY, then that row in the main query will be missing. Think of CROSS APPLY like an INNER JOIN and an OUTER APPLY like a LEFT JOIN.
Please excuse any typos in this. Since I don't have your tables I could have made some mistakes and not know it:
Select a.supplier, a.branch_plant, a.item_number, a.long_item_number
, a.item_description
, SUM(a.consign_on_hand) as consign_on_hand
, SUM(a.warn_on_hand) as warn_on_hand
-- Use the OUTPUT APPLY below instead of this sub-query
--,
--(Select SUM(qty) from (Select (WOA.WMUORG - WOA.WMQNTA) * .0001 as qty
-- FROM PRODDTA.F3111 WOA
-- INNER JOIN PRODDTA.F4801 WO ON WOA.WMDOCO = WO.WADOCO
-- LEFT OUTER JOIN PRODDTA.F3412 MP ON MP.MWDOCO = WO.WADOCO and WOA.WMCPIT = MP.MWITM
-- where WO.WASRST < '90' and WO.WADCTO in ('WO','WE','WP','WF','WR') and MP.MWDCTO = 'FO' and WOA.WMCPIT = a.item_number and WOA.WMCMCU = a.branch_plant
-- UNION
-- Select MWUORG *.0001 as qty
-- from PRODDTA.F3412
-- where MWDCTO in ('WO','WP') and MWMCU = a.branch_plant and MWITM = a.item_number
-- and Convert(varchar,[JDE_PRODUCTION].[dbo].[JDEDateCodeToDate](MWDRQJ),111) <= Convert(varchar,Dateadd(dd,28,getdate()),111)
--) dq) as demand_qty
, SUM(X.Qty) AS demand_qty
From
(SELECT IB.ibvend as supplier, IB.IBMCU as branch_plant, IB.IBITM as item_number
, IB.IBLITM as long_item_number, IM.imdsc1 as item_description
, CASE when LTRIM(IL.LIMCU) = 'KP' then lipqoh * 0.0001 else 0 end AS consign_on_hand
, CASE when LTRIM(IL.LIMCU) <> 'KP' then lipqoh * 0.0001 else 0 end AS warn_on_hand
FROM PRODDTA.F41021 IL
INNER JOIN PRODDTA.F4102 IB ON
IL.LIITM = IB.IBITM
and IL.LIMCU = IB.IBMCU
INNER JOIN PRODDTA.F4101 IM ON
IB.IBITM = IM.IMITM
Where IB.IBSHCN = 'Y'
) a
OUTER APPLY
(SELECT WOA.WMUORG - WOA.WMQNTA) * .0001 AS Qty
FROM PRODDTA.FE111 WOA
INNER JOIN PRODDTA.F4801 WO ON
WOA.WMDOCO = WO.WADOCO
LEFT JOIN PRODDTA.F3412 MP ON
MP.WMDOCO = WO.WADOCO
AND WOA.WMCPIT = MP.MWITM
WHERE WO.WASRST < '90'
AND WO.WADCTO IN ('WO','WE','WP','WF','WR')
AND MP.MWDCTO = 'FO'
AND WOA.WMCPIT = a.item_number
AND WOA.WMCMCU = a.branch_plant
UNION
SELECT MWUORG * .0001
FROM PRODDTA.F3412
WHERE MWDCTO IN ('WO', 'WP')
AND MWMCU = A.branch_pland
AND MWITM = a.item_number
AND Convert(varchar,[JDE_PRODUCTION].[dbo].[JDEDateCodeToDate](MWDRQJ),111)
<= Convert(varchar,Dateadd(dd,28,getdate()),111)
) AS X
Group by a.supplier, a.branch_plant, a.long_item_number, a.item_number, a.item_description
Todd Fifield
September 12, 2011 at 11:25 am
Looks like OUTER APPLY is not supported in SQL 2000 but I did get this to work on a SQL 2008 instance which we will be migrating to soon I hope. I still need to do some bench marking but I really like this approach better because I can store it as a view which is much more handy for adhoc queries.
I really apreaciate the leg up on this!
September 14, 2011 at 1:10 pm
schillingt (9/12/2011)
Looks like OUTER APPLY is not supported in SQL 2000 but I did get this to work on a SQL 2008 instance which we will be migrating to soon I hope. I still need to do some bench marking but I really like this approach better because I can store it as a view which is much more handy for adhoc queries.I really apreaciate the leg up on this!
I use the APPLY operator all the time in views and I've found performance is very good as long as the joining columns are indexed. Even better if the quantity type columns are in the index as an INCLUDE.
If you're using an OUTER APPLY in an aggregate type view, then be sure to do an ISNULL if you really want zero for no matching rows.
Todd Fifield
September 14, 2011 at 3:00 pm
Only hitch I ran into was that the CROSS APPLY was joining to a UNION between 2 table which caused my quantities in table 'a' to double up. Not a big deal to fix however. Final code wound up looking like this -
Select a.supplier,
a.item_number,
a.long_item_number,
a.item_description,
a.consign_on_hand as consign_on_hand,
a.warn_on_hand as warn_on_hand,
SUM(X.Qty) AS demand_qty
From (SELECT MAX(IB.ibvend) as supplier,
IM.IMITM as item_number,
IM.IMLITM as long_item_number,
IM.imdsc1 as item_description,
SUM(CASE when LTRIM(IL.LIMCU) = 'KP' then lipqoh * 0.0001 else 0 end) AS consign_on_hand,
SUM(CASE when LTRIM(IL.LIMCU) <> 'KP' then lipqoh * 0.0001 else 0 end) AS warn_on_hand
FROM PRODDTA.F41021 IL
INNER JOIN PRODDTA.F4102 IB ON IL.LIITM = IB.IBITM and IL.LIMCU = IB.IBMCU
INNER JOIN PRODDTA.F4101 IM ON IB.IBITM = IM.IMITM
Where IB.IBSHCN = 'Y' and IL.LIPQOH > 0
GROUP BY IM.IMITM, IM.IMLITM, IM.imdsc1) a
CROSS APPLY (SELECT SUM((WOA.WMUORG - WOA.WMQNTA) * .0001) AS Qty
FROM PRODDTA.F3111 WOA
INNER JOIN PRODDTA.F4801 WO ON WOA.WMDOCO = WO.WADOCO and WOA.WMCMCU = WO.WAMMCU
LEFT JOIN PRODDTA.F3412 MP ON MP.MWDOCO = WO.WADOCO AND WOA.WMCPIT = MP.MWITM and MP.MWMMCU = wo.WAMMCU
WHERE WO.WASRST < '90' AND WO.WADCTO IN ('WO','WE','WP','WF','WR') AND MP.MWDCTO = 'FO' AND WOA.WMCPIT = a.item_number --AND WOA.WMCMCU = a.branch_plant
group by item_number
UNION ALL
SELECT SUM(MWUORG * .0001) AS Qty
FROM PRODDTA.F3412
WHERE MWDCTO IN ('WO', 'WP') AND MWITM = a.item_number AND Convert(varchar,[JDE_PRODUCTION].[dbo].[JDEDateCodeToDate](MWDRQJ),111)<= Convert(varchar,Dateadd(dd,28,getdate()),111) --AND MWMCU = a.branch_plant
group by item_number
) AS X
Group by a.supplier, a.long_item_number, a.item_number, a.item_description, a.consign_on_hand, a.warn_on_hand
September 14, 2011 at 3:35 pm
Can I ask why you want to create this into a view vs a stored procedure?
Thanks,
Jared
Jared
CE - Microsoft
September 14, 2011 at 3:40 pm
Could go either way but it saves some time when doing data validation and allows me to more easily hook it up to BI tools if I can reference it like a data table. If I was doing something outward face like a customer portal over the internet, I would use a stored proc for security reason.
September 15, 2011 at 2:16 pm
Looks like the APPLY method worked out for you.
Todd Fifield
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply