Ask Your Question
0

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

asked 2014-11-06 18:52:22 +0200

Dycius gravatar image

updated 2016-03-05 22:04:56 +0200

Alex Kemp gravatar image

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: http://ask.libreoffice.org/en/questio... 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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 22:05:13.066176

Comments

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 gravatar imageDycius ( 2014-11-07 11:12:05 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2014-11-07 20:43:38 +0200

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

edit flag offensive delete link more
0

answered 2014-11-07 11:19:28 +0200

Dycius gravatar image

updated 2014-11-07 11:19:56 +0200

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-11-06 18:52:22 +0200

Seen: 511 times

Last updated: Nov 07 '14