Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor III

problem in clause in oracle 10g

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.

Labels (2)
1 Solution

Accepted Solutions
Highlighted
New Contributor III

Re: problem in clause in oracle 10g

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;

View solution in original post

7 Replies
Highlighted

Re: problem in clause in oracle 10g

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
New Contributor III

Re: problem in clause in oracle 10g

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.

Highlighted

Re: problem in clause in oracle 10g

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.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
MVP & Luminary
MVP & Luminary

Re: problem in clause in oracle 10g

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

Highlighted
New Contributor III

Re: problem in clause in oracle 10g

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.

Highlighted
New Contributor III

Re: problem in clause in oracle 10g

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.

Highlighted
New Contributor III

Re: problem in clause in oracle 10g

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;

View solution in original post