topic Re: How to combine record from resident table into SQL where clause to another DB in App Development

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

Roberto03 — Fri, 02 Dec 2022 11:21:30 GMT

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

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

marcus_sommer — Fri, 02 Dec 2022 11:51:20 GMT

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

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

Roberto03 — Fri, 02 Dec 2022 14:09:55 GMT

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

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

marcus_sommer — Fri, 02 Dec 2022 15:31:32 GMT

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

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

Roberto03 — Fri, 02 Dec 2022 15:42:20 GMT

perfect! thanks.