Need help to checking data, and update column
-
My table and data as follow,
GO
/****** Object: Table [dbo].[process_ApplyJob_1] Script Date: 08/17/2014 23:57:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[process_ApplyJob_1]') AND type in (N'U'))
DROP TABLE [dbo].[process_ApplyJob_1]
GO
/****** Object: Table [dbo].[process_ApplyJob_1] Script Date: 08/17/2014 23:57:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[process_ApplyJob_1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[process_ApplyJob_1](
[batch_Id] [uniqueidentifier] NULL,
[SexID] [tinyint] NULL,
[Age] [tinyint] NULL,
[BMI] [decimal](18, 2) NULL,
[Bumiputera] [bit] NULL,
[IsParentTNBStaff] [bit] NULL,
[MaritalStatusID] [tinyint] NULL,
[WorkExperience] [int] NULL,
[r_SexID] [tinyint] NULL,
[r_minAge] [tinyint] NULL,
[r_maxAge] [tinyint] NULL,
[r_minBMI] [tinyint] NULL,
[r_maxBMI] [tinyint] NULL,
[r_Bumiputera] [bit] NULL,
[r_IsParentTNBStaff] [bit] NULL,
[r_MaritalStatusID] [tinyint] NULL,
[r_minWorkExperience] [int] NULL,
[r_maxWorkExperience] [int] NULL,
[checkingStatus] [bit] NULL
) ON [PRIMARY]
END
GO
INSERT [dbo].[process_ApplyJob_1] ([batch_Id], [SexID], [Age], [BMI], [Bumiputera], [IsParentTNBStaff], [MaritalStatusID], [WorkExperience], [r_SexID], [r_minAge], [r_maxAge], [r_minBMI], [r_maxBMI], [r_Bumiputera], [r_IsParentTNBStaff], [r_MaritalStatusID], [r_minWorkExperience], [r_maxWorkExperience], [checkingStatus]) VALUES (N'cb10c302-b591-4c41-84ce-059479ce110e', 1, 30, CAST(19.27 AS Decimal(18, 2)), 1, 0, 1, 6, 1, 30, 45, 15, 30, 1, 0, 4, 1, 36, NULL)
The checking as follow,
1- If SexID = r_SexID, then - PASS
2- If Age Between r_minAge AND r_maxAge - PASS
3- If BMI Between r_minBMI AND r_maxBMI - PASS
4- If Bumiputera = r_Bumiputera - PASS
5- If IsParentTNBStaff = r_IsParentTNBStaff - PASS
6- If MaritalStatusID = r_MaritalStatusID - PASS
7- If WorkExperience = r_WorkExperience - PASS
Consider below,
The Marital Status value is
1- Single = 1
2- Married = 2
3- Widow/Widower = 3
4- N/A = 4
However,
1- If MaritalStatus = 4 - No need to check. Just give PASS
*Only MaritalStatus = 1 OR MaritalStatus = 2 OR MaritalStatus = 3 need to checking.
1- If SexID = r_SexID, then - PASS
2- If Age Between r_minAge AND r_maxAge - PASS
3- If BMI Between r_minBMI AND r_maxBMI - PASS
4- If Bumiputera = r_Bumiputera - PASS
5- If IsParentTNBStaff = r_IsParentTNBStaff - PASS
6- If MaritalStatusID = r_MaritalStatusID - PASS
7- If WorkExperience = r_WorkExperience - PASS
If SexID, Age, BMI, Bumiputera, IsParentTNBStaff, MaritalStatusID, WorkExperience = ALL PASS,
Please
update set checkingStatus='TRUE' where batchId='cb10c302-b591-4c41-84ce-059479ce110e'
Please help me. I'm stuck :crying:
-
questions:
1. are you checking in your application first? (why send bad data to SQL?)
2. What have you tried so far (you need to make some effort here.)
Gerald Britton, Pluralsight courses
-
g.britton (8/17/2014)
questions:1. are you checking in your application first? (why send bad data to SQL?)
2. What have you tried so far (you need to make some effort here.)
I'm a SQL Server Beginner. So, I dump the data into Temp Table Above to make it easily. However, you said - Bad Data :(. Don't understand which one the bad data?
-
Your original post included a screenshot that looks like a windows application. Are you working on that application? If so, I would do the validation there and not in SQL Server. I would only send to SQL Server data that passed my error checking. That's what I mean by not sending bad data to the server. I would not write the data to a temp table then do the validation in SQL.
I understand that you are a beginner. That's great! We want to help you to learn. Part of that will be that you try to work it out and ask for help when things do not work as you expect. From your post I get the feeling that you are looking for a complete solution. That's what I won't do. It will not only take up valuable time but you won't learn nearly as much as trying to do it by yourself first.
When you post a question in this forum, you are expected to provide your work so far with an explanation of where you are getting stuck, not ask for a complete solution.
Please read this article about how to post questions.
http://www.sqlservercentral.com/articles/61537/
Gerald Britton, Pluralsight courses
-
g.britton (8/17/2014)
Your original post included a screenshot that looks like a windows application. Are you working on that application? If so, I would do the validation there and not in SQL Server. I would only send to SQL Server data that passed my error checking. That's what I mean by not sending bad data to the server. I would not write the data to a temp table then do the validation in SQL.I understand that you are a beginner. That's great! We want to help you to learn. Part of that will be that you try to work it out and ask for help when things do not work as you expect. From your post I get the feeling that you are looking for a complete solution. That's what I won't do. It will not only take up valuable time but you won't learn nearly as much as trying to do it by yourself first.
When you post a question in this forum, you are expected to provide your work so far with an explanation of where you are getting stuck, not ask for a complete solution.
Please read this article about how to post questions.
http://www.sqlservercentral.com/articles/61537/%5B/quote%5D
Your allegation about complete solution is not true. I've done filtering. Then, I dumped the data into temp table named process_ApplyJob_1.
Now, I'm looking for help to checking and compare column by column. If column comply with another column, then it's PASS.
Your criticize is not helping me. So, don't help
-
I'm sorry if you are offended. I suppose I'm simply confused.
If you have verified the accuracy of your data before sending it to SQL Server, why do you want to do it again in SQL?
FWIW the simple way to do the verification in SQL (even though it appears to be redundant), is to add check constraints to the columns. If that is insufficient, consider adding a WHERE clause to your UPDATE statement to implement the checks, so that only rows that pass all the checks will be flagged as OK in your table.
Gerald Britton, Pluralsight courses
-
:crying:
-
Based on what you posted try the below. The only way I could think of doing this was using a massive case statement. (I'm sure someone may come along with a better solution)
7- If WorkExperience = r_WorkExperience - PASS
I assumed you mean (from the table structure) workexperience between r_minWorkExperience and r_maxWorkExperience
A lot of the stuff below is just to show how I worked through it and you can see each individual pass / fail status
SELECT batch_Id ,CASE SexID
WHEN r_SexID THEN 'Pass'
ELSE 'Fail'
END AS SexCheck ,
CASE WHEN age BETWEEN r_minAge AND r_minAge THEN 'Pass'
ELSE 'Fail'
END AS AgeCheck ,
CASE WHEN BMI BETWEEN r_minBMI AND r_maxBMI THEN 'Pass'
ELSE 'Fail'
END AS BMICheck ,
CASE Bumiputera
WHEN r_Bumiputera THEN 'Pass'
ELSE 'Fail'
END AS BumiputeraCheck ,
CASE IsParentTNBStaff
WHEN r_IsParentTNBStaff THEN 'Pass'
ELSE 'Fail'
END AS IsPartentTNBStaffCheck ,
CASE WHEN MaritalStatusID = 4 THEN 'Pass'
WHEN MaritalStatusID = r_MaritalStatusID THEN 'Pass'
ELSE 'Fail'
END AS MaritalStatusCheck ,
CASE WHEN WorkExperience BETWEEN r_minWorkExperience
AND r_maxWorkExperience THEN 'Pass'
ELSE 'Fail'
END AS WorkExperienceCheck ,
CASE WHEN ( SexID = r_SexID )
AND ( Age BETWEEN r_minAge AND r_maxAge )
AND ( BMI BETWEEN r_minBMI AND r_maxBMI )
AND ( Bumiputera = r_Bumiputera )
AND ( IsParentTNBStaff = r_IsParentTNBStaff )
AND ( MaritalStatusID = 4
OR MaritalStatusID = r_MaritalStatusID
)
AND ( WorkExperience BETWEEN r_minWorkExperience
AND r_maxWorkExperience )
THEN 'Overall Pass'
ELSE 'Overall Fail'
END AS OverallCheck
FROM dbo.process_ApplyJob_1
UPDATE dbo.process_ApplyJob_1
SET checkingStatus = CASE WHEN ( SexID = r_SexID )
AND ( Age BETWEEN r_minAge AND r_maxAge )
AND ( BMI BETWEEN r_minBMI AND r_maxBMI )
AND ( Bumiputera = r_Bumiputera )
AND ( IsParentTNBStaff = r_IsParentTNBStaff )
AND ( MaritalStatusID = 4
OR MaritalStatusID = r_MaritalStatusID
)
AND ( WorkExperience BETWEEN r_minWorkExperience
AND r_maxWorkExperience )
THEN 'True'
ELSE 'False'
END
SELECT * FROM dbo.process_ApplyJob_1
-
or you could just do this 🙂
UPDATE dbo.process_ApplyJob_1SET checkingStatus = 1
WHERE ( SexID = r_SexID )
AND ( Age BETWEEN r_minAge AND r_maxAge )
AND ( BMI BETWEEN r_minBMI AND r_maxBMI )
AND ( Bumiputera = r_Bumiputera )
AND ( IsParentTNBStaff = r_IsParentTNBStaff )
AND ( MaritalStatusID = 4
OR MaritalStatusID = r_MaritalStatusID
)
AND ( WorkExperience BETWEEN r_minWorkExperience
AND r_maxWorkExperience )
-
...or add the equivalent check constraints and let SQL Server do it for you...
-
gbritton1 (8/18/2014)
...or add the equivalent check constraints and let SQL Server do it for you...Whilst this is fine and dandy when an insert occurs which fails the checks an error will be returned when trying to insert which will need to be handled via the application and also you would have no visibility afterwards of the attempt as it would never get inserted.
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "MarriageCheck". The conflict occurred in database "tempdb", table "dbo.process_ApplyJob_1".
-
higgim (8/18/2014)
gbritton1 (8/18/2014)
...or add the equivalent check constraints and let SQL Server do it for you...Whilst this is fine and dandy when an insert occurs which fails the checks an error will be returned when trying to insert which will need to be handled via the application and also you would have no visibility afterwards of the attempt as it would never get inserted.
Actually I think its more than that. Why would you want to insert invalid data into a table (and then reject it later)? Why not use check constraints and wrap the INSERT In a TRY..CATCH then do what you want ... including logging the error for later visibility and not returning an error to the application? But considering that the OP already validates the data at the application layer, why do it again in SQL Server?
-
gbritton1 (8/18/2014)
higgim (8/18/2014)
gbritton1 (8/18/2014)
...or add the equivalent check constraints and let SQL Server do it for you...Whilst this is fine and dandy when an insert occurs which fails the checks an error will be returned when trying to insert which will need to be handled via the application and also you would have no visibility afterwards of the attempt as it would never get inserted.
Actually I think its more than that. Why would you want to insert invalid data into a table (and then reject it later)? Why not use check constraints and wrap the INSERT In a TRY..CATCH then do what you want ... including logging the error for later visibility and not returning an error to the application? But considering that the OP already validates the data at the application layer, why do it again in SQL Server?
I cant see anything that says the input is checked in the application first. If it were checked then there is no need to do an insert anywhere, it would be a case of a quick update statement. If it's not checked in the application then this could still be done via an update statement with the try catch block and again negate the need for a temp table.
That said, there is not enough information to draw a full conclusion based on what the op has said as we don't know if changes can be made to the application or if error checking is taking place prior to submission to the database.
Whilst the op may not be approaching the problem using the best solution available, we have given advice and a working solution based on requirements and the information available.
-
When I asked him, the OP said, "I've done filtering. Then, I dumped the data into temp table named process_ApplyJob_1. " I understood "filtering" to mean validation. OF course I could be wrong.
-
Dear Mr Higgam,
You're helping me sir. Thanks to you. But no thanks to other one.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply