SQL output to multiple SQL input

[harsha]

Hi Experts,

I'm facing an issue while loading. 

Requirement : Every day, I run a query in Oracle SQL (TOAD) and it gives me 20k + IDs.  and I need to run another query to get values in Oracle SQL. However, oracle SQL has 1000 expressions constraint and i needed to write 20 queries with UNION operator. 

Is there a way that i can automate this in Qlik Sense? Ex: 1st query output should split by 1000 IDs and each thousand should be placed in each Query's IN operator as variable or something.

Query 1 output:

select * from Test where country = 'ABC' and

output IDs:

1,2,3,4 ....... 19999, 20000

Query 2: 

Select * from ABC where ID in (1-1000)

UNION

select * from ABC where ID in (1001 - 2000)

UNIO

select * from ABC where ID in (2001 - 3000) etc to 200000

[kaushiknsolanki]

Hi,

You should use the subquery to  get all this records in single query. For example,

Select * from ABC where ID in

(Select Distinct ID from Test where country = 'ABC' )

[harsha]

Hi Kaushik,

but here the problem is, Oracle SQL would not accept more than 1000 expressions in one query.

In addition, 

first query gives me all the eligible IDs and then, i need to run 2nd query to fetch values. 

Let me know if you have any questions

[harsha]

@stevedark  pls help 

[stevedark]

Hi @harsha 

I'm not sure why you need to load in that way, it seems very strange to get all those separate ranges.

That aside you should be able to do it something like this:

for i = 0 to 200
   let vRange = ((i * 1000) + 1) & '-' & ((i+1)*1000);

   SQL Select * from ABC where ID in ($(vRange));
next

The queries will auto-concatenate, as the field list will always be identical.

Not sure I can see the reasons for doing it that way, rather than getting all at once.

You might want to store each range to QVD and drop the table after each set of data, and then load from all of them at the end. This will allow you to build incremental loads and just get new records in future, for example.

Actually, just realised what you are saying about the IDs from the first query. You could do something like:

IDs:
select ID from Test where country = 'ABC';

let i = 0;
let size = 999;
let IDs = '';

for i = NoOfRows('IDs') -1
    let IDs = IDs & if(IDs = '', '', ',') & Peek('ID', i, 'IDs');

    if mod(i,size) = 0 then
        Select * from ABC where ID in ($(IDs));
        let IDs = '';
    end if
next
if IDs <> '' then
    Select * from ABC where ID in ($(IDs));
end if

Again, the auto-concatenation should take place.

I don't know Oracle so well, but in SQL Server you would do a nested-subquery, which would be much more efficient. Something like:

SELECT * FROM ABC
WHERE ID IN (SELECT ID from Test where country = 'ABC')

Suspect that Oracle will have similar.

Hope that helps.

Steve

[harsha]

many thanks for the quick response. 

Yes, this is a quite strange with Oracle SQL.

WIll give you a brief back ground to it. We are comparing the data from Source DB to Dest DB. So, Oracle DB allow only 1000 expression at IN operator. So, my 1st query results fetch all eligible IDs and those are to be used for getting values with another query.

Here is the prob: 1st query gives me 20000 IDs and i had to create 20 queries by breaking 20000 records by 1000.

This is a daily task and I want to get this automated. 

[Kushal_Chawda]

@harsha 

You can generate in query with multi-variate in clause with some logic built in qlik.

First run the SQL query which generates ID

IDs:
select distinct ID from Test where country = 'ABC'

then prepare multi-variate in clause for oracle query which has limitations (100,000 instead 1000 in normal in)but it will be enough. This in clause we can prepare it in qlik..something like  below

where  ('temp',ID) in (

('temp','1'), ('temp','2'),......('temp','20000'))

Logic goes below like

make_in:
LOAD Concat('('&chr(39)&'temp'&chr(39)&','&chr(39)&ID&chr(39)&')',',') as ConcatID
resident IDs;

let vIDs = Peek('ConcatID',0,'make_in');

Now you can run below single query

select * from ABC where ('temp',ID) in ('$(vIDs)'); // with quotes

or

select * from ABC where ('temp',ID) in ($(vIDs)); // without quotes

If your ID exceeds 100,00 then you need to create temporary table in which you can put all the require id with initial query.(This has to be done in oracle and not via Qlik) Then in Qlik you can run below final query

select where id in (select id from temptable)