topic How to use one server db column value to another server where clause in QlikView

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

saivina2920 — Sat, 16 Nov 2024 03:36:00 GMT

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...

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

dplr-rn — Mon, 13 May 2019 15:15:00 GMT

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/InterRecordFunctions/Exists.htm

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

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

saivina2920 — Mon, 13 May 2019 16:57:29 GMT

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

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

dplr-rn — Mon, 13 May 2019 18:45:19 GMT

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;

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

saivina2920 — Tue, 14 May 2019 00:40:17 GMT

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.