How to get the results of WHERE conditional evaluations in the result set?

***The PROBLEM*** ----------------- I have a query like this: select a.id from a join b on ( a.id = b.my_a ) join .... where ( /* complex and expensive conditional */ ) AND (( /* conditional #1 */ ) OR ( /* conditional #2 */ ) OR ( /* conditional #3 */)) I would like to have the query return something like: select a.id, conditional_1_eval_value, conditional_2_eval_value, conditional_3_eval_value from a join b on ( a.id = b.my_a ) join .... where ( /* complex and expensive conditional */ ) AND (( /* conditional #1 */ ) OR ( /* conditional #2 */ ) OR ( /* conditional #3 */)) where `conditional_1_eval_value`, `conditional_2_eval_value`, and `conditional_3_eval_value` are set to TRUE, FALSE, NULL. NULL indicating that the conditional was not evaluated. So the results set might be: 1, FALSE, NULL, TRUE ( condition_1, condition_3 were evaluate, condition_2 was not) 2, NULL, TRUE, TRUE ( condition_2, condition_3 were evaluate, condition_1 was not) 3, TRUE, FALSE, FALSE (all were evaluated) `condition_1`, `condition_2`, `condition_3` are complex themselves involving correlated subqueries and grouping. EDIT: What am I trying to accomplish? ============================== We need to log which conditional caused the row to be returned. We don't need to know all the reasons why the row was returned. So in the second row of the results example, it is enough to know that `conditional_2` and `conditional_3` were both true. Not knowing the what `conditional_1` value is does not matter. It is enough to know that at least one conditional was satisfied and what that one conditional was. *Nonoptimal solutions* ====================== Obviously I could do this with a **UNION** like this: select a.id, TRUE, NULL, NULL from a join b on ( a.id = b.my_a ) join .... where ( /* complex and expensive conditional */ ) AND ( /* conditional #1 */ ) UNION select a.id, NULL, TRUE, NULL from a join b on ( a.id = b.my_a ) join .... where ( /* complex and expensive conditional */ ) AND ( /* conditional #2 */ ) UNION select a.id, NULL, NULL, TRUE from a join b on ( a.id = b.my_a ) join .... where ( /* complex and expensive conditional */ ) AND ( /* conditional #3 */) But this would mean that: 1. the common "complex and expensive conditional" is evaluated 3 times. 2. that all conditionals are evaluated even when another conditional has already satisfied the OR. 3. there would be a maintenance nightmare insuring that the 3 copies of the common complex query are identical ( o.k. solvable by constructing sql in code and copying the common string -- but that means I would violate another internal standard of all sql not being embedded in java but being in a visible-to-DBAs xml file) Using a **CASE in the select** that duplicates each conditional 1 through 3 avoids the common condition being evaluated 3 times. However, the complexity of conditional 1-3 is such that it may not be possible. Using a **select in the FROM** clause, would be awkward and may not be possible because a FROM SELECT cannot be a correlated query. I am not certain that I can construct a useful noncorrelated query. **Stored procedures** would work. However, this would be out first such stored procedure and would increase our deployment complexity significantly. **Doing the `conditional_1`, `conditional_2`, `conditional_3` evaluation in java code.** This is what we are currently doing and it runs sloooooooow. Lots of data transferred when the database is designed to filter the results set -- should not be doing this in java! Solution suggestions? ===================== Anyone? I should also add that I welcome answers that say this problem cannot be solved. Knowing that the problem cannot be solved would save me time trying to solve it with strictly SQL. If I had to chose, I would lean toward learning what the mysql stored procedure would look like. So if you want to volunteer what the mysql stored procedure would look like that would be great.

以上就是How to get the results of WHERE conditional evaluations in the result set?的详细内容,更多请关注web前端其它相关文章!

赞(0) 打赏
未经允许不得转载:web前端首页 » JavaScript 答疑

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

前端开发相关广告投放 更专业 更精准

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏