November 27, 2013 at 11:09 am
I have a strange, perhaps too complex query (any suggestions gladly welcomed...) meant to catch differences in two tables. POLineDetail contains updates (extracted from an SSRS report...) to tblPO and tblPOLine. This query is meant to log the actions that are about to take place (in other words, what did the update change....)
I expected this to build a POImportLogMessage that identified changes found in the update stream (adds, changes to individual fields, etc.) There may be more than one change per update, and this query is meant to find those as well, concatenating multiple reasons.
As it happens, it ONLY captures new PO Lines, never anything else. It turns out that my test table contains 5 rows where the Work Order (WOID) has changed, and should have triggered a "New Work Order" message.
It does not capture those. POImportLogMessage is blank for those five rows....What did I do wrong?
SS2k12, tblPOLine has maybe 10,000 rows, POLineDetail has maybe 150 rows....FWIW
BEGIN TRANSACTION
INSERT INTO tblPOImportlog(POImportLogDateTime
,POImportPONbr
,POImportPOLineNbr
,POImportLogMessage)
SELECT GETDATE()
,I.PONUM
,I.POLINENUM
,CASE
WHEN l.POID IS NULL
THEN 'Adding New PO Line to CPAS'
ELSE CASE
WHEN I.WOID != L.WOID
THEN 'New Work Order '
ELSE ''
END + CASE
WHEN I.ProjectID != L.ProjectID
THEN 'New Project '
ELSE ''
END + CASE
WHEN ROUND(I.LOCALLINECOST,2)
!= ROUND(isnull(POLineDollarValue,0),2)
THEN 'New PO Amt (Old ' + format(POLineDollarValue,'C') + ' New ' + format(I.LOCALLINECOST,'C') + ' '
ELSE ''
END + CASE
WHEN ROUND(I.PAIDLOCAL,2)
!= ROUND(isnull(L.POLineAmtPaid,0),2)
THEN 'New Paid Amt (Old ' + format(L.POLineAmtPaid,'C') + ' New ' + format(I.PAIDLOCAL,'C') + ' '
ELSE ''
END
END
FROM POLineDetail AS I
LEFT JOIN tblPOLine AS L
ON I.POID = L.POID
AND I.POLINENUM = L.POLineNbr;
SELECT *
FROM tblPOImportLog
WHERE POImportLogDateTime
> DATEADD(d,-1,GETDATE())
ORDER BY POImportLogID DESC;
ROLLBACK TRANSACTION
Jim
November 27, 2013 at 11:35 am
Jim i see the join critieria is this:
LEFT JOIN tblPOLine AS L
ON I.POID = L.POID
AND I.POLINENUM = L.POLineNbr;
so i'd expect that when it's not found, the [L] related aliased data would be NULL;
but your case statement is testing for them to be not equal as part of the ELSE, which would never happen, right?
CASE
WHEN l.POID IS NULL
THEN 'Adding New PO Line to CPAS'
ELSE CASE
WHEN I.WOID != L.WOID
THEN 'New Work Order '
ELSE ''
END
maybe you want to change that to test IF NOT EXISTS instead for inserting data?
Lowell
November 27, 2013 at 11:58 am
It's a left join, so there will be I rows without L rows. In that case, I'm done, and need only one message ('Adding New PO Line to CPAS'). This works properly.
Where POID is not null is when I want to test for various differences... Am I misunderstanding?
Jim
November 27, 2013 at 12:04 pm
JimS-Indy (11/27/2013)
It's a left join, so there will be I rows without L rows. In that case, I'm done, and need only one message ('Adding New PO Line to CPAS'). This works properly.Where POID is not null is when I want to test for various differences... Am I misunderstanding?
mea culpa; my eyes crossed reading POID/WOID and confused the two to be the same;
looking deeper now that you've explained it.
Lowell
November 27, 2013 at 12:52 pm
My plan is to do an "Upsert" using the new "Merge" statement. Haven't gotten to it yet....
Anybody?
Jim
November 27, 2013 at 1:20 pm
I reformatted the query to be slightly more readable (I'd say).
From my point of view there's no need to add all those extra nested ELSE CASE stuff since this will reduce readability...
I'm not sure if this is what you're looking for in terms of business logic...
SELECT GETDATE()
,I.PONUM
,I.POLINENUM
,CASE
WHEN l.POID IS NULL THEN 'Adding New PO Line to CPAS'
WHEN I.WOID != L.WOID THEN 'New Work Order '
ELSE ''
END
+ CASE
WHEN ROUND(I.LOCALLINECOST,2) != ROUND(isnull(POLineDollarValue,0),2) AND l.POID IS NOT NULL
THEN 'New PO Amt (Old ' + format(POLineDollarValue,'C') + ' New ' + format(I.LOCALLINECOST,'C') + ' '
ELSE ''
END
+ CASE
WHEN ROUND(I.PAIDLOCAL,2) != ROUND(isnull(L.POLineAmtPaid,0),2) AND l.POID IS NOT NULL
THEN 'New Paid Amt (Old ' + format(L.POLineAmtPaid,'C') + ' New ' + format(I.PAIDLOCAL,'C') + ' '
ELSE ''
END
FROM POLineDetail AS I
LEFT JOIN tblPOLine AS L
ON I.POID = L.POID
AND I.POLINENUM = L.POLineNbr;
November 27, 2013 at 8:48 pm
I like your reformat. Looks like the query actually works except for some difficulty comparing "real" with "float" data types.
Looks like I have it under control. Thanks for the help, guys.
Jim
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply