Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have created table1 which is populating data from Oracle source.I want to use it into in where condition in the SQL query to check the distinct DisplayName.
How can I do this ? Please suggest ,below approach is giving me error message
Table1:
LOAD *;
SQL
SELECT DISTINCT QV_EXEC_NAME AS "DisplayName" FROM Myview;
QV_UserAccess:
LOAD *;
//get all live users for usage stats
SQL
SELECT
DisplayName
FROM Tab2
WHERE QVAppID = 3
AND ActiveLogin = 1,
AND DisplayName NOT IN ( Select DisplayName from Table1)
What i did in a similar case was something like that:
Accounts:
LOAD Distinct
NewAccount as ArrangementIdInSourceSystem
FROM
[D:\Accounts.xlsx]
(ooxml, embedded labels, table is NewAccount);
LET vNoOfRows=NoOfRows('Accounts');
FOR i=1 to $(vNoOfRows)
LET vTmp=FieldValue('ArrangementIdInSourceSystem',$(i));
IF $(i)= 1 THEN
SET vAcc= "'$(vTmp)'";
ELSE
SET vAcc=$(vAcc),'$(vTmp)';
ENDIF
NEXT;
DROP TABLE Accounts;
Data:
SQL
SELECT
AR01T.UNQID,
AR01T.ARID
FROM
AR01T AS AR01T
WHERE
AR01T.UNQID IN ($(vAcc))
What i did in a similar case was something like that:
Accounts:
LOAD Distinct
NewAccount as ArrangementIdInSourceSystem
FROM
[D:\Accounts.xlsx]
(ooxml, embedded labels, table is NewAccount);
LET vNoOfRows=NoOfRows('Accounts');
FOR i=1 to $(vNoOfRows)
LET vTmp=FieldValue('ArrangementIdInSourceSystem',$(i));
IF $(i)= 1 THEN
SET vAcc= "'$(vTmp)'";
ELSE
SET vAcc=$(vAcc),'$(vTmp)';
ENDIF
NEXT;
DROP TABLE Accounts;
Data:
SQL
SELECT
AR01T.UNQID,
AR01T.ARID
FROM
AR01T AS AR01T
WHERE
AR01T.UNQID IN ($(vAcc))
It worked and corrected syntax error issue
AND DisplayName NOT IN ('$(vWhereStatement)');