Ask Your Question

Collect, Fusion, Intersection functions (asking for example database and queries)

asked 2015-04-09 00:31:05 +0200

mrmister gravatar image


I've been searching information about the Collect, Fusion, Intersection functions and sadly, I did not find any easy to understand and clear example about how these functions works. In fact, I know how to use all the Base functions, except these functions.

I'd like to understand the practical use of these functions, and the best way is dealing with a database and some examples to see the results.

Please, could someone give me some practical examples of these functions with a sample database? Cheers

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-12-13 08:35:27 +0200

EasyTrieve gravatar image

Interesting question.

1) It seems that COLLECT, FUSION, and INTERSECTION are functions to be used with MULTISET data types. But LO does not appear to support MULTISET data types in HSQL. I'm guessing that MULTISET data types are supported in other type databases that can be connected to, so that's why they appear in the list of functions.

2) I think the example in the reference you site is quite interesting, and illustrates the use of COLLECT, FUSION, and INTERSECTION:

Given the this table FRIENDS:

FRIEND              HOBBIES
----------------    -------------------------
'John'              MULTISET['READING', 'POP-MUSIC', 'RUNNING']
'Susan'             MULTISET['MOVIES', 'OPERA', 'READING']
'James'             MULTISET['MOVIES', 'READING']

This query:



ALL_FRIENDS                          ALL_HOBBIES                         COMMON_HOBBIES
                                     'READING', 'POP-MUSIC', 'RUNNING',
                                     'OPERA', 'MOVIES', 'MOVIES']
edit flag offensive delete link more


@EasyTrieve Here is a problem some people create - they post on multiple forums and if/when they get an answer, they don't reference the answer on the other forums. This question was answered on this post.

Ratslinger gravatar imageRatslinger ( 2016-12-14 20:09:39 +0200 )edit

answered 2015-04-14 12:45:05 +0200

mrmister gravatar image

updated 2015-04-14 12:49:43 +0200

Seems to be no one is skilled in SQL for these functions? Well I'm adding to this message what I've discovered, so hopefully someone could read this and help the community

I've found a document in which is described the use of COLLECT, FUSION, INTERSECTION functions in SQL. Exactly, these functions comes from SQL 2003 The document is available here:

I quote verbatim: "Additionally, the union, intersection, and difference of two multiset values are supported, as well as three new aggregate functions to create a multiset from the value of the argument in each row of a group (COLLECT), to create the multiset union of a multiset value in all rows of a group (FUSION), and to create the multiset intersection of a multiset value in all rows of a group (INTERSECTION)."

Okay, I'm trying to recreate the same piece of code of that document, but I am getting an error.

Here is my database:!REB1QS5Y!VH1vmqr...

Here is the piece of code I'm writing:

       COLLECT( "Nombre" ) "AllNames", 
       FUSION( "Hobby" ) "AllHobbies", 
       INTERSECTION( "Hobby" ) "CommonHobbies" 

The error I am getting is: SQL Status: S1000 Error code: -33

Access is denied: COLLECT in statement [SELECT COLLECT( "Nombre" ) "AllNames", FUSION( "Hobby" ) "AllHobbies", INTERSECTION( "Hobby" ) "CommonHobbies" FROM "tbl_HOBBIES"]

If someone with SQL skills could tell me what am I doing wrong? I'd really appreciate that.


edit flag offensive delete link more


Your database is no longer available. (Broken link).

EasyTrieve gravatar imageEasyTrieve ( 2016-12-13 09:32:52 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-04-09 00:31:05 +0200

Seen: 827 times

Last updated: Dec 13 '16