Thanks for all the help and suggestions everyone.

Views are not desirable in my situation because it would require DB Schema changes to many customers, which we try to avoid at all costs. By doing a straight SQL we may make the report/query a bit more complex, but we at the same time reduce a much bigger and time consuming task of manually updating multiple instances of the DB.

If this was a single instance database it would be cake to write a view and do some basic filters off of that.

I'm going to try standardizing my WHERE methods. If that still doesn't work then I'll end up going the 'easy' route and repeating myself a few times to reduce complexity.

I'll update this thread as progress is made. Again thank you for the feedback and assistance. Elevate may not be the creme de le creme of Databases but the support we get here is.

Gregory


If you want to post a bit of database to the binaries I'll happily have a play with it and see if I can kick it into co-operating as well

Roy Lambert

I'll give a shot first here. Not sure if it'll be too much work now that I understand the CASE statements a bit more. If I need I'll take you up on that though.

WOW! Okay well the CASE issue was a bit of a setback, but I found my real issue.

It turns out I was doing a group by on a TIMESTAMP as it should be unique. This would allow me to have a single query to do totals and line-item reporting. It works great, i pass in a parameter FullReport and if TRUE group by unique timestamp otherwise group by CURRENT_DATE() (or 1, or any other static value that won't change).

My assumption that TIMESTAMP was unique was wrong. This was causing odd grouping in my report that made it seem like the logic of the WHERE clause was broken.

Thanks for the help in working this all out everyone. Moral of the story... check your table counts before trying to apply grouping and filtering.

Working Example here, just in case someone wanted to get a feel for nested CASE with multiple filters.

Here is an updated example that shows how I am handling both summary and line item queries with the flip of 1 parameter in group by.

The big issue i had was i wasn't grouping by a unique column, only an assumed unique column.

Roy,

<< 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. >>

The engine isn't properly handling comparing a Boolean to an integer at the value-conversion level, specifically in the direction of Integer = Boolean.  Boolean = Integer is more common, and works just fine.

Tim Young
Elevate Software
www.elevatesoft.com

Gregory

>WOW! Okay well the CASE issue was a bit of a setback, but I found my real issue.
>
>It turns out I was doing a group by on a TIMESTAMP as it should be unique. This would allow me to have a single query to do totals and line-item reporting. It works great, i pass in a parameter FullReport and if TRUE group by unique timestamp otherwise group by CURRENT_DATE() (or 1, or any other static value that won't change).
>
>My assumption that TIMESTAMP was unique was wrong. This was causing odd grouping in my report that made it seem like the logic of the WHERE clause was broken.
>
>Thanks for the help in working this all out everyone. Moral of the story... check your table counts before trying to apply grouping and filtering.
>

Unless you have carefully controlled the data for a TIMESTAMP I would advise against grouping on it - the millisecond portion may be different, and I have no idea on how Tim stores or compares for sorting but in Delphi these are floating point numbers - you have been warned.

Roy

ps I'll have a look at your example later