November 16, 2012 at 2:39 pm
I have a staging table which has unprocessed records of the batch..
I need a query which should validate the unprocess records and log the records into another table which is not valid records and also need to log the passed and failed count.
Below is the Intial state of staging table.
staging Table: smaster
Id Sid name status validated BatchID createddt created_by
--- ------- ----------- ------- -------- ------- --------------------- -------------
931 100.2.4 Siteipname New 0 NULL 2012-11-16 14:00:40.800 Test
932 Siteppname New 0 NULL 2012-11-16 14:00:40.800 Test
933 100.2.3 Siteipname Modify 0 NULL 2012-11-16 14:00:40.800 Test
934 100.2.5 Modify 0 NULL 2012-11-16 14:00:40.800 Test
Expected output
Validation rules:
If the column values for sid or name is null then log the counts of passed and failed records in the sdetail.
Log the into slog table with msg and SID value.
Table:smaster
Id Sid name status validated BatchID createddt created_by
--- ------- ----------- ------- -------- ------- --------------------- -------------
931 100.2.4 Siteipname New 1 1 2012-11-16 14:00:40.800 Test
932 Siteppname New 1 1 2012-11-16 14:00:40.800 Test
933 100.2.3 Siteipname Modify 1 1 2012-11-16 14:00:40.800 Test
934 100.2.5 Modify 1 1 2012-11-16 14:00:40.800 Test
Table:SDetail
BatchID passedcount failedcount createddtcreated_by
------- ----------- ----------- ----------------------- ----------
1 2 2 2012-11-16 14:00:40.800 Test
Table:Slog
ErrorID SID Msgcreateddt created_by Batchid
-------- --- ----------------------------------------------------------- ------------ --------
1 0 invalid Sid is required field2012-11-16 14:00:40.800 Test 1
2 100.2.5 Sitename is required and cannot be null 2012-11-16 14:00:40.800 Test 1
thanks for your help
November 21, 2012 at 8:36 am
Are you able to alter the staging table to add an extra column in there? If so there is a nice solution to this similar to the one we use
November 21, 2012 at 10:26 am
I can alter the staging table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply