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‌

[rubenmarin]

Hi Sunny, I didn't used that function yet but as an idea, in case there is a problem with the oledb retrieving the results directly from that function, you can try enclosing the query as:

SELECT * FROM (YourActualQuery); //hope it works as a workaround

[sunny_talwar]

So enclose my actual query within a SELECT * from???

[rubenmarin]

I don't know why is returning that error, but maybe it has to something it returns when using the function, hoping that enclosing the result in a simple select, reformats the query in a qv-compatible way.

Just a wild idea.

[sunny_talwar]

Let me test that out. Thanks for the idea

[sunny_talwar]

That did not work. Any other ideas? Do you think ODBC will be able to execute this?

[Anonymous]

the data is coming from oracle or microsoft sql server?

[sunny_talwar]

Oracle

[Gysbert_Wassenaar]

I assume your using the Oracle OLEDB driver from Oracle and not the one Microsoft ships. If not, try the driver from Oracle.

Another thing you can try is defining a view based on the sql statement and then do a select from the view.

And no, I really have no idea what's going on. Good luck

[sunny_talwar]

I believe we are using the Oracle OLEDB connection. This is the one we use:

We are talking about the view, but I just wanted to know if there is a way around that.