August 4, 2015 at 10:40 am
While extracting data I ran across a CTE result i was not expecting.
I have a temp table #tReceive (below) that is populated for new Purchase Orders. I was using a CTE to get a distinct key (ponumber, date, delivnote), then using that distinct to give those 15 rows a unique GUID.
When testing a simple select with cteReceiveGuid the results were 15 distinct key (& GUID) rows. However when I used an update against the table itself (populating parent child key PoReceiveHeaderGuid from CTE) there was a different GUID for all 71 rows.
The data below uses 4 lineitems and when testing against the CTE there are 2 distinct results. However running the update every line gets a new GUID.
It feels like the CTE is acting more like RBAR then a result. My best thought is the updating #tReceive from a CTE against #tReceive is somehow re-cursing. Populating another temp table works, as will inserting into POHeader but was surprised by results.
thanks
CREATE TABLE #tReceive -- drop table #tReceive
(
JobNumberIDintNULL, PONUMBERvarchar(14)NULL, DELIVNOTEvarchar(14)NULL,
DATEdatetimeNULL, ORDERKEYdecimalNULL, TYPEvarchar(3)NULL,
QuantityintNULL, PoReceiveHeaderGuiduniqueidentifiernull
)
INSERT INTO #tReceive
( JobNumberID
, PONUMBER
, DELIVNOTE
, DATE
, ORDERKEY
, TYPE
, Quantity
, PoReceiveHeaderGuid
)
VALUES ( 1,'PO1', '11', '2015/07/25', 1, 'HS', 10, NULL )
, ( 1,'PO1', '11', '2015/07/25', 2, 'BS', 10, NULL )
, ( 1,'PO1', '11', '2015/07/25', 3, 'HS2', 10, NULL )
, ( 1,'PO1', '11', '2015/07/26', 3, 'HS2', 10, NULL )
; WITH cteReceiveDistinct AS (
SELECT DISTINCT tr.JobNumberID
, tr.PONUMBER
, tr.DELIVNOTE
, tr.DATE
FROM #tReceive tr
WHERE tr.PoReceiveHeaderGuid IS NULL
)
, cteReceiveGuid AS (
SELECT NEWID() PoReceiveHeaderGuid
, rd.JobNumberID
, rd.PONUMBER
, rd.DELIVNOTE
, rd.DATE
FROM cteReceiveDistinct rd
)
--SELECT * FROM cteReceiveGuid --(comment out for update)
UPDATE #tReceive
SET PoReceiveHeaderGuid = hdr.PoReceiveHeaderGuid
FROM #tReceive tr
INNER JOIN cteReceiveGuid hdr ON tr.JobNumberID = hdr.JobNumberID
AND hdr.PONUMBER = tr.PONUMBER
AND hdr.DELIVNOTE = tr.DELIVNOTE
AND hdr.DATE = tr.DATE
SELECT DISTINCT tr.PONUMBER, tr.DELIVNOTE, tr.DATE , tr.PoReceiveHeaderGuid
FROM #tReceive tr
August 4, 2015 at 11:57 am
Remembering that a CTE is just another way to code sub-queries, your update with CTE is the same as this:
UPDATE #tReceive
SET PoReceiveHeaderGuid = hdr.PoReceiveHeaderGuid
FROM #tReceive tr
INNER JOIN
(
SELECT NEWID() PoReceiveHeaderGuid
, rd.JobNumberID
, rd.PONUMBER
, rd.DELIVNOTE
, rd.DATE
from
(
SELECT DISTINCT tr.JobNumberID
, tr.PONUMBER
, tr.DELIVNOTE
, tr.DATE
FROM #tReceive tr
WHERE tr.PoReceiveHeaderGuid IS NULL
) rd
) hdr
ON tr.JobNumberID = hdr.JobNumberID
AND hdr.PONUMBER = tr.PONUMBER
AND hdr.DELIVNOTE = tr.DELIVNOTE
AND hdr.DATE = tr.DATE
Both the above and your original CTE-based update should generate the same actual execution plan, since SQL just takes the CTE (since it is non-recursive) and unwinds it to a series of nested sub-queries.
I get unique, new GUIDs every time (whether with your query or mine) which is what I would expect.
Gerald Britton, Pluralsight courses
August 4, 2015 at 12:42 pm
Since your UPDATE statement does not work correctly, what should the results be based on the sample data you provided?
August 4, 2015 at 12:48 pm
Daryl AZ (8/4/2015)
It feels like the CTE is acting more like RBAR then a result. My best thought is the updating #tReceive from a CTE against #tReceive is somehow re-cursing. Populating another temp table works, as will inserting into POHeader but was surprised by results.
That's the way NEWID() was designed. It will give a different value on each row as the join generates a new instance of the function. As you found out, the only way to get it right is inserting in a different table to prevent the NEWID() being called for each row of the final query.
By the way, that's not RBAR. This is an example of the pseudo-cursors which allow tally tables, some string concatenations or the quirky update to work correctly.
August 4, 2015 at 1:07 pm
Original Expected results were lines 1 - 3 (key: PO1, 11, 2015/07/25) have one GUID and Line 4 (key po1, 11, 2015/07/26) have a different GUID.
Using the cteReceiveGuid for insert into my PoHeader table worked as expected.
I didn't think through that updating temp table from a CTE to same temp table would be recursive.
thanks for all the feedback/info.
August 4, 2015 at 1:11 pm
Daryl AZ (8/4/2015)
I didn't think through that updating temp table from a CTE to same temp table would be recursive.
It's not recursive.
August 4, 2015 at 1:19 pm
I understand now that this is newID() functionality.
I am familiar with using CTEs for recursion for ',' delimiting and see the difference. It just sorta, kinda, maybe feels like a cross between recursion and RBAR. Though it isn't...
thanks
August 4, 2015 at 1:55 pm
Daryl AZ (8/4/2015)
I understand now that this is newID() functionality.I am familiar with using CTEs for recursion for ',' delimiting and see the difference. It just sorta, kinda, maybe feels like a cross between recursion and RBAR. Though it isn't...
thanks
go reread my post from 6:57:51 PM
For non-recursive uses, CTEs are just another way to write subqueries. Nothing more.
Gerald Britton, Pluralsight courses
August 4, 2015 at 2:02 pm
How about this:
select * from #tReceive;
with basedata as (
select
rn = row_number() over (partition by JobNumberID, PONUMBER, DELIVNOTE, [DATE] order by ORDERKEY)
, JobNumberID
, PONUMBER
, DELIVNOTE
, DATE
, ORDERKEY
, TYPE
, Quantity
from
#tReceive
), rCTE as (
select
NEWID() PoReceiveHeaderGuid
, JobNumberID
, PONUMBER
, DELIVNOTE
, DATE
, ORDERKEY
, TYPE
, Quantity
, rn
from
basedata
where
rn = 1
union all
select
r.PoReceiveHeaderGuid
, bd.JobNumberID
, bd.PONUMBER
, bd.DELIVNOTE
, bd.DATE
, bd.ORDERKEY
, bd.TYPE
, bd.Quantity
, bd.rn
from
rCTE r
inner join basedata bd
on (r.JobNumberID = bd.JobNumberID and
r.PONUMBER = bd.PONUMBER and
r.DELIVNOTE = bd.DELIVNOTE and
r.[DATE] = bd.[DATE])
where
r.rn = case bd.rn when 1 then 0 else 1 end
)
--select
-- r.PoReceiveHeaderGuid
-- , r.JobNumberID
-- , r.PONUMBER
-- , r.DELIVNOTE
-- , r.DATE
-- , r.ORDERKEY
-- , r.TYPE
-- , r.Quantity
--from rCTE r
--order by
-- r.JobNumberID
-- , r.PONUMBER
-- , r.DELIVNOTE
-- , r.DATE
-- , r.ORDERKEY
update tr set
PoReceiveHeaderGuid = r.PoReceiveHeaderGuid
from
#tReceive tr
inner join rCTE r
on (tr.JobNumberID = r.JobNumberID and
tr.PONUMBER = r.PONUMBER and
tr.DELIVNOTE = r.DELIVNOTE and
tr.[DATE] = r.[DATE]);
select * from #tReceive;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply