Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
saivina2920
Creator
Creator

How to use one server db column value to another server where clause

I am using two databases. one is SQL server and another one is oracle db.

First :

The purpose of SQL server to use, i am maintaining all predefined values in this SQL Server table. instead of using hardcode in the Qlik variable, i am just using SQL Server Table.

Second:

The second DB is oracle, my all requirement columns and values have been defined in this DB.

my requirement is, i want to pass SQL server table value in Oracle table where clause.

How to pass multiple values (SQL Server column values) in Oracle where clause.

For Example,

SQL Server table is below :

vENO    vNAME   vPLACE

101         JIVE         vUSA

102          RIJI         UK

103          BIJA       CANADA

My Qlik Script is below

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xxxx;Initial Catalog=SQLTEST;Data Source=xxxxx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=xxxx;Use Encryption for Data=False;Tag with column collation when possible=False];

SQL_EMP_DETAILS:
LOAD vENO,
vENAMe,
vEPLACE;
SQL SELECT *
FROM TESTRA12.dbo."tbl_Emp_Details";

 

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=xxxxx;Data Source=xxxxxx;Extended Properties=""] (XPassword is xxxxxxxx);


SELECT EMPNO,EMPNAME,EPLACE,ECOUNTRY where EMPNO in () ?????? // here pass SQL server column value --> vENO

How to do this..?

pls. reply immediately, i am struggling in between...

 

Thanks...

Labels (3)
4 Replies
dilipranjith
Partner
Partner

You can use exists function within qlik script not on the sql query.
https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Inter...

i.e. load data as is and the resident load with exists function
saivina2920
Creator
Creator
Author

i understood but little bit confused.
can i have some example scripts or file..?
dilipranjith
Partner
Partner

Something like below (very rough code) .

 

SQL_EMP_DETAILS:
LOAD vENO,
vENAMe,
vEPLACE;
SQL SELECT *
FROM TESTRA12.dbo."tbl_Emp_Details";

 

Temp:

SELECT EMPNO,EMPNAME,EPLACE,ECOUNTRY;

noconcatenate

FinalTemp:

load * 

resident Temp where exists(EMPNO,vENO);

drop table Temp;

saivina2920
Creator
Creator
Author

Thanks for your reply
Is it possible to use same in the select statement.
because my query is too large and formed using select statement with many unions,
i just pass only the variable in the where clause. now it is hardcoded like my above example.