How to combine record from resident table into SQL where clause to another DB

[Roberto03]

Hello,
in a table I have a list of products.

PRODUCT:
LOAD DISTINCT AUG_COD_IMP
RESIDENT QLIK_PRODUCT_TABLE;

I should use these product codes to filter an extractions towards an oracle DB.

However, by inserting the records as a join or in the where clause, the system looks for the qlik table in the oracle db and then returns the error as "table not found".

LIB CONNECT TO 'DB_ORDER';

ORDERS:
SELECT
AUG_COD_IMP,
HTM_DATA_CAMPIONAMENTO,
FROM TELEMISURA inner join PRODUCT l on (l.AUG_COD_IMP = AUG_COD_IMP)
WHERE 1=1
;

How can I insert the record from qlik table into the sql query in order to avoid the extraction of the whole db?

THANKS

[marcus_sommer · Accepted solution]

If the amount of data isn't too big (depends on the database + network performance and the accepted run-times) you could apply the filter on a preceding load, like:

ORDERS:
load * where exists(AUG_COD_IMP);
SQL SELECT
AUG_COD_IMP,
HTM_DATA_CAMPIONAMENTO,
FROM TELEMISURA;

which means that all records are pulled from the database at first and afterwards filtered. Another way could be to concatenate all values into a string and using this within an IN(), for example like:

PRODUCT:
LOAD DISTINCT concat(AUG_COD_IMP, ',') as AUG_COD_IMP
RESIDENT QLIK_PRODUCT_TABLE;

let var = fieldvalue('AUG_COD_IMP', 1);

SQL SELECT
AUG_COD_IMP,
HTM_DATA_CAMPIONAMENTO,
FROM TELEMISURA where AUG_COD_IMP in ($(var));

Depending on the kind of content the string-creation needs to include a quotation of the values. AFAIK the most databases have a limit how many values an IN() could contain.

If both methods are not practicably you could store the Qlik table as csv and importing them as a temporary table within the database and then the data are there natively available.

- Marcus

[Roberto03]

Thanks, I think that the "concat" solution is the best for my case. But how to sorround the product code with " ' " ex: '12345','98765'? thanks

[marcus_sommer · Accepted solution]

You could try it in this way:

PRODUCT:
LOAD DISTINCT concat(chr(39) & AUG_COD_IMP & chr(39), ',') as AUG_COD_IMP
RESIDENT QLIK_PRODUCT_TABLE;

- Marcus

[Roberto03]

perfect! thanks.