February 4, 2014 at 9:35 am
Afternoon all,
I need to be able to test columns in a table to see if they contain the correct data, if not send an email.
A bit of background, I populate a table with order numbers, pallet counts, addresses, etc. This then gets output to a text file and ftp'd to a third party.
The issue I'm trying to resolve is that some of the columns contain manditory data, in set formats, if one is incorrect, say, a pallet count of zero, the third party return the file, I want to check prior to sending, thus saving time and effort.
I could write a bunch of IF statements, one for each field or as I am currently trying, having two queries producing CHECKSUMs one just including all my manditory columns and one surrounding the manditory columns with NULLIF, which if a column is wrong will produce a different CHECKSUM
Is there a better way?
Ta
David
February 4, 2014 at 10:01 am
Hi David welcome to the forums. Please read the article in my signature about posting questions to the forum this will help us in solving your question. Without knowledge of your table structure or the columns that we are comparing or the valid vs. invalid data you could do something like this:
select
TableID
,case when column data is invalid then 1 else 0 end as Col1Wrong
,... as Col2Wrong
,... etc
from table
where
col1 data is invalid
or
col2 data is invalid
or
... etc
February 4, 2014 at 10:19 am
Hi Keith,
I'll get on and read the artical.. 🙂
In the meantime I have been working with this as an example. The first row is correct, second has no order number, third has no pallet count and the fourth has no weight. If i run the SELECT at the bottom, the CHECKSUMs are different, I could use this as the criteria for sending an email.
CREATE TABLE OrdersTest(OrderNo CHAR(10),
PalletCount NUMERIC,
WeightKG NUMERIC)
INSERT INTO OrdersTest VALUES ('abc1234567', 10, 1000)
INSERT INTO OrdersTest VALUES (' ', 10, 1000)
INSERT INTO OrdersTest VALUES ('def1234567', 0, 1000)
INSERT INTO OrdersTest VALUES ('ghi1234567', 10, 0)
SELECT'Checksum' = CHECKSUM(OrderNo,PalletCount, WeightKG),
'TestedChecksum' = CHECKSUM(NULLIF(OrderNo,' '),NULLIF(PalletCount, 0), NULLIF(WeightKG, 0))
FROM OrdersTest
Is there a better way of doing this?
February 4, 2014 at 10:34 am
first stab at the issue:
SELECT OrdersTest.*,
CASE
WHEN RTRIM(ISNULL(OrderNo,'')) = ''
THEN 'Blank Order Number'
ELSE
''
END AS OrderNumberIncorrect,
CASE
WHEN ISNULL(PalletCount,0) = 0
THEN 'Pallet Count Incorrect'
ELSE ''
END AS PalletCountIncorrect,
CASE
WHEN ISNULL(WeightKG,0) = 0
THEN 'WeightKG Incorrect'
ELSE
''
END AS WeightKGIncorrect
FROM OrdersTest WHERE RTRIM(ISNULL(OrderNo,'')) = ''
OR ISNULL(PalletCount,0) = 0
OR ISNULL(WeightKG,0) = 0
Lowell
February 4, 2014 at 10:38 am
February 4, 2014 at 10:42 am
performance wise, this would be a little better, since the OR statement forces a table scan, and this would/could take advantage of existing indexes instead.
but it's effectively the same approach.
SELECT
OrdersTest.*,
CASE
WHEN RTRIM(ISNULL(OrderNo, '')) = ''
THEN
'Blank Order Number'
ELSE
''
END AS Problem
FROM OrdersTest
WHERE RTRIM(ISNULL(OrderNo, '')) = ''
UNION ALL
SELECT
OrdersTest.*,
CASE
WHEN ISNULL(PalletCount, 0) = 0
THEN
'Pallet Count Incorrect'
ELSE
''
END AS Problem
FROM OrdersTest
WHERE ISNULL(PalletCount, 0) = 0
UNION ALL
SELECT
OrdersTest.*,
CASE
WHEN ISNULL(WeightKG, 0) = 0
THEN
'WeightKG Incorrect'
ELSE
''
END AS Problem
FROM OrdersTest
WHERE ISNULL(WeightKG, 0) = 0
Lowell
February 10, 2014 at 10:35 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply