![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 18 of 18 total |
![]() |
Wed, Dec 16 2015 10:37 AM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | 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. |
Wed, Dec 16 2015 11:19 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Wed, Dec 16 2015 11:27 AM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | 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.
|
Wed, Dec 16 2015 11:51 AM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | 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. |
Wed, Dec 16 2015 1:23 PM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | Working Example here, just in case someone wanted to get a feel for nested CASE with multiple filters.
Attachments: NestedCaseEx.SQL |
Wed, Dec 16 2015 1:27 PM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | 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. Attachments: NestedCaseEx.SQL |
Wed, Dec 16 2015 5:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Thu, Dec 17 2015 3:29 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |