topic AW:Re: AW:Load .qvd in Oracle SQl where question in QlikView

Load .qvd in Oracle SQl where question

Tue, 25 Aug 2009 21:49:42 GMT

Hi, I have a number of IDs stored in a .qvd file. Now I want to Load more data for all these IDs from a Oracle database. I was thinking about doing it like this:

tSALES:
LOAD
..
ID,
..;
SQL SELECT
..
ID,
..
FROM IFSAPP."CASH_RECEIPT_LINE_JOIN" where ID IN (LOAD ID from 'qvd\tMISSING_IDS.qvd' (qvd));

The problem is that the IN statement in Oracle is not accepting loading a .qvd file.
I believe the answer to this would be to load tMISSING_IDS.qvd, see example below, first and then access the values like an array of variables. Something that makes it look like a list of numbers to Oracle and not a .qvd file.
I know I can do this when I use variables.

Here is the qvd loaded as usual.
tMissingID:
LOAD ID from 'qvd\tMISSING_IDS.qvd' (qvd);

Thanks for help, let me know if the post was understandable. Regards.

AW:Load .qvd in Oracle SQl where question

biester — Tue, 25 Aug 2009 23:52:07 GMT

Hi,

if I understand correctly, a solution to your problem would be:

Load IDs from the missing_IDS.qvd into a TEMP-Table, using ID as a key
NOCONCATENATE Load data for tSALES from Oracle, using WHERE exists()
Delete TEMP-Table

If you need further assistance, I can post a demo.

Rgds,
Joachim

AW:Load .qvd in Oracle SQl where question

Wed, 26 Aug 2009 16:20:18 GMT

Hi Joachim, thanks for your answer. It seems like Oracle wants a sub query inside the parenthesis when you use the Exists() statement. I agree with the logic you wrote but still cant get it to work. If you have a demo it would be wonderful. Regards.

AW:Re: AW:Load .qvd in Oracle SQl where question

biester — Wed, 26 Aug 2009 16:39:41 GMT

Hi Ludvig,

I tried to build an environment similar to yours so you only have to modify few things (Oracle connect, path to QVD, Oracle scheme). Have a look at it and see if you're getting it to work.

Main thing I suppose is that you have to use the "WHERE EXISTS" in the LOAD-Clause, NOT in the SQL SELECT-Clause!

Rgds,
Joachim

AW:Re: AW:Load .qvd in Oracle SQl where question

Wed, 26 Aug 2009 20:12:44 GMT

I can use it in the LOAD clause but the loading time is vastly longer so I am loosing the benefit. If I cant get it to work I will have to that.