List from table field to pass to SQL In clause

[rbotham]

Hi everyone,

I am attempting to limit the scope of multiple SQL queries with In clauses by creating a list from a QlikView table. To do so, I have a subroutine that can be easily recycled:

SUB SQL_FILT_LIST(SRC_TABLE, SRC_COL)
TEMP:
NoConcatenate

LOAD
CONCAT(''''&$(SRC_COL)&'''', ',') AS TARGET
RESIDENT $(SRC_TABLE);

LET vFILT_LIST = PEEK('TARGET');
DROP TABLE TEMP;
END SUB;

Assuming I have a resident table GRPIDS with a field named GRPID, I can use:

CALL SQL_FILT_LIST('GRPIDS', 'GRPID');

to define vFILT_LIST and pass it to a SQL query ending with:

and GRPID IN ($(vFILT_LIST));

This works beautifully when I reload the script on my machine, but throws Error in expression: ')' expected upon execution of the subroutine when another user tries to reload.

Any ideas why? Is there a better way to accomplish this and avoid the error?

[Chanty4u]

Try to debug the variable 

TRACE vFILT_LIST: $(vFILT_LIST);

[rbotham]

It doesn't seem like it is an issue with the variable formatting, but rather in the subroutine that produces it. When I reload locally on my machine, I get no error, the variable is correctly formatted, and can be successfully passed to my SQL query. The trace function prints

vFILT_LIST: 'GRPID1', 'GRPID2', 'GRPID3'...

However, when my colleague attempts to reload the script he is met with the attached error. It looks like it fails before the LET vFILT_LIST line is even executed.

[henrikalmen · Accepted solution]

Maybe try this:

CONCAT(chr(39)&$(SRC_COL)&chr(39), ',') AS TARGET

[rbotham]

That worked great, also much more legible. Thank you!

Any idea why the original implementation only worked on mine, not my colleagues machine? We've run into a few other strange errors when reloading on his side that I'm unable to reproduce. He's running QlikView version 11.00.11149.0 while I have 12.8.20200.0

[henrikalmen]

Well, I'd say you answered your own question there. If I remember correctly there were kind of unusually big changes when QV12 arrived, compared to QV11. I don't know exactly why this particular thing differs between version, but QV11.0 is a really old product version by now. Differences are expected. (Everything that works in 11 should work in 12, but not the other way around.)

[rbotham]

Ha, that's kind of what I figured. I couldn't think of any other reason we'd be seeing the difference but wasn't sure about what exactly had changed between the versions.

I already told him to update - that subroutine cut reload times on some of our stuff literally in half and I really didn't want to roll everything back. FWIW, your fix worked great on both. Thank you again!