T-SQL Doesn't Catch Condition

Question

Post reply

T-SQL Doesn't Catch Condition

JimS-Indy

SSCarpal Tunnel

Points: 4514
More actions
November 27, 2013 at 11:09 am

#279555

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JimS-Indy SSCarpal Tunnel Points: 4514 More actions · Answer 2

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

Lowell SSC Guru Points: 323518 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JimS-Indy SSCarpal Tunnel Points: 4514 More actions · Answer 4

My plan is to do an "Upsert" using the new "Merge" statement. Haven't gotten to it yet....

Anybody?

Jim

LutzM SSC Guru Points: 107049 More actions · Answer 5

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;

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

JimS-Indy SSCarpal Tunnel Points: 4514 More actions · Answer 6

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