Base: How can I fix this case when to be parsed by LO and not HSQL Directly?

[Dycius]

Hi All,

I have been trying get s table to display two records in the same result row using LO 4.1 and the embedded HSQLDB version. Sliderule provided a great example, but I want to be able to parse the sql with LO and not HSQL because I need to add variables to code. I thought I would try this as a learning experience. The last bit of code is this:

SELECT TOP 1 CASE 
      WHEN MOD("F3"."TOTAL", 2) = 1
         THEN "F3"."ID"
      ELSE NULL
      END AS "COL1"
   ,CASE 
      WHEN MOD("F3"."TOTAL", 2) = 1
         THEN "F3"."FruitName"
      ELSE NULL
      END AS "COL2"
   ,NULL AS "COL3"
   ,NULL AS "COL4"
FROM "FRUITS_ROWNUM_VIEW" AS "F3"
ORDER BY "F3"."TOTAL" DESC

It’s part of a larger view, but I traced the issue to the above section. The other parts all work fine by themselves as I broke it up and tested smaller parts. This is the full code of the view:

// FRUITS_2COL_VIEW
SELECT 
	"A"."ID1"
   ,"A"."FruitName1"
   ,"A"."ID2"
   ,"A"."FruitName2"
FROM (
   SELECT 
	   "F1"."ID" AS "ID1"
      ,"F1"."FruitName" AS "FruitName1"
      ,"F2"."ID" AS "ID2"
      ,"F2"."FruitName" AS "FruitName2"
   FROM "FRUITS_ROWNUM_VIEW" AS F2
   CROSS JOIN "FRUITS_ROWNUM_VIEW" AS F1
   WHERE ("F1"."TOTAL" + 1) = "F2"."TOTAL"
      AND MOD("F1"."TOTAL", 2) = 1
   
   UNION ALL
   
   (
      SELECT TOP 1 CASE 
            WHEN MOD("F3"."TOTAL", 2) = 1
               THEN "F3"."ID"
            ELSE NULL
            END
         ,CASE 
            WHEN MOD("F3"."TOTAL", 2) = 1
               THEN "F3"."FruitName"
            ELSE NULL
            END
         ,NULL AS "COL3"
         ,NULL AS "COL4"
      FROM "FRUITS_ROWNUM_VIEW" AS "F3"
      ORDER BY "F3"."TOTAL" DESC
      )
   ) AS "A"
WHERE "A"."ID1" IS NOT NULL
   AND "A"."FruitName1" IS NOT NULL

As you can see, I added in AS “COL1” and AS “COL2”, trying to follow another format in a different post about this:

Can I use a CASE WHEN statement in LO Base?

I am trying to get my database to show a zero when its NULL (I know in formulas you can use the IF NULL(“ColumnName”, ‘0’ but this does not work in a stand alone select statement without a formula). I was told I should try a CASE WHEN but I could not get it to work. I tried CASE (“ColumnName”) IS NULL THEN ‘0’ ELSE “ColumnName” And I got SQL error so I am obviously missing something - or can I not use CASE WHEN in LO Base? I am running LO Base 4.0.0.3 on Ubuntu 13.4

They mention being able to do this in LO without needed HSQL to parse it directly.

The error that I get with the trouble section is the following:

SQL Status: HY000 Error code: 1000

syntax error, unexpected $end,
expecting BETWEEN or IN or
SQL_TOKEN_LIKE

I hope someone can help me get this section working with LO’s parser so I can add in variables.

Thanks,
Jon

[Dycius]

After more studying, it appears that Base only supports TOP in SQL mode rather than processing it directly. Is there a way to return only one record in a Base friendly way?

[Dycius]

I did it, but it’s really a bit of a hack, in my opinion. If anyone has a better way of doing it, please let me know as I know have a lot of sub-select statements going on, so the runtime of this on a large database must be huge. The solution is to replace TOP with MAX as follows, in case anyone needs the answer to this:

SELECT CASE 
      WHEN MOD("F3"."TOTAL", 2) = 1
         THEN "F3"."ID"
      ELSE NULL
      END AS "COL1"
   ,CASE 
      WHEN MOD("F3"."TOTAL", 2) = 1
         THEN "F3"."FruitName"
      ELSE NULL
      END AS "COL2"
   ,NULL AS "COL3"
   ,NULL AS "COL4"
FROM "FRUITS_ROWNUM_VIEW" AS "F3"
Where "TOTAL" = (SELECT MAX("TOTAL") FROM "FRUITS_ROWNUM_VIEW")
ORDER BY "F3"."TOTAL" DESC

Cheers.

[Sliderule]

SELECT 
     CASE 
     WHEN MOD("F3"."TOTAL", 2) = 1
        THEN "F3"."ID"
     ELSE NULL
     END AS "COL1"
  ,  CASE 
     WHEN MOD("F3"."TOTAL", 2) = 1
        THEN "F3"."FruitName"
     ELSE NULL
     END AS "COL2"
  ,  NULL  AS "COL3"
  ,  NULL  AS "COL4"
FROM "FRUITS_ROWNUM_VIEW" AS "F3"
ORDER BY "F3"."TOTAL" DESC 
LIMIT 1

By using LIMIT 1 at the bottom, this Query will work for you, whether using the LibreOffice Base Parser, OR, if the Query is passed to the database engine ( HSQL 1.8.1.10 ) directly.

Sliderule

[Sliderule]

SELECT 
     CASE 
     WHEN MOD("F3"."TOTAL", 2) = 1
        THEN "F3"."ID"
     ELSE NULL
     END AS "COL1"
  ,  CASE 
     WHEN MOD("F3"."TOTAL", 2) = 1
        THEN "F3"."FruitName"
     ELSE NULL
     END AS "COL2"
  ,  NULL  AS "COL3"
  ,  NULL  AS "COL4"
FROM "FRUITS_ROWNUM_VIEW" AS "F3"
ORDER BY "F3"."TOTAL" DESC 
LIMIT 1

By using LIMIT 1 at the bottom, this Query will work for you, whether using the LibreOffice Base Parser, OR, if the Query is passed to the database engine ( HSQL 1.8.1.10 ) directly.

Sliderule