QlikView and SQL's ListAgg() function

[sunny_talwar]

Hi QlikView and SQL Experts -

Does anybody have experienced an issue with creating a new field using ListAgg() function in SQL Select statement? I am getting this error when I try to run with ListAgg()

QVX_UNEXPECTED_END_OF_DATA:

Error: OLEDB read failed

What I am looking to do is very simply. Lets say I have the following data

Key     Dim     Sales

1     India     50

2     India     100

3     India     20

4     US     30    

5     US     60

6     US     120

I want this from SQL (and not QlikView)

Key     Dim     Sales

1|2|3     India     170

4|5|6     US     210

SQL Query runs without any error in SQL Developer, but errors out in QlikView when I use ListAgg() function to create 1|2|3

Looking for some advice here gentleman

pcammaert‌, gwassenaar‌‌, swuehl‌‌, johnw‌, marcowedel‌, jagan‌, rubenmarin‌

[sunny_talwar]

Good idea, let me try ; this may be

[sunny_talwar]

Just to add a little more information -> when I run this on QMC, the application runs for 24-25 minutes before it errors out. So it seems that it is executing something.

[johnw]

We don’t have LISTAGG in our environment. We do have XMLAGG, which works running natively, and doesn’t work from QlikView. The issue there seems to be that while natively it produces what looks like text, it’s not actually text, and I can’t even cast it as character data. The only thing that suggests to me is that maybe the return type of LISTAGG is unusual and QlikView doesn’t know what to do with it? Looks like the return type in VARCHAR2. I assume that’s not a problem.

The gist of what I’m reading is that this sort of thing is often caused by data problems. For instance, if you have a division in your SQL, and the database has a 0 on one row, or you’re doing date logic, and one of the rows had an invalid date. That sort of thing. And that’s logical to me with it running fine for 25 minutes and then bombing out. There’s some record it hits at the 25 minute mark that blows up the SQL in a way that only blows up QlikView, not SQL developer. That’s my working theory, at least.

And if you remove the listagg(), it works, right? So it would have to be a data error that affects that function.

Is the column you’re aggregating potentially null? If so, does LISTAGG handle nulls? Documentation says it ignores nulls, so that shouldn’t cause a failure. What if every value for a group is null? I’m unclear what it returns then. I’m guessing null. Still, I’d hope QlikView doesn’t choke on nulls. I guess I could check by getting a null back from a left join. Yeah, QlikView handles it fine, just loads it as null. That wouldn’t be a unique ID if there were more than one row like that, but it wouldn’t bomb out the SQL.

Yeah, other than saying “maybe bad data is somehow causing the function to fail”, I’ve no idea. I can’t see how to cause it to fail, even if I were trying.

[sunny_talwar]

John -

Thanks for doing some comprehensive testing. I guess you are seeing the same issue using XMLAGG which I am seeing using LISTAGG. In one of the tests. Moving onto the reasons why this might have happened (although all these are hypothesis, but might make sense to assume that they might not be causing the issue)

1) The field used within ListAgg() is a Unique Identifier for the fact table. I don't think it can be null. So, I think it is safe to think that null might not be causing this issue

2) One thing that came to my mind was the fact that this could be because of a very long key which QlikView is not able to handle for some reason. But to get rid of those extra long keys, I tried adding a Having statement at the end but even this failed. I guess I can try with Count(DISTINT Key_Field = 1 and see what happens.

Having Count(DISTINCT Key_Field) <= 2

It does work if I remove the ListAgg() function from the script. So my guess is that it has to do something with ListAgg(), but again, what exactly happens after the 20 minute mark that causes the issue?

What are some other things we can categorize as bad data in this case? May be you can point me to a link where I can read about this?

Finally, I would just like to mention that I have already bugged you over this for quite a bit. You don't have to spend even a single more minute on this because after Friday I won't even have access to all of this and I wouldn't be able to test anything out. I don't want you to keep wasting your important time for a almost lost cause. (Almost because we do have the option to use views )

Best,

Sunny

[rubenmarin]

Hi Sunny, looking over internet for other ways to do concatenation... I don't have actually a QV in a Oracle environment available, but similar query returned the results in PL/SQL Developer over Oracle

SELECT Dim,

       LTRIM(MAX(SYS_CONNECT_BY_PATH(key,'|'))

       KEEP (DENSE_RANK LAST ORDER BY curr),'|') AS UNIQUE_IDENTIFIER,

       Sum(Sales) AS Sales

FROM   (SELECT Dim,

               key,

               ROW_NUMBER() OVER (PARTITION BY Dim ORDER BY key) AS curr,

               ROW_NUMBER() OVER (PARTITION BY Dim ORDER BY key) -1 AS prev,

               Sales

        FROM   TableName)

GROUP BY Dim

CONNECT BY prev = PRIOR curr AND Dim= PRIOR Dim

START WITH curr = 1;

[sunny_talwar]

Does this PL/SQL have a slightly different syntax than a regular SQL? I am not sure I understand some of the syntax here . Never used PL/SQL before so have no idea. Is there a SQL equivalent of this which I can try?

[rubenmarin]

I also learned some keywords that I didn't know they exist until now.  I get I idea of what is doing but I will be unable of reproduce without copy/paste.

If it helps: it also worked on toad

[sunny_talwar]

Let me give it a try and see if I can make this work. Thanks for giving some direction.

Best,

Sunny

[swuehl]

Have you tried to run your SELECT LISTAGG() on a different / smaller table / fewer records of same table, just to check that the code itself performs?

It might also be helpful if you post more information, like your complete SQL statement, more details about your table source etc.

[sunny_talwar]

Stefan -

I did try the complete SQL in SQL Developer and it works. In QlikView, I tried adding a Having statement to restrict to a smaller subset of data. Without ListAgg() there are 3+ million rows of data. With ListAgg() + Having, Expect a number lower than that. Do you think it makes sense to reduce it further down?

Best,

Sunny