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.

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

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

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

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.

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

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

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

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

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