-3

I need to Capture WHERE Clause Conditional Result and append in SELECT Statement using SQL Server

Sample SQL:

SELECT c.*
    , Result_Of_Condition_#1
    , Result_Of_Condition_#2 
FROM Customer c 
WHERE (Condition #1) OR (Condition #2)

Note: Kindly avoid replicating the SAME Condition once again in the SELECT Query. I need a smart approach.

Don't share the following approach

SELECT c.*
    , CASE WHEN (Condition #1) THEN 1 ELSE 0 END
    , CASE WHEN (Condition #2) THEN 1 ELSE 0 END
FROM Customer c 
WHERE (Condition #1) OR (Condition #2)

Kindly assist me.

B.Balamanigandan
  • 4,713
  • 11
  • 68
  • 130
  • 1
    Please review [How to post a tsql question on a public forum](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) and [How-to-Ask](http://stackoverflow.com/help/how-to-ask) and edit your question accordingly. – SqlZim Feb 21 '17 at 12:57
  • Why *not* use the solution you already posted? Why do you think this *isn't* a smart approach? *Only* the matching results are returned and the `WHERE` clause will (probably) take advantage of any indexes. What do you mean "smart"? – Panagiotis Kanavos Feb 21 '17 at 15:30
  • @PanagiotisKanavos - The Condition #1 and #2 are very Complex, if I proceed with my approach, I have to place the same Complex logical condition and it executes once again. – B.Balamanigandan Feb 21 '17 at 15:35
  • 1
    Then simplify them. Or use a view, or CTE to expose parts of them. It's impossible to answer since you don't give *any* details – Panagiotis Kanavos Feb 21 '17 at 15:35
  • 2
    There's no straightforward way to achieve this since there's no user-visible boolean type in SQL Server - but where clause predicates evaluate conditions and produce boolean results. You can't have something that is *directly* a) usable in a `WHERE` clause and b) produces a value that can appear in a result set. – Damien_The_Unbeliever Feb 21 '17 at 15:42

2 Answers2

1

How about this? 

SELECT  *
FROM
( SELECT    *,
            Condition = CASE WHEN [Condition #1]  THEN 1
                             WHEN [Condition #2]  THEN 2
                          ELSE 0
                        END
  FROM  customer
) AS C
WHERE   C.Condition <> 0;

I assume you're trying not to duplicate code. However, I think your original version will likely perform better as this version will not use any indexes for the where clause.

Wes H
  • 4,186
  • 2
  • 13
  • 24
  • Your query perform very very slow, because the Inner Query is looping with out any filter (i.e., without any WHERE clause). Consider the `Customer` table has 1500K records then it `SELECT`'s all the records. – B.Balamanigandan Feb 21 '17 at 15:32
  • 1
    @B.Balamanigandan given how vague the question is, Wes gave an answer that is as good as can be expected. You already posted the solution and said you don't want it. You should rephrase your question and explain *what* it is that you actually want. What is the problem with `CASE`? What do you mean by `smart`? What is the *actual* problem that you want to solve? – Panagiotis Kanavos Feb 21 '17 at 15:34
  • @PanagiotisKanavos - We can't expect all the problems should be easy, some problem may be VAGUE. But a problem is a problem. We can't able to skip if the problem is looking VAGUE. Anyway we need to find a solution even-though if a Problem is VAGUE. If no solution found, I raise a bounty to get the answer. – B.Balamanigandan Feb 21 '17 at 15:38
  • Just as an FYI, there are no loops in this version. However, SQL Server has to calculate the condition before it can do any filtering. That is what makes it run slower. The query that you didn't want is how I would normally write this. The condition is duplicated, but the performance is best. – Wes H Feb 21 '17 at 15:38
  • @B.Balamanigandan OK, I'll rephrase. Vague to the point of closing the question as unclear. – Panagiotis Kanavos Feb 21 '17 at 15:38
  • @B.Balamanigandan The *problem* seems to be that you want SQL to guess that what is actually a **single** logical expression in `WHERE` should be treated as *two* expressions. Second problem, you want SQL to expose the results of those expressions as fields, without actually defining them as fields. – Panagiotis Kanavos Feb 21 '17 at 15:40
  • @B.Balamanigandan now, *maybe* you can define individual expressions as functions, provided they don't result in a slow execution plan. *Maybe* you can use a CTE. Maybe not. It's impossible to answer without any information, ie - a table schema, query, and description of the conditions – Panagiotis Kanavos Feb 21 '17 at 15:41
  • @PanagiotisKanavos - what are all the possibilities you know, just post it in the answer block – B.Balamanigandan Feb 21 '17 at 15:42
  • @B.Balamanigandan none. Given no information, the answer is none – Panagiotis Kanavos Feb 21 '17 at 15:43
  • @PanagiotisKanavos - No issue. Some intelligent peoples are there, they can understood the issue, not by the common implementer. Anyway thanks for your comments. – B.Balamanigandan Feb 21 '17 at 15:45
  • 1
    @B.Balamanigandan - " the Inner Query is looping with out any filter (i.e., without any WHERE clause)." - there's no way to know what would actually happen without having a real query and real data. The point of SQL is you write *what you want*, not *how to do it*. The system is free to rearrange *when* and *where* it evaluates particular predicates to try to achieve the most efficient query plan. We have no idea whether it *would* produce a complete result set from the inner query before attempting to filter by the outer predicate. – Damien_The_Unbeliever Feb 21 '17 at 15:45
  • @B.Balamanigandan you have to understand that `WHERE` takes one expression. Just *one*. Whether that contains boolean operators like AND, OR, comparisons or whatever, it's only *one* expression. The value of that expression is always TRUE for the returned results. If you want *some* parts of that expression to be exposed as fields, you have to specify them. – Panagiotis Kanavos Feb 21 '17 at 15:57
  • 1
    Furthermore, the specifics *matter*. For example, you could use a rule table to convert complex rules and conditions to simple checks against that table. In this case, the matching rule line shows you all you need to know. Can this be done with your expressions? Maybe, maybe not. – Panagiotis Kanavos Feb 21 '17 at 16:02
1

Here is another approach. This will perform better than the other answer I posted, but has its own code maintenance trade offs.

SELECT    *, Condition = 1
      FROM  customer
      WHERE (Condition #1)
UNION ALL
SELECT    *, Condition = 2
      FROM  customer
      WHERE (Condition #2)

It will still be slower than the version you don't want because it is querying and filtering the table twice.

Wes H
  • 4,186
  • 2
  • 13
  • 24