Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 18 total |
Nested Case Issues? |
Mon, Dec 14 2015 4:13 PM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | I have a fairly complex QUERY that has several nested CASE statements. I won't go into deep details of what each case does, but essentially it's a way for me to use this one query to dynamically generate a report with multiple result sets on it. Trying to keep my codebase DRY is making me batty.
Are there limitations or known bugs with nested Case statements? Attached is my code. Please note that this is just in testing phase. The production code will have the 1=0,1=2, etc parts of the CASE switched to use parameters or string interpolation if needed so I can pass in params to a script to select which version of this report I want output. When I have the 1st case true i get 16 rows, 2nd case true 102 rows, 3rd case true (which should be Both 1&2) i get 102 rows again. I'm confused why the OR clause isn't working within the 3rd case. Attachments: RoomRevenueTaxes.SQL |
Tue, Dec 15 2015 12:11 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Gregory,
I do not understand what you are trying to achieve - it is too complicated for me. How about you create views that are grouped on the required columns. Then you can use the UNION statement to combine the results of the views as required. I think it would simplify the problem and make it easier to understand what is happening. Richard |
Tue, Dec 15 2015 3:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Gregory
Its very difficult to read I'm also not dead certain of just what <<When I have the 1st case true i get 16 rows, 2nd case true 102 rows, 3rd case true (which should be Both 1&2) i get 102 rows again. I'm confused why the OR clause isn't working within the 3rd case.>> means in terms of the sql. My guess is its this bit WHEN 2=2 THEN (gd.Cancelled IS NULL AND gd.Deposit is NULL AND gr.Cost = 0) OR (CASE 1 WHEN 1 THEN --Both WithHeld and Non ((gd.Cancelled IS NULL) OR (gd.Cancelled >= DATE '2015-10-1' AND gd.Cancelled < DATE '2015-11-1' AND gd.Deposit IS NOT NULL)) WHEN 2 THEN --ONLY Withheld (gd.Cancelled < DATE '2015-10-1' AND gd.Deposit IS NOT NULL) WHEN 3 THEN --NO Withheld (gd.Cancelled IS NULL) END) This CASE 1 WHEN 1 seems wrong. I'm not sure what that first 1 is doing but I don't think it should be there. Roy Lambert |
Tue, Dec 15 2015 3:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Gregory
I've just done some playing select * from contacts where case (_surname like 'w%') when 1 = 4 then true end returns 9588 rows select * from contacts where case 1 when 1 = 4 then true end returns 10398 rows select * from contacts where _surname like 'w%' returns 810 Looking at the OLH CASE WHEN <BooleanExpression> THEN <Expression> [WHEN <BooleanExpression> THEN <Expression>] [ELSE] <Expression> END Short-hand syntax: CASE <Expression> WHEN <Expression> THEN <Expression> [WHEN <Expression> THEN <Expression>] [ELSE] <Expression> END The primary difference between the short-hand syntax and the normal syntax is the inclusion of the expression directly after the CASE keyword itself. It is used as the comparison value for every WHEN expression. All WHEN expressions must be type-compatible with this expression, unlike the normal syntax which requires boolean expressions. The rest of the short-hand syntax is the same as the normal syntax. I can read it, I have the results of my test above, but I don't really understand it Roy Lambert |
Tue, Dec 15 2015 1:43 PM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | Let me see if I can't throw together a script and some example tables/data to better illustrate what I'm trying to achieve.
Essentially I have a report that is for taxes. This report has a group of 3 options that act as radio butotns (only 1 selectable ) then 2 other options that are independent of one another. Here is an example of the options matrix: a a a a b b b b c c c c 1 ON ON OFF OFF ON ON OFF OFF ON ON OFF OFF 2 ON OFF ON OFF ON OFF ON OFF ON OFF ON OFF These options are essentially to include certain types of financial transactions, include certain types of tax groups, etc. I have a report generator that is powered by Delphi, but allows for XML scripting of report parameters. Within the XML I am able to do string interpolation within any SQL Query or SQL Script. I'm trying to write a stored procedure (or several) to power the many reports that utilize this same basic query structure. I am starting to lean toward dynamically generating the WHERE query based upon options selected and pumping it into a script via interpolation, but It'd still be nice for me to be able to have nested CASE statements as well as nested or multiple Boolean filters within the case options Example: SELECT A, B ,C FROM exampleTable et WHERE CASE {RadioButtonGroup} WHERE A THEN CASE {OptionGroup} WHERE 1 THEN et.A IS NULL WHERE 2 THEN et.B IS NOT NULL WHERE 3 THEN et.A is NULL AND et.B is NOT NULL END WHERE B THEN CASE {OptionGroup} WHERE 1 THEN et.B >0 WHERE 2 THEN et.B < 100 WHERE 3 THEN et.B >0 AND et.B < 100 END WHERE C THEN CASE {OptionGroup} WHERE 1 THEN et.A IS NULL AND et.B > 0 WHERE 2 THEN et.B IS NOT NULL AND et.B < 100 --This is just example, in actual code I'd use COALESCE(et.b,0) < 100 WHERE 3 THEN et.A is NULL AND et.B is NOT NULL AND et.B < 0 AND et.B > 100 END END The {RadioButtonGroup} and {OptionGroup} can be thought of as Variables or parameters that will be passed into the query before execution. That is why I was using the WHERE 1=1 or CASE 1 WHERE 2 in these examples. It was a way for me to hard code the different cases for testing. This basic of a use case above may work fine.... but I was seeming to have issues when adding OR groups and AND groups within the multiple CASE Statements. Hopefully this clarifies what I'm trying to achieve and explains the code a bit more. Due to the complexity of this query though I am starting to think it maybe better to bit the bullet and run multiple selects and dump into a temporary table then return that table and do any additional filtering, summations, counts, etc as needed. Thanks for taking a look at this, and other ideas/suggestions are welcomed and appreciated. |
Tue, Dec 15 2015 6:26 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I've just done some playing >> There issue is the type mismatch - the inline version of the CASE statement isn't properly type-checking the arguments and is just assuming that they're correct. A fix will be in 2.21. Tim Young Elevate Software www.elevatesoft.com |
Tue, Dec 15 2015 6:29 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
Nope, scratch that - the issue is the mixing of the Integers and Booleans. The type-checking is working okay, but the evaluation isn't doing the type conversion properly when comparing the Integers to the Booleans. Tim Young Elevate Software www.elevatesoft.com |
Wed, Dec 16 2015 12:04 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Hi Gregory,
<<This basic of a use case above may work fine.... but I was seeming to have issues when adding OR groups and AND groups within the multiple CASE Statements. Hopefully this clarifies what I'm trying to achieve and explains the code a bit more. Due to the complexity of this query though I am starting to think it maybe better to bit the bullet and run multiple selects and dump into a temporary table then return that table and do any additional filtering, summations, counts, etc as needed.>> I think you will find that creating temporary tables (or VIEWS) will make life much easier. It will separate out the data from the reporting functions. Imagine when you (or someone else) starts to add or remove a number of radio buttons or check boxes in a few years time. VIEWS are really easy to manage and EDB v2.21 allows indexing of views. EDB allows EXCEPT and INTERSECT as well as UNION which can be useful to manipulate results sets. Richard Harding |
Wed, Dec 16 2015 4:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
Can you come up with a better explanation of the functionality for the OLH please, or give an example, I'm baffled as to just what its meant to do. Roy Lambert |
Wed, Dec 16 2015 4:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Gregory
There seems to be a bit of a problem with the short-hand case statement which Tim's looking at. I know you've had Views recommended as a solution but I dislike them for their lack of flexibility. They're brilliant for some things but you can't parameterise them. My preferred approach with anything as complex and variable as your requirement is to build the sql in code. I find that it make debugging and modification MUCH easier. If I'm going to be running the query in a loop (or several times) with parameters I'll build the parameters in via code and prepare the query before running. You can end up with a faster cleaner solution since the SQL isn't as complex which is an added bonus. Roy Lambert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |