0

Is there a good way to create a "table of conditions"?

I have a huge table, where 4-5 columns are considered in a set of rules, and based on those columns default values must appear in other columns. The update part, I can do.

I would like for the whole thing to be user maintainable: they should be able to open a table and change, delete or add rules. They can do it with a table, but they can't edit a code.

A simplified version would be something like this:

If ColumnA = 1 AND ColumnB = 2 THEN X
If ColumnA = 1 AND ColumnB IS empty (NULL) THEN Y
If Column A = 3 AND ColumnB IS anything THEN Z

Is there a nice way to do this? I can programmatically loop through the rows and construct update queries, but it will be desperately slow, and will run as many updates as many condition rows there are. Seems wasteful.

I came up with a token for "anything": (*), and I can use this in a horrid SQL, but I was wondering if there was a better way to do this.

SELECT M.*
FROM MainData M
INNER JOIN Rules R
ON ((M.Check2 = R.C2) OR R.C2="(*)" OR (R.C2 IS NULL AND M.Check2 IS NULL))
  AND ((M.Check1 = R.C1) OR R.C1="(*)" OR (R.C1 IS NULL AND M.Check1 IS NULL))

To make matters worse this is MS Access, but I'm actually interested in the concept at this point.

  • 1
    I am not sure what you are going for with the rules table here. For the example with the IF's you could go for CASE WHEN statements. (CASE WHEN ColumnA = 1 AND ColumnB = 2 THEN X WHEN ColumnA = 1 AND ColumnB IS empty (NULL) THEN Y WHEN Column A = 3 THEN Z END AS Column. I would really try to refrain from using OR's and AND's like the last example, for performance reasons. – Randi Vertongen Sep 30 at 14:48
  • @RandiVertongen The point is I don't want to hardcode the 80+ rules for the 80+ different outcomes. Not just out of lazyness, I want it to be user editable. I can tell the users they need to add a new row in such and such table if they want a new rule. I should add this to the question... – vacip Sep 30 at 15:21
  • We would really need a real world / working example. If a user adds a rule, you would have to change the query anyways right? Could you add an example with these rules and adding a rule? It looks like you want to add columns to both tables when a rule is added – Randi Vertongen Sep 30 at 16:45
  • 1
    You could parse records from the table and use dynamic SQL to formulate your final query, but as my post on this question states, it's best to avoid Dynamic SQL in production code as you WILL RUN INTO ISSUES. – John Eisbrener Sep 30 at 18:50
  • @JohnEisbrener Thank you. I too am wary of dynamic SQL. I'll try to come up with a table that I can just join. – vacip Oct 1 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.