[Solved] WHILE ROLLUP in base parser, or alternative

[Nikos]

I have the following query, that works when send directly to MariaDB, but not when send via the parser.

Code: Select all

SELECT `ANK`.`company`, `ANK`.`journal`,`ANK`.`pb`, `ANK`.`db`, `DEUTERO`.`dbdesc`,
 SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) AS `T`,  
 SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ) AS `T_prev`, 
 SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) - SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ) AS `Diff`,
 ( SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) / NULLIF ( SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ), 0 ) - 1 ) AS `Percent` 
 FROM `ANK` LEFT JOIN `DEUTERO` ON `ANK`.`db` = `DEUTERO`.`db` WHERE `ANK`.`year` >= 2024 - 1 AND `company` = 'VD' AND `ANK`.`omada` = '6' AND `ANK`.`month` <= 12 AND `ANK`.`journal` = 'ΓΗ'
GROUP BY `ANK`.`pb`, `ANK`.`db` WITH ROLLUP

The problem is the usage of "WITH ROLLUP"

I would like the years, company and journal to be parameters ( =:par ), which means I would like to use the parser.
However, the ROLLUP function does not seem to be understood by the parser.
What are my alternatives?

[Jan_J]

The SQL parser in Base seems somewhat aggresive, especially when you use non-native SQL dialect.
You can establish server-side named view

Code: Select all

CREATE VIEW view_name AS query_text;

and refer to it in a query

Code: Select all

SELECT * FROM view_name;

[UnklDonald418]

A Filter table would be another option especially with multiple parameters
There is a lengthy discussion of the concept at
Filter/Search with Forms (leveraging SubForms)
but skip down near the bottom and download the example database named
LIKE_user_input_filter.odb
It has good descriptions on how it works.

[Sliderule]

Your original post is below:

Code: Select all

SELECT 
   `ANK`.`company`, `ANK`.`journal`,`ANK`.`pb`, `ANK`.`db`, `DEUTERO`.`dbdesc`,
    SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) AS `T`,  
    SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ) AS `T_prev`, 
    SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) - SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ) AS `Diff`,
    ( SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) / NULLIF ( SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ), 0 ) - 1 ) AS `Percent` 
FROM `ANK` LEFT JOIN `DEUTERO` ON `ANK`.`db` = `DEUTERO`.`db` 
    WHERE `ANK`.`year` >= 2024 - 1 
        AND `company` = 'VD' 
        AND `ANK`.`omada` = '6' 
        AND `ANK`.`month` <= 12 
        AND `ANK`.`journal` = 'ΓΗ'
GROUP BY `ANK`.`pb`, `ANK`.`db` WITH ROLLUP

Run the SQL Query below ( with LibreOffice Base Parser ), and, confirms it performs what you need. What I did was to add two lines, first line and last line, to hide the SELECT statement from LibreOffice Base parser.

Code: Select all

-- LINE Below Added By Sliderule for testing with LibrOffice Base Parser
SELECT `A`.* FROM (  
      SELECT 
         `ANK`.`company`, `ANK`.`journal`,`ANK`.`pb`, `ANK`.`db`, `DEUTERO`.`dbdesc`,
          SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) AS `T`,  
          SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ) AS `T_prev`, 
          SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) - SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ) AS `Diff`,
          ( SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) / NULLIF ( SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ), 0 ) - 1 ) AS `Percent` 
      FROM `ANK` LEFT JOIN `DEUTERO` ON `ANK`.`db` = `DEUTERO`.`db` 
      WHERE `ANK`.`year` >= 2024 - 1 
          AND `company` = 'VD' 
          AND `ANK`.`omada` = '6' 
          AND `ANK`.`month` <= 12 
          AND `ANK`.`journal` = 'ΓΗ'
      GROUP BY `ANK`.`pb`, `ANK`.`db` WITH ROLLUP
-- LINE Below Added By Sliderule for testing with LibreOffice Base Parser
) as `A`

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.