Create a list of strings and use it as a variable in a SQL IN clause

[dschillenger]

Hi dear all,

After several operations in the beginning of my script I have a table that lists a certain amount of booking references in a field

Spoiler

Job_References:
LOAD BOOKING_NUMBER Resident Facts_OL_tmp;

And I want to get some information from a very large SQL Developer table concerning these bookings. The best way to do it according to me is :

- To concatenate all the field values and to transform them in a list variable 

Spoiler

Job_References:
LOAD  Concat(chr(39) & BOOKING_NUMBER & chr(39), ',') as BOOKING_NUMBERS
Resident Facts_OL_tmp;

LET vBookingsNumbers = PEEK('BOOKING_NUMBERS');

- And add a IN clause to the SQL query that follows, using that variable

Spoiler

Bookings:
LOAD * ;

SQL SELECT *
FROM B_R_BOOKINGS
WHERE BOOKING_NUMBER IN ( '$(vBookingsNumbers ) ')

But it doesn't work, even if I don't use the single quotes. I think I could use a loop and concatenate the results but the SQL table is too big and it would take too much time.

If someone has any idea, it would be great 🙂

Thank you

[jaibau1993]

Hi!

It should work, we just have to find the correct place for all single and double quotes 😂

1. Which database are you connecting to? 
2. Have you checked that the content of the variable vBookingsNumbers is as you spect it?
3. Which data type has those booking numbers in the data base? I mean, should they be writen beetwen single/double/no quotes? Which is the correct syntax of that IN clause? 
4. I know you tried it but if you use the dollar expanssion within single quotes in the query, it is sent to the driver as a sentence beetwen single quotes and I dont think it to be correct since each booking number is writen in single quotes in your variable (Example: WHERE BOOKING_NUMBER IN ( ''1','2','3'') )

Regards!

Jaime.

[dschillenger]

Hi Jaime,

Thank you for your answer.

It works without the single quotes, you were right 🙂

Actually I spot another problem, I have a lot of booking numbers so the variable size kills the memory. Maybe I should find another way to do this thing. Or maybe loop with a limited number of bookings at each iteration.

Do you have an idea ?

David

[jaibau1993]

Uau, I've never seen something as big. If you have as much booking numbers maybe the WHERE clause does not bring eficience to the problem. I mean, in your case it may be better to query all the data and then filter it in QlikView side with an Exists function. Moreover, you can query all the data once, store it in a QVD file and implement an incremental load to maintain the QVD updated. The approach depends on the complexity of the problem.

Regards,

Jaime.

[dschillenger]

I saw that the limit is 1 000 for an IN clause in ORACLE. So there's a real problem when you have 1 000 000 values 😁

The problem is about app loading performance because like I said the table has several hundreds millions of rows. So I did it to avoid the full loading and storing of the table in a QVD file. Maybe I should try to loop on the booking number values 1 000 by 1 000, store QVD temp files and concatenate everything at the end of the operation. 

[marcus_sommer]

An alternatively to your where-clause could be to use your table Job_References as filter in an inner join. I assume this table is the result of the same database and therefore you could create it there as table. That would be the easiest way because otherwise you will need to transfer these data from Qlik (AFAIK there are ways to write back) or from the outside (just storing it anywhere as csv).

- Marcus

[dschillenger]

Hi Marcus,

Thank you for your answer.

That's the problem, the first table comes from a different database so I can't join them with a SQL. Moreover the first table is built with a table from the first database inner joined with a xls referential that doesn't come from any database. That's a complex issue I struggle with. The loop in the SQL statement is the best way I've found until now.

David.

[JGMDataAnalysis]

Try with this...

Job_References_Map:
MAPPING LOAD BOOKING_NUMBER, 1 RESIDENT Facts_OL_tmp;

Bookings:
NOCONCATENATE 
LOAD *
WHERE ApplyMap('Job_References_Map', BOOKING_NUMBER, 0)
;
SQL SELECT * FROM B_R_BOOKINGS;

[marcus_sommer]

Ok. you couldn't generate this filter-table within your db but you could upload it there. It may not look very nice to upload an csv-file just for this reason but I think there is no other way to restrict the data already on the sql-side if features like in() are too limited to filter the entire dataset - by smaller datasets the in() might be applied in loops - but by larger ones it's not very effective.

An alternatively could be to filter the data on the Qlik-side for example by implementing incremental approaches and just to load new/changed data - maybe by applying multi-stages incremental methods, to pull at first the new data in regard to a date and on this are further stages/measures applied.

- Marcus

[dschillenger · Accepted solution]

Hi everyone,

Thank you again for your participation.

Finally I had to develop another approach because the large amount of bookings is terrible for the performance of the query. So I simply applied filters on the table and iterate through it to create monthly qvd's. But I don"t use the referential file, I'll join the information to this ref later in the process. The performance is now acceptable for me. 

Thank you and have a great day.

David