I have a requirement where I need to capture all failing validations in SQL database.
I have below table:
department age IT 27 IT 25 Marketing 29 Fashion 30 IT 32 IT 28
My validation rules are as follows:
- Department should be IT
- Age should be in 20s
- Age should not be 25
These are sample validation and my requirement is to capture all failed records along with failing conditions, I have achieved it by using union as follows:
select department , age , 'Age is 25' as FailedValidation, 1 as FailedValidationId from CENTRALFINANCETAX_IM_DEV.salluTest where age = 25 union select department , age, 'Age is not in 20s' as FailedValidation, 2 as FailedValidationId from CENTRALFINANCETAX_IM_DEV.salluTest where age > 29 union select department , age , 'Dept not IT' as FailedValidation, 3 as FailedValidationId from CENTRALFINANCETAX_IM_DEV.salluTest where department != "IT"
Below is the result achieved:
department age FailedValidation FailedValidationId IT 32 Age is not in 20s 2 Marketing 29 Dept not IT 3 Fashion 30 Dept not IT 3 Fashion 30 Age is not in 20s 2 IT 25 Age is 25 1
But, if the validations increase I have to add many select query.
Is there any alternative way with better performance?
Advertisement
Answer
You could use cross apply
:
select t.*, x.* from centralfinancetax_im_dev.sallutest t cross apply (values (1, case when t.age = 25 then 'Age is 25' end), (2, case when t.age > 29 then 'Age is not in 20s' end), (3, case when t.department <> 'IT' then 'Dept not IT' end) ) x(FailedValidationId, FailedValidation) where x.FailedValidation is not null