Report a single to large format error

Question

An attempt to correct the format of Informix query results from a one-to-many relationship. My current query uses JOIN, but creates a new line for every match with the JOIN ON clause. I have to add below, this is just an example, the real data is thousands of records with about 100 unique "categories", so I can’t approve hard WHERE codes, they need to read each record and add if there is a match. I tried GROUP_CONCAT, but just returned an error, I guess this is not an informix function, I also tried to read this topic, but so far I could not work. Show relationships from one to many as 2 columns - 1 unique row (identifier and comma separated list)

Any help would be appreciated.

• IBM / Informix-Connect Version 3.70.UC4
• LIBRARY IBM / Informix LIBGLS Version 5.00.UC5
• IBM Informix Dynamic Server Version 11.70.FC8W1

Table

film

name rating movie_id rio g 1 horton g 2 blade r 3 lotr_1 pg13 4 lotr_2 pg13 5 paul_blart pg 6 

category

 cat_name id kids 1 comedy 2 action 3 fantasy 4 

category_member

 movie_name cat_name catmem_id lotr_1 action 1 lotr_1 fantasy 2 rio kids 3 rio comedy 4 

When i use

 #!/bin/bash echo "SET isolation dirty read; UNLOAD to /export/home/movie/movieDetail.unl DELIMITER ',' SELECT a.name, a.rating, b.cat_name FROM movie a LEFT JOIN category b ON b.movie_name = a.name ;" | dbaccess thedb; 

I get

 rio,g,kids rio,g,comedy lotr_1,pg13,action lotr_1,pg13,fantasy 

I would like

 rio,g,kids,comedy lotr_1,pg13,action,fantasy 

Answer 1

Set GROUP_CONCAT custom aggregate

You must install the user-defined population from SO 715350 in your GROUP_CONCAT database (link to your question). The GROUP_CONCAT component is not defined by Informix, but can be added if you use SQL from this question. One difference between this and the usual built-in function is that you need to install the aggregate in each database on the server where you need to use it. There may be a way to do a “global installation” (for all databases on this server), but I forgot (or rather never found out) how to do it.

Writing Your Inquiries

With the sample database indicated below:

• The query in the question does not start:

   SELECT a.name, a.rating, b.cat_name FROM movie a LEFT JOIN category b ON b.movie_name = a.name; SQL -217: Column (movie_name) not found in any table in the query (or SLV is undefined). 

• This can be fixed by changing category to category_member . This gives:

   SELECT a.name, a.rating, b.cat_name FROM movie a LEFT JOIN category_member b ON b.movie_name = a.name; rio g kids rio g comedy horton g blade r lotr_1 pg13 action lotr_1 pg13 fantasy lotr_2 pg13 paul_blart pg 

• LEFT JOIN seems undesirable. And using GROUP_CONCAT gives about the desired answer:

   SELECT a.name, a.rating, GROUP_CONCAT(b.cat_name) FROM movie a JOIN category_member b ON b.movie_name = a.name GROUP BY a.name, a.rating; rio g kids,comedy lotr_1 pg13 action,fantasy 

• If you specify the separator as,, the commas in the data from the GROUP_CONCAT operator will be escaped to avoid ambiguity:

   SELECT a.NAME, a.rating, GROUP_CONCAT(b.cat_name) FROM movie a JOIN category_member b ON b.movie_name = a.NAME GROUP BY a.NAME, a.rating; rio,g,kids\,comedy lotr_1,pg13,action\,fantasy 

  In standard Informix utilities, there is no way to avoid this; they do not leave the selected / uploaded data in an ambiguous format.

• I am not sure that the database schema is very well organized. The table of films is in order; the Category table is in order; but the Category_Member table would be more orthodox if it used the schema:

   DROP TABLE IF EXISTS category_member; CREATE TABLE category_member ( movie_id INTEGER NOT NULL REFERENCES Movie(Movie_id), category_id INTEGER NOT NULL REFERENCES Category(Id), PRIMARY KEY(movie_id, category_id) ); INSERT INTO category_member VALUES(4, 3); INSERT INTO category_member VALUES(4, 4); INSERT INTO category_member VALUES(1, 1); INSERT INTO category_member VALUES(1, 2); -- Use GROUP_CONCAT SELECT a.NAME, a.rating, GROUP_CONCAT(c.cat_name) FROM movie a JOIN category_member b ON b.movie_id = a.movie_id JOIN category c ON b.category_id = c.id GROUP BY a.NAME, a.rating; 

  The result of this query is the same as the previous one, but the connection is more orthodox.

Sample database

 DROP TABLE IF EXISTS movie; CREATE TABLE movie ( name VARCHAR(20) NOT NULL UNIQUE, rating CHAR(4) NOT NULL, movie_id SERIAL NOT NULL PRIMARY KEY ); INSERT INTO movie VALUES("rio", "g", 1); INSERT INTO movie VALUES("horton", "g", 2); INSERT INTO movie VALUES("blade", "r", 3); INSERT INTO movie VALUES("lotr_1", "pg13", 4); INSERT INTO movie VALUES("lotr_2", "pg13", 5); INSERT INTO movie VALUES("paul_blart", "pg", 6); DROP TABLE IF EXISTS category; CREATE TABLE category ( cat_name VARCHAR(10) NOT NULL UNIQUE, id SERIAL NOT NULL PRIMARY KEY ); INSERT INTO category VALUES("kids", 1); INSERT INTO category VALUES("comedy", 2); INSERT INTO category VALUES("action", 3); INSERT INTO category VALUES("fantasy", 4); DROP TABLE IF EXISTS category_member; CREATE TABLE category_member ( movie_name VARCHAR(20) NOT NULL, cat_name VARCHAR(10) NOT NULL, catmem_id SERIAL NOT NULL PRIMARY KEY ); INSERT INTO category_member VALUES("lotr_1", "action", 1); INSERT INTO category_member VALUES("lotr_1", "fantasy", 2); INSERT INTO category_member VALUES("rio", "kids", 3); INSERT INTO category_member VALUES("rio", "comedy", 4);