October 31, 2011 at 6:04 pm
Is it possible to include a subquery on the table being updated in the quirky update? I'm experimenting with the following code but the subqueries are not returning updated data that I expect from a pseudo-cursor. The first row updates fine, but when later rows need to get the data from the first row, the subquery comes back with 0 instead of the correct value. Am I expecting too much?
UPDATEA
SET@Anchor = VntPodKey,
@Pct = A.Pct2008 =
CASE
WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN -- REDUCING OR GAINING GALLONS DOES NOT CHANGE PERCENTS - GET THE LAST PERCENT FOR THIS LOT
(SELECT TOP 1 B.Pct2008
FROM #tmpVintage B
WHERE B.VntLotKey = A.VntLotKey AND B.VntTranDate < A.VntTranDate
ORDER BY B.VntTranDate DESC)
ELSE -- INCREASING GALLONS ON A 2-SIDED TRANSACTION
CASE
WHEN A.VntPodCount = 2 THEN -- GET THE PCT FROM THE 'FROM' SIDE OF THE TRANSACTION
(((SELECT B.Pct2008
FROM #tmpVintage B
WHERE B.VntPodKey = A.VntPodKey AND
B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2008
+
(SELECT TOP 1 C.Run2008
FROM #tmpVintage C
WHERE C.VntLotKey = A.VntLotKey AND C.VntTranDate < A.VntTranDate
ORDER BY C.VntTranDate DESC))-- ADD THE LAST RUNNING TOTAL FOR THE LOT TO THE QTY2008 FOR THE RUN2008 QTY
/
A.VntRunTranQty -- DIVIDE THE RUN2008 BY THE LOT'S RUNNING TOTAL TO GET THE PERCENT
END
END,
@Qty = A.Qty2008 =
CASE
WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN
@Pct * A.VntTranQty / 100
ELSE
((SELECT B.Pct2008
FROM #tmpVintage B
WHERE B.VntPodKey = A.VntPodKey AND
B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2008
END,
@Run = A.Run2008 =
CASE
WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN
@Pct * A.VntRunTranQty / 100
ELSE
(((SELECT B.Pct2008
FROM #tmpVintage B
WHERE B.VntPodKey = A.VntPodKey AND
B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2008
+
(SELECT TOP 1 C.Run2008
FROM #tmpVintage C
WHERE C.VntLotKey = A.VntLotKey AND C.VntTranDate < A.VntTranDate
ORDER BY C.VntTranDate DESC)) -- ADD THE LAST RUNNING TOTAL FOR THE LOT TO THE QTY2008 FOR THE RUN2008 QTY
END
FROM#tmpVintage A WITH (TABLOCKX)
WHEREA.VntTagKey = 0
OPTION (MAXDOP 1)
October 31, 2011 at 6:31 pm
gcresse (10/31/2011)
Is it possible to include a subquery on the table being updated in the quirky update? I'm experimenting with the following code but the subqueries are not returning updated data that I expect from a pseudo-cursor. The first row updates fine, but when later rows need to get the data from the first row, the subquery comes back with 0 instead of the correct value. Am I expecting too much?
Briefly... yes.
There may be ways to get particular optimizers to handle this, but in the end I've found it safest for serial updates to not subquery. Dump it to #tmps and keep going, or do all data association first in the #tmp you're working from and then start using the serializability.
I have NOT tested that factor with CROSS APPLY, though, but that does all sorts of different things under the hood.
EDIT:
I just re-read your code, you're self-referencing in the middle of a serial update.
There's no way this can end well. The idea behind serial/quirky updating is to handle the current row and anything you've been carrying with you along the way since the beginning. It's not meant to go back and use what you've already done.
Occasionally RBAR is the answer, unfortunately. This looks like one of those cases.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 31, 2011 at 6:41 pm
Thanks, Craig. When you say "dump it into #tmps" do you mean write the data that I'm trying to get out of my subqueries into temp tables and then join to those tables instead of subquerying? I'm not exactly sure how to do that, if that's what you mean. Also, Jeff Moden's article about the quirky update prohibits joins in the quirky update. Perhaps I am trying to accomplish the impossible here.
October 31, 2011 at 6:51 pm
gcresse (10/31/2011)
Thanks, Craig. When you say "dump it into #tmps" do you mean write the data that I'm trying to get out of my subqueries into temp tables and then join to those tables instead of subquerying? I'm not exactly sure how to do that, if that's what you mean. Also, Jeff Moden's article about the quirky update prohibits joins in the quirky update. Perhaps I am trying to accomplish the impossible here.
I have used joins, including self-joins, before in the serial update without issue, however, I do not have a set of rules that you could follow that will guarantee the outcome, so, take that with a grain of salt. I would have to break down the query and figure out exactly what logic you were trying to get it to do, and when I play those games I work off temp tables entirely to get as much control as possible. I mostly learned how to do it because I didn't know you couldn't, and long before I'd even heard of Jeff Moden or his incredibly thorough article. 😉
As to dump it to #tmps, yes, that is exactly what I was saying. However, in this case you're probably going to want to do pre-associations in the main table you're running the quirky on for whatever data its going to need when you do the update. IE: If you need the associated average for a line, go out and get the average separate from the update and include it in another column the primary table in the update uses.
Correllated subqueries back to itself however... that's pretty much never going to work. Between having to do dirty reads, needing to keep row control, and a number of other factors it's just too much to try to work against when we can't see how things work under the hood and we're using an undocumented 'feature' to begin with.
EDIT: A good example for pre-work:
CASE
WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN
@Pct * A.VntTranQty / 100
ELSE
((SELECT B.Pct2008
FROM #tmpVintage B
WHERE B.VntPodKey = A.VntPodKey AND
B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2008
END,
Leave the CASE, but bring in the B.PCT2008 into another column before you start so you don't have to go look it up again with the SELECT.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 31, 2011 at 8:48 pm
Evil Kraig F (10/31/2011)
Correllated subqueries back to itself however... that's pretty much never going to work. Between having to do dirty reads, needing to keep row control, and a number of other factors it's just too much to try to work against when we can't see how things work under the hood and we're using an undocumented 'feature' to begin with.
Preeeeecisely. That's why Rule 5 stipulates "no joins". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2011 at 11:46 am
Initially, my plan was to put everything I needed in the temp table as a column so I could use the standard 'quirky' update logic. My problem is that I'm creating this temp table by pivoting the results of a 'lot trace' that gathers every input that went into a particular lot of wine. I need to calculate the percents for the vintage, appellation, and combined variety/appellation for any given lot at any given time. The generic non-varietal wines can have hundreds of variety/appellation combinations by the time they are put in a bottle, and I'm already pretty sure I'm going to max out the table column limit just by running this experiment on one of those lots. I'm thinking now that I may have to create a seperate temp table for each vintage (and eventually appellation, variety/appellation) but I haven't worked that out in my head yet.
I do have a script to experiment on a very small test lot (LotC) with only a few transactions. The script picks up after I've already traced the lot, pivoted the vintages, calculated the receiving data by vintage, and calculated the running totals by lot. Every lot begins with some sort of receiving with known percents. The attached spreadsheet shows the 9 basic transactions (PodKey is the production order detail key) where we either receive a lot of wine, pump from one lot to another, or gain gallons. The second (with colors) set of data is the pivoted data with the final results I'm after. The third smaller "Running Total" set is just the interim step I took to calculate the running totals by lot.
I'm open to any ideas or suggestions on how I might accomplish this. Thanks for your input.
-- BUILD THE TEMP TABLE TO STORE THE PIVOTED VINTAGES
IF (SELECT OBJECT_ID('tempdb..#tmpVintage')) IS NOT NULL
DROP TABLE #tmpVintage
CREATE TABLE #tmpVintage( VntTranDateDATETIMENOT NULL,
VntIssueRcptCHAR(1)NOT NULL,
VntLotVARCHAR(15)NOT NULL,
VntPodKeyINTEGERNOT NULL,
VntPodCountTINYINTNOT NULL,
VntTagKeyINTEGERNOT NULL,
VntTranQtyDECIMAL(18,5)NOT NULL,
VntTranUOMVARCHAR(2)NOT NULL,
VntRunTranQtyDECIMAL(18,5)NULL,
Qty2009DECIMAL(18,5)NULL,
Run2009DECIMAL(18,5)NULL,
Pct2009DECIMAL(18,5)NULL,
Qty2010DECIMAL(18,5)NULL,
Run2010DECIMAL(18,5)NULL,
Pct2010DECIMAL(18,5)NULL)
IF (SELECT OBJECT_ID('tempdb..idx_tmpVintageByDate')) IS NOT NULL
DROP INDEX idx_tmpVintageByDate ON #tmpVintage
CREATE CLUSTERED INDEX idx_tmpVintageByDate ON #tmpVintage (VntTranDate, VntPodKey, VntIssueRcpt)
INSERT INTO #tmpVintage(VntTranDate,
VntIssueRcpt,
VntLot,
VntPodKey,
VntPodCount,
VntTagKey,
VntTranQty,
VntTranUOM,
VntRunTranQty,
Qty2009,
Run2009,
Pct2009,
Qty2010,
Run2010,
Pct2010)
VALUES('1/1/2011','R','LotA',1,1,1,1000,'GA',1000,1000,1000,100,0,0,0),
('1/2/2011','I','LotA',2,2,0,-750,'GA',250,NULL,NULL,NULL,NULL,NULL,NULL),
('1/2/2011','R','LotC',2,2,0,750,'GA',750,NULL,NULL,NULL,NULL,NULL,NULL),
('1/3/2011','R','LotB',3,1,2,1000,'GA',1000,0,0,0,1000,1000,100),
('1/4/2011','I','LotB',4,2,0,-500,'GA',500,NULL,NULL,NULL,NULL,NULL,NULL),
('1/4/2011','R','LotC',4,2,0,500,'GA',1250,NULL,NULL,NULL,NULL,NULL,NULL),
('1/5/2011','I','LotA',5,2,0,-250,'GA',0,NULL,NULL,NULL,NULL,NULL,NULL),
('1/5/2011','R','LotB',5,2,0,250,'GA',750,NULL,NULL,NULL,NULL,NULL,NULL),
('1/6/2011','I','LotB',6,2,0,-500,'GA',250,NULL,NULL,NULL,NULL,NULL,NULL),
('1/6/2011','R','LotC',6,2,0,500,'GA',1750,NULL,NULL,NULL,NULL,NULL,NULL),
('1/7/2011','R','LotD',7,1,3,1000,'GA',1000,100,100,10,900,900,90),
('1/8/2011','I','LotD',8,2,0,-1000,'GA',0,NULL,NULL,NULL,NULL,NULL,NULL),
('1/8/2011','R','LotC',8,2,0,1000,'GA',2750,NULL,NULL,NULL,NULL,NULL,NULL),
('1/9/2011','R','LotC',9,1,0,50,'GA',2800,NULL,NULL,NULL,NULL,NULL,NULL);
SELECT * FROM #tmpVintage
DECLARE @Pct AS DECIMAL(18,5) = 0,
@Qty AS DECIMAL(18,5) = 0,
@Run AS DECIMAL(18,5) = 0,
@Anchor AS VARCHAR(15)= ''
UPDATEA
SET@Anchor = VntPodKey,
@Pct = A.Pct2009 =
CASE
WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN -- REDUCING OR GAINING GALLONS DOES NOT CHANGE PERCENTS - GET THE LAST PERCENT FOR THIS LOT
(SELECT TOP 1 B.Pct2009
FROM #tmpVintage B
WHERE B.VntLot = A.VntLot AND B.VntTranDate < A.VntTranDate
ORDER BY B.VntTranDate DESC)
ELSE -- INCREASING GALLONS ON A 2-SIDED TRANSACTION
CASE
WHEN A.VntPodCount = 2 THEN -- GET THE PCT FROM THE 'FROM' SIDE OF THE TRANSACTION
(((SELECT B.Pct2009
FROM #tmpVintage B
WHERE B.VntPodKey = A.VntPodKey AND
B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2009
+
(SELECT TOP 1 C.Run2009
FROM #tmpVintage C
WHERE C.VntLot = A.VntLot AND C.VntTranDate < A.VntTranDate
ORDER BY C.VntTranDate DESC))-- ADD THE LAST RUNNING TOTAL FOR THE LOT TO THE QTY2009 FOR THE RUN2009 QTY
/
A.VntRunTranQty -- DIVIDE THE RUN2009 BY THE LOT'S RUNNING TOTAL TO GET THE PERCENT
END
END,
@Qty = A.Qty2009 =
CASE
WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN
@Pct * A.VntTranQty / 100
ELSE
((SELECT B.Pct2009
FROM #tmpVintage B
WHERE B.VntPodKey = A.VntPodKey AND
B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2009
END,
@Run = A.Run2009 =
CASE
WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN
@Pct * A.VntRunTranQty / 100
ELSE
(((SELECT B.Pct2009
FROM #tmpVintage B
WHERE B.VntPodKey = A.VntPodKey AND
B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2009
+
(SELECT TOP 1 C.Run2009
FROM #tmpVintage C
WHERE C.VntLot = A.VntLot AND C.VntTranDate < A.VntTranDate
ORDER BY C.VntTranDate DESC)) -- ADD THE LAST RUNNING TOTAL FOR THE LOT TO THE QTY2009 FOR THE RUN2009 QTY
END,
@Pct = A.Pct2010 =
CASE
WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN -- REDUCING OR GAINING GALLONS DOES NOT CHANGE PERCENTS - GET THE LAST PERCENT FOR THIS LOT
(SELECT TOP 1 B.Pct2010
FROM #tmpVintage B
WHERE B.VntLot = A.VntLot AND B.VntTranDate < A.VntTranDate
ORDER BY B.VntTranDate DESC)
ELSE -- INCREASING GALLONS ON A 2-SIDED TRANSACTION
CASE
WHEN A.VntPodCount = 2 THEN -- GET THE PCT FROM THE 'FROM' SIDE OF THE TRANSACTION
(((SELECT B.Pct2010
FROM #tmpVintage B
WHERE B.VntPodKey = A.VntPodKey AND
B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2010
+
(SELECT TOP 1 C.Run2010
FROM #tmpVintage C
WHERE C.VntLot = A.VntLot AND C.VntTranDate < A.VntTranDate
ORDER BY C.VntTranDate DESC))-- ADD THE LAST RUNNING TOTAL FOR THE LOT TO THE QTY2010 FOR THE RUN2010 QTY
/
A.VntRunTranQty -- DIVIDE THE RUN2010 BY THE LOT'S RUNNING TOTAL TO GET THE PERCENT
END
END,
@Qty = A.Qty2010 =
CASE
WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN
@Pct * A.VntTranQty / 100
ELSE
((SELECT B.Pct2010
FROM #tmpVintage B
WHERE B.VntPodKey = A.VntPodKey AND
B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2010
END,
@Run = A.Run2010 =
CASE
WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN
@Pct * A.VntRunTranQty / 100
ELSE
(((SELECT B.Pct2010
FROM #tmpVintage B
WHERE B.VntPodKey = A.VntPodKey AND
B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2010
+
(SELECT TOP 1 C.Run2010
FROM #tmpVintage C
WHERE C.VntLot = A.VntLot AND C.VntTranDate < A.VntTranDate
ORDER BY C.VntTranDate DESC)) -- ADD THE LAST RUNNING TOTAL FOR THE LOT TO THE QTY2010 FOR THE RUN2010 QTY
END
FROM#tmpVintage A WITH (TABLOCKX)
WHEREA.VntTagKey = 0 -- << RUNNING TOTALS FOR RECEIVINGS HAVE ALREADY BEEN CALCULATED SO SKIP THEM HERE
OPTION (MAXDOP 1) -- << THIS IS REQUIRED TO PREVENT PARALLELISM
SELECT * FROM #tmpVintage ORDER BY VntTranDate, VntPodKey, VntIssueRcpt
The more I look at this, the more I think I should not pivot the vintages across, but add a column with the vintage and perform the running totals per vintage. Then I might have room to add columns for the values I need to perform the calculations... back to the drawing board.
... bad news, I just checked my 'worst case scenario' generic wine and it has over 48,000 different input lots to track. Too many columns :crying:
November 1, 2011 at 1:30 pm
Leave the CASE, but bring in the B.PCT2008 into another column before you start so you don't have to go look it up again with the SELECT.
I won't have enough room for all the potential columns I'll need. If only there was a way I could write the B.PCT2008 value to a different temp table at this point in time, then select from it again when I need it...
November 1, 2011 at 1:59 pm
gcresse (11/1/2011)
I'm open to any ideas or suggestions on how I might accomplish this. Thanks for your input.
My apologies. I'm sure there's a way out of this but I just got a massive project dropped on my desk at work that's going to require an absurd amount of my attention for the next few days.
I'm going to drop off this conversation for now, but I'll be back to check on it later when time permits... hopefully Jeff (since he's dropped by once) or one of the others will be able to pick this up and help you out in the meanwhile.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply