Can Case be used to assign filter with =, =
-
When writing a SQL query, I want to have a parameter (say, Return) which will have the following values (the parenthesis text is what the end user sees, not the values):
- A (Budget)
- B (Actual)
- C (Encumbrance)
- D (Actual & Encumbrance)
In my dataset, the Tranact_Type are:
- 1 for Actual
- 4 for Encumbrance
- 5 for Budget
There is nothing in 2 and 3 at the moment but I want the query to be scalable and not goof up if they added 2 and 3 to the source database (so not using a less than or equal approach here).
SELECT
gl_detail.j_transact_year
,gl_detail.j_transact_period
,gl_detail.j_tranact_type
,gl_detail.a_fund_seg1
,gl_detail.a_org
,gl_detail.a_object
,gl_detail.j_credit_amount
,gl_detail.j_debit_amount
FROM
gl_detail
WHERE
gl_detail.a_fund_seg1 = @Fund
AND (gl_detail.j_transact_year >= @FPstart AND gl_detail.j_transact_year <= @FPend)
AND gl_detail.a_object = @ObjectSo to finish this, would I have to do something like
AND CASE @Return
WHEN "A" THEN gl_detail.j_tranact_type = 5
WHEN "B" THEN gl_detail.j_tranact_type = 1
WHEN "C" THEN gl_detail.j_tranact_type = 4
WHEN "D" THEN (gl_detail.j_tranact_type = 1 AND gl_detail.j_tranact_type = 4)
ELSE NULL
______
Below are some more tools to help with providing context as well as a dummy excel with source data excerpts and destination table (pivot view) what my target report should look like in SSRS. Note the filters in this Pivot Table are what I want to use as parameters.
- This topic was modified 3 years, 6 months ago by fahad.kazi.
- This topic was modified 3 years, 6 months ago by fahad.kazi.
-
I think that is possible, but not QUITE the way you wrote it. What I think you would need to do (I don't have your data or any DDL to test this, just going off the top of my head) would be something like:
AND gl_detail.j_transact_type = (CASE @Return
WHEN 'A' THEN 5
WHEN 'B' THEN 1
WHEN 'C' THEN 4
)I left out the "ELSE NULL" because doing an equality comparison on NULL will ALWAYS return FALSE, so there is not much point in including that.
I left out D because what you wrote will give you 0 results. There is not going to be the case when ANY int value can be BOTH 1 AND 4. If that is supposed to be an OR and not an AND with 'D', then something like this SHOULD work:
AND (gl_detail.j_transact_type = (CASE @Return
WHEN 'A' THEN 5
WHEN 'B' THEN 1
WHEN 'C' THEN 4
) OR
(@REturn = 'D' AND gl_detail.j_transact_type IN (1,4))ALTERNATELY, to do this without the CASE statement:
AND ((gl_detail.j_transact_type = 5 AND @Return = 'A') OR
(gl_detail.j_transact_type = 1 AND @Return = 'B') OR
(gl_detail.j_transact_type = 4 AND @Return = 'C') OR
(gl_detail.j_transact_type IN (1,4) AND @Return = 'D'))My personal preference is to do it without the CASE statement, but that is just because I don't like CASE statements in a WHERE clause. I am not aware of any hard-rules or "best practices" that discourage it. I also find the logic easier to follow and read with the last solution I presented.
Just to re-iterate, I have NOT tested the above code so there may be syntax errors.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
@Mr. Brian Gale, thank you so much!!!
Your last submission worked like a charm and per your recommendation I adopted it. However, I am wondering what really happened there because to me, your case (or if) submission is intuitive. It is just saying Filter transaction type per @Return. If Return is A then filter to 5, If Return is B then filter to 4, so on and so forth.
What are we saying in your alternative approach?
Filter transaction type to 5 and Return to A or transaction type to 4 and Return to B? How is that interacting with parameters? Why isn't it breaking or duplicating because I didn't put WHERE @Return = @Return anywhere in my query. Also what would happen if I had another AND in there like:
(gl_detail.j_transact_type = 2 AND @Return = 'A')
Sorry if all this is inconvenient for you to explain to a newbie like me, but I would love to learn what SQL is thinking when it reads those commands because like you mentioned there is no way for IN function to work with CASE.
-
I would recommend looking into Truth Tables. But basically, what the approach is doing is if condition1 AND condition2 are both TRUE, then use the first option. If EITHER condition is false, then move onto the next one.
The "alternate" approach where I am not using any CASE statements, it is just doing the comparison. If your input value for @return is 'A', then the transaction type MUST be 5 or the clause would resolve to FALSE.
Now, an alternate way to write it would be:
AND (
(gl_detail.j_transact_type = 5 AND @Return = 'A') OR
(gl_detail.j_transact_type = 1 AND @Return = 'B') OR
(gl_detail.j_transact_type = 4 AND @Return = 'C') OR
(gl_detail.j_transact_type = 1 AND @Return = 'D') OR
(gl_detail.j_transact_type = 4 AND @Return = 'D')
)as you get the same result without needing to use IN. I just like combining the 2 rows when @Return is D. So your question about including transaction type of 2 or 5 when @Return is A could be done the way you had said, or you could use the IN approach like I did with D.
What I am saying in the alternate approach is basically the same thing you are saying - if Return is A then transaction type must be 5. If Return is B then transaction type must be 4. Basically everything inside the brackets must be true for the row to be included.
One of the biggest things to think about with SQL code vs most other languages is that you are writing a query to get a set of data. The set MAY be only 1 row, but in most cases you are dealing with a set of data (1000's of rows for example) and you want a subset of that data or you want to manipulate a subset of that data. SQL works GREAT for things like this. Other languages, if you were thinking of manipulating a set of data (an array for example), your code would be using a loop to go through the whole thing. In SQL, it is RARE that you need to use a loop and in most cases if you put a loop in, you are doing it inefficiently. Not always, but I'd say 99% of the time when I've seen a loop used in non-administrative code, it wasn't actually needed. Heck, I think 99% of the time I have put a loop in my non-administrative code, there was a way to do it without a loop. I am emphasizing "non-administrative" as loops CAN be required and useful in administrative code such as mapping all users to their respective logins after a database refresh or writing code that needs to be run on ALL databases or tables on an instance.
Now, to address the interacting with parameters question, it is interacting with the parameters. It is interacting with the "@Return" parameter. I included that in the examples I gave.
And you likely don't want to include "WHERE @Return = @Return" because there isn't much point. EVERY row would result to TRUE. It's like putting in "WHERE 1 = 1". There just isn't any point. The ONLY exception would be if @Return was NULL because if you have "WHERE NULL = NULL" you will get no results because NULL doesn't equal NULL.
As for using "IN" with a CASE, off the top of my head I am thinking it isn't possible, but it may be. I just personally find it harder to read the code and decode what it is doing.
But when working with a WHERE clause, truth tables are VERY big helpers. They seem like a lot of extra paperwork, but they work GREAT to help wrap your brain around how they query is working.
This website has a good writup on truth tables and how they work:
https://byjus.com/maths/truth-table/
I would start with a simple one where p is "@Result = 'A'" and q is "transaction type = 5". If p is true AND q is true the statement is true. If EITHER one is false, then the whole statement is false. If BOTH are false, the statement is false. Then you can expand it to be that p is "@Result = 'A' AND transaction type = 5" and q is "@Result = 'B' and transaction type = 4". Here, we have an OR statement so if either one is true, the whole statement is true. If both are true, the statement is true. If neither are true, the statement is false. As @Result CANNOT be both A and B, we immediately know that there is no case where both p and q can be true at the same time. So if p is true, q MUST be false.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Thank you so much, I believe I get it now. Below is a simplistic example of what's happening.
Let's say this is our code.
SELECT
gl_detail.fund
,gl_detail.transaction_type
,gl_detail.object
,gl_detail.amount
FROM
gl_detail
WHERE
gl_detail.fund = '110'
AND gl_detail.object = '501001'
AND ( (@return = 'A' AND gl_detail.transaction_type = '5')
OR (@return = 'B' AND gl_detail.transaction_type = '1')
OR (@return = 'C' AND gl_detail.transaction_type = '4')
OR (@return = 'D' AND gl_detail.transaction_type = '1')
OR (@return = 'D' AND gl_detail.transaction_type = '4') )Now, let's say below is my data-set, since Return is a parameter, it has no values until the user enters one. At this point, our query will populate no results as it Return is neither A, B, C or D which is one of the AND functions in WHERE. So it is essentially filtering into nothingness.
Now user enters A for @Return and the data-set transforms like below. The WHERE function in the QUERY hasn't changed, it is doing the same thing as before looking for rows where: Fund is 110, Object is 501001 and ( (Return is A and Transaction Type is 5) or (Return is B and Transaction Type is 4) or (Return is C and Transaction Type is 1) or (Return is A and (Transaction Type is 1 or 4 ) ) ). In this case, the only successful result it finds is the highlighted one because it has 110 for Fund, 501001 for Object, A for Return and 5 for Transaction Type.
Transaction Type 1 and 4 are ignored because we don't have Return B, C or D. Thereby, making the first clause of (@return = 'B' AND gl_detail.transaction_type = '1'), (@return = 'C' AND gl_detail.transaction_type = '4'), (@return = 'D' AND gl_detail.transaction_type = '1') or (@return = 'D' AND gl_detail.transaction_type = '4') false and, therefore, not populated.
Now user enters B for @Return and the data-set transforms like below. The WHERE function in the QUERY hasn't changed (scroll up to see what it is). In this case, the only successful results it finds are the highlighted ones because it has 110 for Fund, 501001 for Object, B for Return and 1 for Transaction Type.
Transaction Type 4 and 5 are ignored because we don't have Return A, C or D. Thereby, making the first clause of (@return = 'A' AND gl_detail.transaction_type = '5'), (@return = 'C' AND gl_detail.transaction_type = '4'), (@return = 'D' AND gl_detail.transaction_type = '1') or (@return = 'D' AND gl_detail.transaction_type = '4') false and, therefore, not populated.
Now user enters C for @Return and the data-set transforms like below. The WHERE function in the QUERY hasn't changed (scroll up to see what it is). In this case, the only rows it filters are the highlighted ones because it has 110 for Fund, 501001 for Object, C for Return and 4 for Transaction Type.
Transaction Type 1 and 5 are ignored because we don't have Return A, B or D. Thereby, making the first clause of (@return = 'A' AND gl_detail.transaction_type = '5'), (@return = 'B' AND gl_detail.transaction_type = '1'), (@return = 'D' AND gl_detail.transaction_type = '1') or (@return = 'D' AND gl_detail.transaction_type = '4') false and, therefore, not populated.
Now user enters D for @Return and the data-set transforms like below. The WHERE function in the QUERY hasn't changed (scroll up to see what it is). In this case, the rows it filters because it has 110 for Fund, 501001 for Object, D for Return and 4 for Transaction Type are highlighted grey and the rows it filters because it has 110 for Fund, 501001 for Object, D for Return and 1 for Transaction are highlighted green. Both green and grey will be populated because of the OR function.
Transaction Type 5 will be ignored only because we don't have Return A, B or C (but the way the function is written D will cover B and C). Thereby, making the first clause of (@return = 'A' AND gl_detail.transaction_type = '5'), (@return = 'B' AND gl_detail.transaction_type = '1') or (@return = 'C' AND gl_detail.transaction_type = '4') false and, therefore, not populated. Once again, return D will cover the rows for B and C in our case because the transaction type it looks for are the same B and C look for individually.
-
Kind of, but SQL is a little bit different than the images you sent (that look like Excel?).
SQL has all of the data in the database and is just holding onto it UNTIL someone asks for it. Until the SQL query is run, it is not doing anything. The query isn't really changing when you give a value to @return, in the same way that a google search doesn't change any code simply because you entered a thing to search for. Changing the value of @return just changes the result.
So if @Return is "A", then anything row doesn't match the WHERE clause EXACTLY would be excluded from the results. Thinking of it LIKE Excel, it is like adding a filter onto your columns. If you put a filter on Return to ONLY show you rows where the value is "A" and you put a filter on Transaction Type to only show you rows where the value is "5", all of the other rows would be hidden. They still exist, they are just not visible.
Now, if "Return" is not A, B, C, or D, then the WHERE clause has NO valid rows so you would get no results. The WHERE clause is used to reduce your data set based on the filters.
My general approach when I write queries is to start by determining what data I need (the columns), where it is coming from (the tables), how it is related (the table joins), and how I want to filter the data (the WHERE clause), and finally if it needs to be sorted (ORDER BY). And I tend to do it in that order most of the time. SOMETIMES, if it is a LARGE data set, I will put a where clause in to reduce the data set to a single row to make sure that I am getting EXCATLY what I am expecting from the columns or tables, or I will add a "TOP(1)" into it to make sure I have a small data set to work with initially and expand it as needed. But I like to make sure that I can get ALL data before I start filtering it down to exactly what I want. Sometimes I even export the data to Excel to make sure it is capturing everything I want how I want it.
SQL is a fun tool, but something to remember is that it isn't the ONLY tool either. What I mean is sometimes you need to think about what tool is consuming the data as well as the SQL query you are writing. For example, if you are exporting the data to Excel, you likely don't need to waste server resources sorting the data as the end user will sort it however they need to after they have the data. Or if the data is going to be used by a .NET application by an end user on a dialup connection, it MAY be better to let the application handle sorting and filtering the data. Slower application startup times (initial data retrieval will be slower as it is a large data set), but faster sorting and filtering as the user doesn't need to re-request data from the database each time they change something.
It is a fine balance between using too little of the SQL server and too much. It depends on the end users and the available resources too.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Thank you, Mr. Brian Gale. This helps puts things in perspective.
-
Brian gave you a solution using either a case expression - or checking each parameter and condition. Either will work - but may not work as well if there is an index available. Instead of doing this in the where clause - I would either build a table variable or limit the query to at most 2 available values.
Declare @transact_type_1 int = 0
, @transact_type_2 int = 0;
Set @transact_type_1 = Case When @return = 'A' Then 5
When @return = 'B' Then 1
When @return = 'C' Then 4
When @return = 'D' Then 1
Else 0
End;
Set @transact_type_2 = Case When @return = 'D' Then 4
Else 0
End;
Select ...
From ...
Where ...
And gl_detail.j_transact_type In (@transact_type_1, @transact_type_2);This sets @transact_type_1 to either 1, 4 or 5 depending on the parameter - and @transact_type_2 to 4 if @return is 'D'. This assumes that gl_detail.j_transact_type will never be 0 - but you could also use -1 or some other value that would never be utilized.
This could also be changed to:
Select ...
From ...
Where ...
And (
gl_detail.j_transact_type = @transact_type_1
Or gl_detail.j_transact_type = @transact_type_2
);Which is how SQL will rewrite the IN statement anyways.
We could also do this:
Declare @return char(1) = 'A';
Declare @transact_type_1 int = 0
, @transact_type_2 int = 0;
Set @transact_type_1 = choose(ascii(@return) - 64, 5, 1, 4, 1);
Set @transact_type_2 = choose(ascii(@return) - 64, 0, 0, 0, 4);
Select @transact_type_1, @transact_type_2;Although that isn't as clear as using a standard CASE expression. It could be used in the where clause:
Where ...
And (
gl_detail.transact_type = choose(ascii(@return) - 64, 5, 1, 4, 1)
Or gl_detail.transact_type = choose(ascii(@return) - 64, 0, 0, 0, 4)
);Or - you could replace CHOOSE in the WHERE clause with a standard case expression. The point here is to use a 'default' value that will never be found which then eliminates the extra checking and complexity.
This also shows that you can use an integer for your @return value - starting from 1 to 4 and 'map' those values to the corresponding transact_type. Do that changes the choose function to:
Where ...
And (
gl_detail.transact_type = choose(@return, 5, 1, 4, 1)
Or gl_detail.transact_type = choose(@return, 0, 0, 0, 4)
);With that - if you pass in a 4 for @return you would get transact_type = 1 or transact_type = 4.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thanks, Jeffrey. I think I am following what you are doing here (remember, an SQL noob here). What is the 64 in set functions? Is it to assign 64 bits to the variable?
-
The ascii function returns the ascii value of the character. Capital A is ascii 65, but choose works with int values starting at 1, so we subtract 64 to get to 1. B is ascii 66 minus 64 = 2, etc.
This assumes the values are upper case when passed, and if not you would need to force it to upper.
Much easier if you changed the parameter to use 1 thru 4 directly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I see, makes sense now. Thanks Jeffrey.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply