![]() ![]() If we include the Where clause with the SQL Where Case in it, then we get 290 rows returned: If we do a quick Select of everything, we get 504 rows returned: In this particular case, when the price is less than a 100 it will return the row and when it’s greater than a 100 it will not return anything. What we’re actually doing here is we’re saying when 1 equals 1, return the row and when 1 equals 0, do not return the row. The Where clause says 1 equals case when the price is less than a 100, output one, else 0 end. You’re probably thinking we could just say Select everything from “Product”, where “ListPrice” is less than 100. Run a quick Select statement to retrieve everything from the table:ġ = CASE WHEN ListPrice < 100 THEN 1 ELSE 0 END Let’s say we want to order the results of our products. While this can be a little outside of the realm or outside the comfort level of the basic query class, it’s good to get familiar with those as well because you’re bound to come across at some day and it’s good to know why they’re there and see the power of Case statement in SQL. But a lot of times and in some more advanced and complex stuff like stored procedures, you’ll see SQL Case statement in places like Order by and/or SQL Where Case clause. Now, let’s see a couple of quick examples when a SQL Case statement can be also used because a lot of times and the most common place you’re going to see a Case statement in SQL is in a Select list to do things like we did above to modify and work with the output. The above query returns the “Price Range” as a text comment based on the price range for a product: ![]() Now, instead of doing SQL case statement followed by a column name we just do Case, When, and then column name with the output range and what we want to print out. The following query is based on the price range for a product: There’s also a searched expression version of it that allow us to work on a range. That’s one use of the SQL Case statement (equality version of it). This means that you’d have seen the “Unknown” output as well if the character was anything else than stated in the When clause. It’s not seen in this example because all the fields match arguments but the Case statement in SQL supports an optional Else argument. So, if we now execute our query with this SQL Case statement it will fetch that information and output it appropriately: Another way to do aliases is to put the name of the alias first followed by equals SQL Case on “MaritalStatus” as in the example above. Furthermore, we’re aliasing the name of the column as “GenderDescription”. Also, if it’s neither of those it’s going to output “Unknown”. In this particular case, whenever “Gender” equals “F” then it’s going to output “Female” and when it equals “M” then it’s going to output “Male”. What it basically means is that we can do Case followed by the field name and then we can have all case expressions. ![]() This is a great equality expression case statement in SQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |