problem in clause in oracle 10g

[JMAROUF]

Hello everyone,

i want to load just the informations wich i need basing on IDs, my probleme is the limitation of IN clause in oracle 10g, i can't upgrade to oracle 11g, so if i execute load it will bring over 50 000 000 lines. did anyone run into the same  issue before? thank you for advance.

[Anil_Babu_Samineni]

I have an idea without upgrade the system (Oracle 10G)

Table:

Load ID Where Match(ID, 'ID1', 'ID2'); // Here ID1, ID2 are your ID numbers..

SQL Select ID from Public.table;

[JMAROUF]

Hi @Anil_Babu_Samineni , but i will bring 5000000 lines with select statment, i want avoid this, i want bring just what i want with select statment.

[Anil_Babu_Samineni]

Can you please put the select statement you want to write. We will try the way same in Qlik since 10G is not allowed for you.

[marcus_sommer]

You could - within the sql - replace the in() with an inner join, like in this simplified example:

sql select * from a; inner join select ID from b;

If b isn't within your db else any Qlik result you could store it as txt and load it into your db before executing your real query.

- Marcus

[JMAROUF]

Hi @marcus_sommer 

b  isn't in my database and i don't have the right to load it  in the DB, it's forbidden to write any thing in database.

[JMAROUF]

Hi @Anil_Babu_Samineni 

i have some IDs in a qvd table and i want execute this statment:

select

x,y,z

from "table"

where x in ( id1,id2,...............................,id n) ;

the probleme with oracle 10g is we can't go over 1000 values, in my case i have about 75000 values ==> n =75000.

[JMAROUF · Accepted solution]

thank you every one for your help, i've solved the problem, here is the solution below:

LET vFiltre='';

DESTINATION_TABLE:
LOAD * inline[ACCOUNT_VALUE,ID_ACCOUNT,ID ]
;

FILTER_TABLE:
LOAD distinct ID,
num(RowNo()) as numLigne  resident MY_SOURCE;

Let lignes = NoOfRows('FILTER_TABLE');
Let iterations= num($(lignes)/1000);

for i=0 to $(iterations)-1

FILTER_TMP:
LOAD
Concat(DISTINCT ID, ',') as Filter_code
resident FILTER_TABLE  where numLigne > ($(i)*1000) AND numLigne <= (($(i)+1)*1000) order by numLigne asc;


LET vFiltre='(' & FieldValue('Filter_code', 1) & ')';

Concatenate(DESTINATION_TABLE)

LOAD
ACCOUNT_VALUE,
ID_ACCOUNT,
ID;
SQL SELECT "ACCOUNT_VALUE",
"ID_ACCOUNT",
"ID"
FROM LEARNINGSQL."TEST_ACCOUNT" where ID in $(vFiltre);

drop table FILTER_TMP;
let vFiltre = null();
next

DROP Table FILTER_TABLE;