implementing checks on table content

[ck9663]

question....

i have a table with around 130 columns x (smallest, 20K, biggest 15M) rows.

i need to check around 15 of those columns. if one or more columns is "doubtful" based on some business rules, i have to flag that row/record as "doubtful". i need a unique error code to identify that, that row is doubtful because of such and such column. if 3 column failed the check, that means i have to identify those 3 errors for later analysis.

have you guys encountered something like this? i don't need the actual script, just the technique...

thanks,

--CK

[iburyak]

question....

i have a table with around 130 columns x (smallest, 20K, biggest 15M) rows.

i need to check around 15 of those columns. if one or more columns is "doubtful" based on some business rules, i have to flag that row/record as "doubtful". i need a unique error code to identify that, that row is doubtful because of such and such column. if 3 column failed the check, that means i have to identify those 3 errors for later analysis.

have you guys encountered something like this? i don't need the actual script, just the technique...

thanks,

--CK

What do you think about this?

Update table_name set
Frlag =
Case when col1 <> failure condition then ‘ ’ else ‘1’ end &
Case when col2 <> failure condition then ‘ ’ else ‘2’ end &
Case when col3 <> failure condition then ‘ ’ else ‘3’ end &

…..

You can use instead of a number a real column names if you wish.
When you try to get records that have doubt in column 3 do following:

Select * from table_name where substring(flag, 3, 1) <> ‘ ‘

You could think of multiple variations of the same. It is just an idea to chew on.
Don’t forget that after 9 you will have double digits… :)

Good Luck.

[ck9663]

What do you think about this?



You can use instead of a number a real column names if you wish.
When you try to get records that have doubt in column 3 do following:



You could think of multiple variations of the same. It is just an idea to chew on.
Don’t forget that after 9 you will have double digits… :)

Good Luck.

thanks...but that will only store one error...in your example, i need to keep track of all those three records/error code...that corresponds with the error on it's respective fields...

any more ideas?

[iburyak]

Nope, it is not one error. In example below you put error for each column with its own criteria of failure and a space if error in specific column was not found. & - appends errors and spaces together in one field.


Case when col1 <> failure condition then ‘ ’ else ‘1’ end &
Case when col2 <> failure condition then ‘ ’ else ‘2’ end &
Case when col3 <> failure condition then ‘ ’ else ‘3’ end &


For example if you have errors in all columns result should be
123
If error in column 1 and 3 result should be:
1 3

You have to expend my code up to the number of columns you have.

Hope it helps.

[ck9663]

Nope, it is not one error. In example below you put error for each column with its own criteria of failure and a space if error in specific column was not found. & - appends errors and spaces together in one field.


Case when col1 <> failure condition then ‘ ’ else ‘1’ end &
Case when col2 <> failure condition then ‘ ’ else ‘2’ end &
Case when col3 <> failure condition then ‘ ’ else ‘3’ end &


For example if you have errors in all columns result should be
123
If error in column 1 and 3 result should be:
1 3

You have to expend my code up to the number of columns you have.

Hope it helps.

sorry, i missed the "&" sign...this means i will have one 15-character length field...that also means to analyze the table, i'd go WHERE substring(error field,positiono ferrorflag,1) = '1'

thanks....

any other takers?

[Motoma]

sorry, i missed the "&" sign...this means i will have one 15-character length field...that also means to analyze the table, i'd go WHERE substring(error field,positiono ferrorflag,1) = '1'

thanks....

any other takers?

Try making a view which does a query similar to this:

[CODE=sql]
SELECT ID,
CASE WHEN Age < 13 THEN 1 ELSE 0 END AS LessThanThirtee n,
CASE WHEN Age < 18 THEN 1 ELSE 0 END AS CantSmoke,
CASE WHEN Age < 21 THEN 1 ELSE 0 END AS CantDrink
FROM ChildTable
[/code]

You can then query that view like so:

[code=sql]
SELECT ID FROM ChildView WHERE LessThanThirtee n + CantSmoke + CantDrink > 0
[/code]

Which will return all rows that have at least one error.