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

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

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?

( 2014-11-07 11:12:05 +0200 )edit

Sort by » oldest newest most voted
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

more

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.

more