SUM values from seperate DBs and UPDATE Results Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SUM values from seperate DBs and UPDATE Results Table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-30 : 07:48:52

Kim writes "Good Afternoon,

I have a question, and maybe you can tell me what I'm doing wrong.

I am trying to sum the QTY On Hand from three DB's, then update the Result into a Results Table.

If I do the following SELECT Statement, I get the result I want (refer to Code1).

If I change the code to UPDATE my results table (refer to Code2), I get the following Error (refer to Error1).

Code1.
SELECT tmp_FINALRESULTS.ITEMNMBR, TOTALITEMQTY.ITEMNMBR, TOTALITEMQTY.SUMEDQTY
FROM
[POWER].[dbo].[tmp_FINALRESULTS] AS tmp_FINALRESULTS
JOIN
(
SELECT ITEMSQTYS.ITEMNMBR AS ITEMNMBR, SUM(ITEMSQTYS.QTYONHND) AS SUMEDQTY
FROM
(
SELECT ITEMNMBR, QTYONHND FROM [POWER].[dbo].[IV00102] WHERE RCRDTYPE = 1
UNION ALL
SELECT ITEMNMBR, QTYONHND FROM [SAFT].[dbo].[IV00102] WHERE RCRDTYPE = 1
UNION ALL
SELECT ITEMNMBR, QTYONHND FROM [SMV].[dbo].[IV00102] WHERE RCRDTYPE = 1
) AS ITEMSQTYS
GROUP BY ITEMNMBR
) AS TOTALITEMQTY
ON
tmp_FINALRESULTS.ITEMNMBR = TOTALITEMQTY.ITEMNMBR
WHERE
tmp_FINALRESULTS.ITEMNMBR = '22401-W8915'

Code2.
UPDATE [POWER].[dbo].[tmp_FINALRESULTS]
SET
TOTQTYONHND = SUMEDQTY
FROM
[POWER].[dbo].[tmp_FINALRESULTS] AS tmp_FINALRESULTS
JOIN
(
SELECT ITEMSQTYS.ITEMNMBR AS ITEMNMBR, SUM(ITEMSQTYS.QTYONHND) AS SUMEDQTY
FROM
(
SELECT ITEMNMBR, QTYONHND FROM [POWER].[dbo].[IV00102] WHERE RCRDTYPE = 1
UNION ALL
SELECT ITEMNMBR, QTYONHND FROM [SAFT].[dbo].[IV00102] WHERE RCRDTYPE = 1
UNION ALL
SELECT ITEMNMBR, QTYONHND FROM [SMV].[dbo].[IV00102] WHERE RCRDTYPE = 1
) AS ITEMSQTYS
GROUP BY ITEMNMBR
) AS TOTALITEMQTY
ON
tmp_FINALRESULTS.ITEMNMBR = TOTALITEMQTY.ITEMNMBR
WHERE
tmp_FINALRESULTS.ITEMNMBR = '22401-W8915'

Error1.
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'TOTALITEMQTY' does not match with a table name or alias name used in the query.

Hope you can help."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources