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

[mrmister]

Hello

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

[mrmister]

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:

http://www.sigmod.org/publications/sigmod-record/0403/E.JimAndrew-standard.pdf

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:

https://mega.co.nz/#!REB1QS5Y!VH1vmqrVmeOa4dtA3Sl8luR00DggV0CEvlUl8qWn1L0

Here is the piece of code I’m writing:

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

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.

Cheers

[EasyTrieve]

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

[EasyTrieve]

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:

SELECT 
   COLLECT(FRIEND)       AS ALL_FRIENDS, 
   FUSION(HOBBIES)       AS ALL_HOBBIES, 
   INTERSECTION(HOBBIES) AS COMMON_HOBBIES
FROM FRIENDS

Returns:

ALL_FRIENDS                          ALL_HOBBIES                         COMMON_HOBBIES
MULTISET ['John', 'Susan', 'James']  MULTISET ['READING', 'READING',     MULTISET ['READING']
                                     'READING', 'POP-MUSIC', 'RUNNING',
                                     'OPERA', 'MOVIES', 'MOVIES']

[Ratslinger]

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