Group Concat

[DL]

Hi,

Is there any easy way you can simulate GROUP_CONCAT functionality in Ingres 9.2?

I have a table which has something like :

OrderID LineNumber LineText
1 1 This is an example note which is trunc
1 2 ated at a certain point.
2 1 Another note which is just one line.

And so on. Some notes are 1 line, others are 50+ lines.

I want a query to return:

OrderID FullText
1 This is an example note which truncated at a certain point.
2 Another note which is just one line.

In MySQL or SQLite I'd use GROUP_CONCAT. In MS SQL it's more difficult but I'd use the FOR XML functionality to achieve a solution. I'm not sure how I could do this in Ingres. I started writing a stored procedure which could return the concatenated notes for a single order id, but I couldn't see an easy way of integrating that into my queries.

Any ideas?

[Roy Hann]

DL wrote:

> Is there any easy way you can simulate GROUP_CONCAT functionality in Ingres 9.2?

Short answer: no.

Much longer answer: you *might* be able to implement group_concat
yourself using OME.

> Any ideas?

Perhaps your example text fragments were small just to make the
illustration easy. But if they really are that small my first choice
would be to just declare the varchar column big enough to hold the
entire string without fragmenting it. Reconstruct the notes in the new
table just once and you're done. If few of the notes require the full
allocation of space declare the table WITH COMPRESSION=(DATA); it
reduces the space required though it doesn't really compress and there
is negligible overhead.

--
Roy

UK Actian User Association Conference 2013 will be on Tuesday June 11 2013.
Register today at http://www.regonline.co.uk/ukiua2013

[Steve McElhinney]

On Tuesday, 28 May 2013 16:01:50 UTC+1, DL wrote:
[...]
> Any ideas?

Hmmm not sure if this is going to help, but I'm pretty sure this kind of conversion can be done using Ingres Report-Writer, which effectively adds another layer of processing to the output of an SQL statement.
You can use RW to output a text/data file, which is clean, i.e. no headers.

RW is documented in Chapter 10 of the Character-based Querying and Reporting Tools User Guide.

HTH
Steve

[Ingres Forums]

How about a row producing procedure?

Jeremy


--
jruffer
------------------------------------------------------------------------
jruffer's Profile: http://community.actian.com/forum/member.php?userid=504
View this thread: http://community.actian.com/forum/showthread.php?t=15234

[darian....@gmail.com]

> Perhaps your example text fragments were small just to make the
> illustration easy. But if they really are that small my first choice
> would be to just declare the varchar column big enough to hold the
> entire string without fragmenting it.

Thanks for the reply.

Unfortunately, the data is in a system which is from a third party (i.e. I can't alter the table structure or the application that uses it).

Some notes are quite long (I think there's a couple with more than 2000 lines) but the majority are less than 50 lines long. If the system were being made today I suspect they would have each note in a LONG VARCHAR column (or equivalent).

[darian....@gmail.com]

> How about a row producing procedure?
> Jeremy

I did consider that, but how can I call / reference such a procedure in other SQL statements?

[Roy Hann]

You can't in 9.2, but in 10 you can refer to a row-producing procedure
in the FROM clause like any other table or view.

Well maybe you can do it in 9.2 as well, but I'm told it's potentially
fragile.

[Ingres Forums]

>
> Well maybe you can do it in 9.2 as well, but I'm told it's potentially
> fragile.

I got it working in 10.0 but 9.2 seems to be rather restrictive.