Load .qvd in Oracle SQl where question

[#1]

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.

[biester]

Hi,

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

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

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

Rgds,
Joachim

[#3]

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.

[biester]

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

[#5]

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.