[Solved] Please help me with a tricky query

Creating tables and queries
Post Reply
erf9201
Posts: 20
Joined: Wed Jun 22, 2011 2:04 am

[Solved] Please help me with a tricky query

Post by erf9201 »

Hello,

I have a rather tricky problem.

I have a dataset such as:

Code: Select all

ID     Section  Description

1          1           Red
2          1           White
3          1           Orange
4          1           Blue
5          2           Tan
6          2           Yellow
...       ...           ...

I need to create a simple query (that i can hopefully use in a simple form) that will sort it by it by Section, then by ID and concatenate the Descriptions together. Like this:

Code: Select all

Section                       Description

    1                     Red White Orange Blue
    2                            Tan Yellow
I tried many different things, including CONCAT command, but I still cannot figure out how to make it join rows into a single row and how to sort it, all at the same time.

If anyone can help me, I would really appreciate it.

Thank you.
Last edited by erf9201 on Thu Jun 23, 2011 3:43 am, edited 1 time in total.
OpenOffice 3.3 on Windows 7 HP 64bit HSQLDB 2.2
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Please help me with a tricky query

Post by Sliderule »

What you are describing is called GROUP_CONCAT Aggregate Function.

Please see the post below . . . starting with the post(s) by Sliderule . . . and . . . the GRAPHICS.

http://www.oooforum.org/forum/viewtopic.phtml?t=104415

Per your description, the SQL Query code would be:

Code: Select all

-- SQL Query below will NOT work with HSQL Version 1.8, but WILL work with HSQL 2.0 and above
Select
   "Section",
   GROUP_CONCAT("Description" Order By "ID" Separator ' ' ) as "Description"

From "MyTable"    -- Change this to your real table name

Group By "Section"

Order By "Section"
Explanation : This feature is NOT available as is with the default OpenOffice Base HSQL Engine ( Embedded database version 1.8 ) :crazy: .

It is available, if you elect to upgrade your database engine to HSQL 2.2.4 ( that is the current version available for download as HSQLDB.JAR file as the day I am writing this. :super:

Please see the posts on this forum by DACM, he has available the technique and tools to migrate to HSQL 2.2.4 .

See DACM's post at: http://user.services.openoffice.org/en/ ... 83&t=17567

I hope this helps.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Last edited by Sliderule on Thu Jun 23, 2011 2:41 am, edited 1 time in total.
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Please help me with a tricky query

Post by Sliderule »

Just want to add another LINK . . . from DACM . . . with what he calls "Quick Fix" for Automatically create a new HSQLDB 2.x "multi-mode" database

http://www.oooforum.org/forum/viewtopic ... 523#396523

Sliderule
erf9201
Posts: 20
Joined: Wed Jun 22, 2011 2:04 am

Re: Please help me with a tricky query

Post by erf9201 »

Thanks, Sliderule. It works.

I did know about Group_Concat and this SQL upgrade, but I was hoping that I can avoid it and I was also hoping that I simply did not know how to do it right ( ;) ).

Ohh, well... Maybe normal front/back-end DB is better anyway. Although I was extremely surprised that built in SQL (HSQL 1.8) did not support this. Its a basic function pretty much. Makes me wonder what else it does not support ;)

Just a note: I used this link (from the one that you have provided) to migrate single file DB: http://www.oooforum.org/forum/viewtopic ... 995#386995
But I have to say that this migration is not exactly user friendly. Also, it does not mention SHUTDOWN COMPACT command to make exported data work faster.

Anyway, thanks again for the response and nice ready-to-use query (saved me some time).
OpenOffice 3.3 on Windows 7 HP 64bit HSQLDB 2.2
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved]Please help me with a tricky query

Post by Sliderule »

Great that things work as you needed.

You said . asked:
Its a basic function pretty much. Makes me wonder what else it does not support
The HSQL 2.2.4 documentation for SQL functions can be found at:

http://www.hsqldb.org/doc/2.0/guide/bui ... chapt.html

A few of the functions that are in HSQL 2.2.4 but NOT in 1.8 include
  1. Date arithmetic: DATEADD
  2. Paddding - LPAD, RPAD
  3. Regular Expression - REGEXP_MATCHES
  4. Change characters backwards - REVERSE
  5. Aggregate Functions - GROUP_CONCAT and MEDIAN
  6. Ability to write your own functions, for example, I wrote a PROPER function to start each word in a string with an UPPER case character and all other characters lower case, as well as writing a RANDBETWEEN function
Sliderule
erf9201
Posts: 20
Joined: Wed Jun 22, 2011 2:04 am

Re: [Solved]Please help me with a tricky query

Post by erf9201 »

Thanks, man. I was just braggin a bit ;)

I mean, of all 100s of functions, it did not have the one I needed. lol

But seriously, I do appreciate a lot the tremedous work that openoffice community does.
OpenOffice 3.3 on Windows 7 HP 64bit HSQLDB 2.2
Post Reply