Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-05 : 08:08:20
|
| Gero writes "I have a table containing several "rules":# | ItemType | ItemStatus | ItemAuthor | RulePriority | etc...1 | null | null | null | 5 | etc...2 | 5 | 1 | 245 | 10 | etc...3 | 5 | null | null | 20 | etc...Now a record is submitted...ID | ItemType | ItemStatus | ItemAuthor | etc...1 | 5 | 0 | 134 | etc...or: 2 | 5 | 1 | 89 | etc...or: 3 | 4 | 0 | 234 | etc...or: 4 | 5 | 0 | 245 | etc......and the rules are checked to see if something matches.According to "ItemType" (5) both rules (#2 & #3) would apply. However, since rule #2 requires additional parameters that do not match (ItemStatus 1 & ItemAuthor 245) and rule #3 does *not* require additional parameters rule #3 should apply. (There will always be only ONE rule returned based on the RulePriority just in case multiple rules apply.) If that's not confusing enough here's the kicker: If no rule matches anything rule #1 should apply since all of the rules parameters are null.Ok, here's how it should look for the other submitted records:Record 2 = Rule 3Record 3 = Rule 1Record 4 = Rule 3How do I do that *without* resorting to dynamic SQL?!? I did try this:SELECT * FROM tableWHERE (ItemType IS NULL OR ItemType = @ItemType) AND (ItemStatus IS NULL OR ItemStatus = @ItemStatus) AND (ItemAuthor IS NULL OR ItemAuthor = @ItemAuthor)but it doesn't work the way I need it to...A fundimental change of the architecture is still possible in order to accomodate this problem. So if some other approach will do the trick, please don't hold back! ;)Thanks!" |
|
|
Jay99
468 Posts |
Posted - 2002-04-05 : 09:04:53
|
If I understand this correctly, you can do it withough dynamic SQL, you just need to encapsulate the biz rules in your where clause. I'll try to get you started.select <columnlist>from <tablename>where -- ALL NULL apply rule 1 (ItemType is null and ItemStatus is null and ItemAuthor is null) OR -- Apply rule 2 (ItemType = 5 and ItemStatus is not null and ItemAuthor is not null) or -- Apply rule 3 (ItemType = 5 and ItemStatus is null and ItemAuthor is null) Now, what is missing here is the 'application' of the rules, but from your post, I can't really tell what that means. I hope this helps. If not, please post back with more (maybe clearer :) ) details . . .Jay<O> |
 |
|
|
gsnk
Starting Member
24 Posts |
Posted - 2002-04-05 : 22:25:00
|
| Alright... let me try to make it a bit clearer:The background to this question is a piece of functionality in an online project mgmt software. Users on the system are able to define certain rules that will determine how the notification for trouble tickets that are submitted in the system will be handled. In other words, I as a user could define a low priority trouble ticket to be sent to my work email. However, if a low priority ticket is being submitted and my CEO is the author I want to be notified on my pager immediately. The way that would look in the record is something like this:Let's assume the record that needs to be compared looks like this:ItemType = 5 (= Trouble Ticket)ItemStatus = 1 (= low priority)ItemAuthor = 123 (= UserID of CEO)the "rules" table contains--among others--these records:# | ItemType | ItemStatus | ItemAuthor | RulePriority | etc... 1 | null | null | null | 5 | etc... 2 | 5 | 1 | 123 | 10 | etc... 3 | 5 | 1 | null | 20 | etc...(Rule #1 is a default rule that will apply if no other rule applies)(RulePriority is a field in the "rules" table that will enable the system to return ONE rule from multiple matches.)In order to accomplish the requirement ("send all low priority tickets to email A but any low priority from CEO to email B") I will have to compare the record to the 3 rules above. rule #3 tells me that *any* trouble ticket (5) that is a low priority (1) will match it. rule #2 tells me that a low priority trouble ticket from the CEO will match.I hope this clears up the basics a bit.Now, some essential information I seem to have omitted the first time around is that the rules are contained in a "rules" table, new tickets are submitted to a "tickets" table via a sproc which in turn will look in the "rules" table to see what to do with that new record.I sincerely hope that I'm making more sense now. Problem is that I know exactly what I need. And as I sit here writing this stuff down my thoughts are running wild and my fingers have a hard time keeping up... ;)Edited by - gsnk on 04/05/2002 22:40:55Edited by - gsnk on 04/05/2002 23:50:27 |
|
|
|
Jay99
468 Posts |
Posted - 2002-04-06 : 09:32:52
|
| So what you need is a trigger on Tickets on INSERT,UPDATE that looks uses the conditionals in the above where clause in a series of IF...ELSE... flow control to determine which action to take . . . am I getting warmer?Jay<O> |
|
|
|
gsnk
Starting Member
24 Posts |
Posted - 2002-04-06 : 10:33:59
|
Jay, yes, you are getting very close!! :)1. I wasn't planning on using a trigger, just calling that sproc (spEmailNotification) from within the sprocs that handle the inserts/updates/deletes of the tickets. But I guess that doesn't really matter, right?2. Something I never really mentioned is why my solution (WHERE (ItemType IS NULL OR ItemType = @ItemType), etc.) didn't work: there will *always* be a default rule which contains all NULLs. Unfortunatelly this condional will always pull that default one regardless of whether any others match as well. That's not so bad yet since I can set the priority of that default rule to 1000000 and it will always be last in the result set. What's worse is that I cannot prioritize between a rule with 2 matches or one with 3 matches. In other words, if rules #2 & #3 from my last post where switched, it would *always* disregard the matching "ItemAuthor."Now, as I am sitting here typing this I am reminded of the Outlook Inbox Rules. Aren't they working on the same principle where the user has to prioritize them. If prioritization is not right some rules will be left untouched?! I may just have answered my own question here... Does that make sense at all?Thanks a bunch for enduring my rants here...  |
|
|
|
|
|
|
|
|
query where condition question