December 5, 2011 at 8:05 am
HI All I am trying to generate a customer error column which will be generated via code only and no table level checks. I want to loop through the rows and generate the message.
I am getting stuck with the code, help would be much appreciated.
DataTable: Name HRMaster
IDNameEmpNoHR_IDErrorMessage
1John10005123456789'EmpNo- Incorrect'
2Tim330222123456'HR ID- Incorrect'
3Harry22298765432'EmpNo-Incorrect'/ 'HR ID- Incorrect'
DECLARE @MaxID AS int
DECLARE @MinID AS int
DECLARE @EmpNo AS varchar(100)
DECLARE @HR_ID AS varchar(100)
DECLARE @ErrorEmpNo varchar(200)
DECLARE @ErrorHR_ID varchar(200)
DECLARE @CompleteErrorMsg varchar(200)
-- Initiate MinID and MaxID to loop through the rows of data
SELECT
@MinID=MIN(ID),@MaxID=MAX(ID)
FROM
dbo.stgTemplate
-- While loop to generate the error messages
WHILE @MinID <= @MaxID
BEGIN
-- Assign Source CLI and Destination CLI to the variables for a specific ID
SELECT
@EmpNo=EmpNo ,@HR_ID=HR_ID
FROM
dbo.stgTemplate
WHERE
ID=@MinID
-------This is the section I am not sure of the code .. tried combinations but unable to populate the variable @CompleteErrorMsg
IF (LEN(@EmpNo) < 10 OR LEN(@EmpNo) > 11)
BEGIN
SET @ErrorEmpNo ='Error Emp number'
END
IF (LEN(@HR_ID) < 10 OR LEN(@HR_ID) > 11)
BEGIN
SET @ErrorHR_ID ='Error HR ID'
END
ELSE
GOTO Success
-------------------------------
SET @CompleteErrorMsg=@ErrorEmpNo + @ErrorHR_ID
Failed:
UPDATE
dbo.HRMaster
SET
ErrorMessage = @CompleteErrorMsg
WHERE
ID=@MinID
---
Success:
SET @MinId = @MinId + 1
END
December 5, 2011 at 8:10 am
i'm not sure why you wnat to loop; you could do the entire table in a single pass
something like htis:
SELECT ' Invalid data: record '
+ convert(varchar,id)
+ 'contains an invalidly formatted HR_ID ',*
FROM HRMaster
WHERE LEN(HR_ID) <> 10
UNION
SELECT ' Invalid data: record '
+ convert(varchar,id)
+ 'contains an invalidly formatted EmpNo ',*
FROM HRMaster
WHERE LEN(EmpNo ) <> 10
Lowell
December 5, 2011 at 8:18 am
Hi Lowell,
Many thanks for the code .. I am looking to loop as I will have a number of such columns and I will want to concatenate the error messages in the one column but concatenating the error messages whenever they arise for each row.
Thanks ..
December 5, 2011 at 8:20 am
I have tried your code ... many thanks again .. it works great but it returns multiple rows with 'Error Messages'.
For my purposes I would want to have the one Error Message which lists out all the columns which have wrong data in them.
December 5, 2011 at 8:59 am
Hi,
How many fields do you need to look at? If there aren't too many you could use a CASE statement, given you work the logic out correctly:
SELECTEmp_No,
HR_ID,
CASE
WHEN LEN(Emp_No) < 7 AND LEN(HR_ID) < 7 THEN 'Emp_No: Too Short, HR_ID: Too Short'
WHEN LEN(Emp_No) < 7 THEN 'Emp_No: Too Short'
WHEN LEN(HR_ID) < 7 THEN 'HR_ID: Too Short'
END AS Errors
FROM
(SELECT 12345 AS Emp_No, 'abcdef' AS HR_ID) testData
Hope this helps..
Cheers,
Jim.
December 5, 2011 at 9:04 am
aarionsql (12/5/2011)
Hi Lowell,Many thanks for the code .. I am looking to loop as I will have a number of such columns and I will want to concatenate the error messages in the one column but concatenating the error messages whenever they arise for each row.
Thanks ..
You can do this by using CASE "functions".
SELECT
CASE WHEN LEN(HR_ID) <> 10
THEN ' Invalid data: record '
+ convert(varchar,id)
+ 'contains an invalidly formatted HR_ID '
ELSE ''
END
+CASE WHEN LEN(EmpNo ) <> 10
THEN ' Invalid data: record '
+ convert(varchar,id)
+ 'contains an invalidly formatted EmpNo '
ELSE ''
END
FROM HRMaster
If necessary, you can use correlated subqueries to validate your data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 5, 2011 at 9:05 am
Hi Jim,
Many thanks for your help. I should have mentioned I have gone down the route of CASE statements .. but there are far too many columns to look up which is why I am looking at the Script option.
Thank you
December 5, 2011 at 9:07 am
aarionsql (12/5/2011)
Hi Jim,Many thanks for your help. I should have mentioned I have gone down the route of CASE statements .. but there are far too many columns to look up which is why I am looking at the Script option.
Thank you
I've seen case expression go over 1000 lines. How can that possibly not be enough?
Looks like you are going down the wrong road. What's the big goal here?
December 5, 2011 at 9:07 am
Jim-720070 (12/5/2011)
Hi,How many fields do you need to look at? If there aren't too many you could use a CASE statement, given you work the logic out correctly:
SELECTEmp_No,
HR_ID,
CASE
WHEN LEN(Emp_No) < 7 AND LEN(HR_ID) < 7 THEN 'Emp_No: Too Short, HR_ID: Too Short'
WHEN LEN(Emp_No) < 7 THEN 'Emp_No: Too Short'
WHEN LEN(HR_ID) < 7 THEN 'HR_ID: Too Short'
END AS Errors
FROM
(SELECT 12345 AS Emp_No, 'abcdef' AS HR_ID) testData
Hope this helps..
Cheers,
Jim.
You are much better off specifying a separate CASE function for each error condition. Your approach gets unwieldy very quickly and is almost impossible to maintain if you add new error conditions. Specifying a separate CASE function for each error condition makes it very easy to ensure you have all of the possible conditions and it's also very easy to add new errors.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 5, 2011 at 9:10 am
Thanks Drew, yes I need them in one column and I have too many columns to utilise the CASE statement. Going forward for a non-technical person to maintain it or debug the code it will become an issue.
That's why I was looking at the code.
December 5, 2011 at 9:12 am
Hi .. I have written it out with CASE statements but if I could sort out the one issue where the variable is going wrong then I could get the code running and easily add other conditions to it .. with the CASE staements I would have to duplicate the code a number of times in order to catch all possible error scenarios
December 5, 2011 at 9:14 am
aarionsql (12/5/2011)
Hi Jim,Many thanks for your help. I should have mentioned I have gone down the route of CASE statements .. but there are far too many columns to look up which is why I am looking at the Script option.
Thank you
If you use a separate CASE function for each error, it's not going to be any more difficult to specify the error conditions using a CASE function than it is to specify using a loop and the CASE is going to be much more efficient.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 5, 2011 at 9:18 am
aarionsql (12/5/2011)
Thanks Drew, yes I need them in one column and I have too many columns to utilise the CASE statement. Going forward for a non-technical person to maintain it or debug the code it will become an issue.That's why I was looking at the code.
Do anticipate having non-technical people maintaining your queries? And why not use check constraints?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2011 at 9:19 am
HI Drew,
can I have a join in a CASE statement?
December 5, 2011 at 9:22 am
Hi Sean,
That's not an option as data will be copy pasted across from various data sources directly into the table.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply