From: DavidC on
I have a stored procedure where I am trying to update a temp table in the
flow of the sp. I have tried the code below but it gives me an error
"Incorrect syntax near the keyword 'GROUP' and I understand the error but
don't know how to get around it and still get sums. Can anyone help? Thanks.

UPDATE #tempInventoryAnalysis
SET [PTDSalesQty] = SUM(CASE WHEN MGB.dbo.tblArHistHeader.InvcDate
BETWEEN @StartDate AND @EndDate THEN
MGB.dbo.tblArHistDetail.QtyShipSell
ELSE 0
END),
[YTDSalesQty] = SUM(MGB.dbo.tblArHistDetail.QtyShipSell),
[PTDSalesAmt] = SUM(CASE WHEN MGB.dbo.tblArHistHeader.InvcDate
BETWEEN @StartDate AND @EndDate THEN
MGB.dbo.tblArHistDetail.PriceExt
ELSE 0
END),
[YTDSalesAmt] = SUM(MGB.dbo.tblArHistDetail.PriceExt),
[YTDCostAmt] = SUM(MGB.dbo.tblArHistDetail.CostExt)
FROM MGB.dbo.tblArHistHeader INNER JOIN
MGB.dbo.tblArHistDetail ON MGB.dbo.tblArHistHeader.PostRun =
MGB.dbo.tblArHistDetail.PostRun AND
MGB.dbo.tblArHistHeader.TransId = MGB.dbo.tblArHistDetail.TransID
INNER JOIN
#tempInventoryAnalysis.ItemId ON MGB.dbo.tblArHistDetail.PartId =
#tempInventoryAnalysis.ItemId
WHERE (MGB.dbo.tblArHistHeader.InvcDate BETWEEN @YearStart AND @EndDate)
AND (MGB.dbo.tblArHistDetail.PartId IS NOT NULL)
GROUP BY MGB.dbo.tblArHistDetail.PartId


--
David
From: Tom Cooper on
If I understand what you want done by your update, you do not need a GROUP
BY. For each row in #tempInventoryAnalysis, the result of the select only
contains the rows for one MGB.dbo.tblArHistDetail.PartId (because your query
has

INNER JOIN
#tempInventoryAnalysis.ItemId ON MGB.dbo.tblArHistDetail.PartId =
#tempInventoryAnalysis.ItemId

so for each row in #tempInventoryAnalysis, you just want the sum with no
GROUP BY. So just remove the GROUP BY from the update and it should work.

If that doesn't work, please provide sample tables and data (in the form of
CREATE TABLE and INSERT statements, please) and then the results you want in
#tempInventoryAnalysis after the update and we should be able to provide you
with what you want.

Tom

"DavidC" <dlchase(a)lifetimeinc.com> wrote in message
news:B3B91C54-65D3-4F2B-9109-79FDCD03FD51(a)microsoft.com...
>I have a stored procedure where I am trying to update a temp table in the
> flow of the sp. I have tried the code below but it gives me an error
> "Incorrect syntax near the keyword 'GROUP' and I understand the error but
> don't know how to get around it and still get sums. Can anyone help?
> Thanks.
>
> UPDATE #tempInventoryAnalysis
> SET [PTDSalesQty] = SUM(CASE WHEN MGB.dbo.tblArHistHeader.InvcDate
> BETWEEN @StartDate AND @EndDate THEN
> MGB.dbo.tblArHistDetail.QtyShipSell
> ELSE 0
> END),
> [YTDSalesQty] = SUM(MGB.dbo.tblArHistDetail.QtyShipSell),
> [PTDSalesAmt] = SUM(CASE WHEN MGB.dbo.tblArHistHeader.InvcDate
> BETWEEN @StartDate AND @EndDate THEN
> MGB.dbo.tblArHistDetail.PriceExt
> ELSE 0
> END),
> [YTDSalesAmt] = SUM(MGB.dbo.tblArHistDetail.PriceExt),
> [YTDCostAmt] = SUM(MGB.dbo.tblArHistDetail.CostExt)
> FROM MGB.dbo.tblArHistHeader INNER JOIN
> MGB.dbo.tblArHistDetail ON MGB.dbo.tblArHistHeader.PostRun =
> MGB.dbo.tblArHistDetail.PostRun AND
> MGB.dbo.tblArHistHeader.TransId = MGB.dbo.tblArHistDetail.TransID
> INNER JOIN
> #tempInventoryAnalysis.ItemId ON MGB.dbo.tblArHistDetail.PartId =
> #tempInventoryAnalysis.ItemId
> WHERE (MGB.dbo.tblArHistHeader.InvcDate BETWEEN @YearStart AND @EndDate)
> AND (MGB.dbo.tblArHistDetail.PartId IS NOT NULL)
> GROUP BY MGB.dbo.tblArHistDetail.PartId
>
>
> --
> David

From: --CELKO-- on
>> I have a stored procedure where I am trying to update a temp table in the flow of the stored procedure. <<

What you have described is how we programmed in the 1950's with mag
tape systems. Hang a scratch tape, then do procedural steps in a
procedural process. The idea of SQL is declarative programming,
without any concept of flow. We want to avoid redundancy and flow
ISO-11179 naming rules, so silly redundancies like "#temp" or "tbl-"
just are not used.

UPDATE.. FROM.. is both proprietary and flawed by itself. I have no
idea how it will work when you have a self-join in an UPDATE, as you
do. Instead of thinking of step-by-step scratch tape programming, why
not build a VIEW or a procedure that returns a query that is always
up to date? Without DDL, I can only make a guess, but here is a
skeleton:

CREATE PROCEDURE AnalysisByParts
(@in_start_date DATE, @in_end_date DATE)
AS
SELECT D.part_id,
SUM(CASE WHEN H.invoice_date
BETWEEN @in_start_date AND @in_end_date
THEN D.ship_sell_qty ELSE 0 END) AS ptd_sales_qty,
SUM(D.ship_sell_qty) AS ytd_sales_qty
SUM(CASE WHEN H.invoice_date
BETWEEN @in_start_date AND @in_end_date
THEN D.price_ext ELSE 0 END) AS ptd_sales_amt
SUM(D.price_ext) AS ytd_sales_amt,
SUM(D.cost_ext) AS ytd_cost_amt
FROM MGB.dbo.ArHistHeaders AS H,
MGB.dbo.ArHistDetails AS D
WHERE H.invc_date BETWEEN @in_start_date AND @in_end_date
AND H.post_run = D.post_run
AND H.trans_id = D.trans_id
GROUP BY D.part_id;

We can probably rearrange this to move the Headers. I am also trying
to figure out how a part_id could be NULL in the details table.
From: DavidC on
"--CELKO--" wrote:

> >> I have a stored procedure where I am trying to update a temp table in the flow of the stored procedure. <<
>
> What you have described is how we programmed in the 1950's with mag
> tape systems. Hang a scratch tape, then do procedural steps in a
> procedural process. The idea of SQL is declarative programming,
> without any concept of flow. We want to avoid redundancy and flow
> ISO-11179 naming rules, so silly redundancies like "#temp" or "tbl-"
> just are not used.
>
> UPDATE.. FROM.. is both proprietary and flawed by itself. I have no
> idea how it will work when you have a self-join in an UPDATE, as you
> do. Instead of thinking of step-by-step scratch tape programming, why
> not build a VIEW or a procedure that returns a query that is always
> up to date? Without DDL, I can only make a guess, but here is a
> skeleton:
>
> CREATE PROCEDURE AnalysisByParts
> (@in_start_date DATE, @in_end_date DATE)
> AS
> SELECT D.part_id,
> SUM(CASE WHEN H.invoice_date
> BETWEEN @in_start_date AND @in_end_date
> THEN D.ship_sell_qty ELSE 0 END) AS ptd_sales_qty,
> SUM(D.ship_sell_qty) AS ytd_sales_qty
> SUM(CASE WHEN H.invoice_date
> BETWEEN @in_start_date AND @in_end_date
> THEN D.price_ext ELSE 0 END) AS ptd_sales_amt
> SUM(D.price_ext) AS ytd_sales_amt,
> SUM(D.cost_ext) AS ytd_cost_amt
> FROM MGB.dbo.ArHistHeaders AS H,
> MGB.dbo.ArHistDetails AS D
> WHERE H.invc_date BETWEEN @in_start_date AND @in_end_date
> AND H.post_run = D.post_run
> AND H.trans_id = D.trans_id
> GROUP BY D.part_id;
>
> We can probably rearrange this to move the Headers. I am also trying
> to figure out how a part_id could be NULL in the details table.
> .
>

I agree with the 'tbl' prefix. And please don't jump to conclusions as we
did not create this database we are just pulling some data from it for our
customer. Those of us not in the "ivory tower" world have many weird things
we have to deal with, including designs from other vendors.

That said, I agree with using a SELECT and SUM's with a GROUP BY. However,
this is only 1/3 of the answer. We have to SUM detail from 4 other
header/detail tables. If I join them here also then I will get X times more
total than I want. Any ideas on the best way to proceed? Should I build 3
summary views and then JOIN them on the ItemId to get a single summary by
ItemId (named PartId in the one table)?

Thanks.

--
David


From: Erland Sommarskog on
DavidC (dlchase(a)lifetimeinc.com) writes:
> I have a stored procedure where I am trying to update a temp table in
> the flow of the sp. I have tried the code below but it gives me an
> error "Incorrect syntax near the keyword 'GROUP' and I understand the
> error but don't know how to get around it and still get sums. Can anyone
> help?

You need to produce the sums in a derived table (or a Common Table
Expression).

Below is a rewrite of the query. It's fairly mechanical, and I had
to make some assumptions. Be sure to test it well. I also introduced
aliases to make it possible to see the forest through the trees.

I also should add that I have not checked the query for syntax, but
I trust that you can fix any syntax errors on your own.

UPDATE #tempInventoryAnalysis
SET PTDSalesQty = AH.PTDSalesQty,
YTDSalesQty = AH.YTDSalesQty,
PTDSalesAmt = AH.PTDSalesAmt,
YTDSalesAmt = AH.YTDSalesAmt,
YTDCostAmt = AH.YTDCostAmt
FROM #tempInventoryAnalysis IA
JOIN (SELECT D.PartID,
PTDSalesQty =
SUM(CASE WHEN H.InvcDate BETWEEN @StartDate AND
@EndDate
THEN D.QtyShipSell
ELSE 0
END),
YTDSalesQty = SUM(D.QtyShipSell),
PTDSalesAmt = SUM(CASE WHEN H.InvcDate BETWEEN @StartDate
AND @EndDate
THEN D.PriceExt
ELSE 0
END),
YTDSalesAmt = SUM(D.PriceExt),
YTDCostAmt = SUM(D.CostExt)
FROM MGB.dbo.tblArHistDetail D ON ON d
JOIN MGB.dbo.tblArHistHeader H ON H.PostRun = D.PostRun
AND H.TransId = D.TransID
WHERE H.InvcDate BETWEEN @YearStart AND @EndDate
AND D.PartId IS NOT NULL
GROUP BY D.PartID) AS AH ON AN.PartId = IA.ItemI



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: Sql server 2008 r2 hangs up
Next: Top 2 from count