Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I am absolutely noob on QlikView and I am facing my first issue when trying to retrieve data from an Oracle 11g Database, using a very simple query.
My issue: SQL queries are executed correctly, but no data is received.
As an example, I have a very simple SQL query that returns 55 rows in any other application like Oracle's SQLDeveloper, TOAD, etc:
SELECT
OPUN.NAME
,OPUN.ORGANIZATION_ID
,OPUN.LOCATION_ID
,OPUN.BUSINESS_GROUP_ID
FROM
APPS.HR_ORGANIZATION_UNITS OPUN
WHERE OPUN.NAME LIKE '%_OU'
AND OPUN.NAME NOT LIKE '(DO NOT USE)%'
ORDER BY OPUN.NAME ASC
;
However, when running the same query in QlikView, I am always receiving zero rows. It gets executed without any errors, but no data is received, as you can see in the following image:
However, when using the same source code file, if I run a dummy query against Dual, I am able to obtain data and save it into a QVD file:
Dummy query:
SELECT 1 A, 2 B FROM Dual
UNION
SELECT 2 A, 1 B FROM Dual
UNION
SELECT 3 A, 3 B FROM Dual
;
Does this sound like a known issue?
Thanks and regards in advance for any help on this issue!
My source code:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
// SET APPLICATION RUN START TIME
LET vSTART = NOW();
//$(Include=..\config\path_variables.txt);
SET vProjectQvdDir = '../qvd/';
SET vProjectDataDir = '../data/';
SET vSharedQvdDir = '../../shared/qvd/';
SET vSharedDataDir = '../../shared/data/';
SET vCorpSharedQvdDir = '../../../shared/qvd/';
SET vCorpSharedDataDir = '../../../shared/data/';
ODBC CONNECT TO [blprdstb;DBQ=BLPRDSTB] (XUserId is XXXXXXX, XPassword is XXXXXXX);
// MY FIRST TEST: EXTRACT DATA FROM THE EBS DATABASE AND SAVE IT TO A QVD FILE USING A VERY SIMPLE SQL QUERY:
D2B_Test01:
SQL
/*
SELECT 1 A, 2 B FROM Dual
UNION
SELECT 2 A, 1 B FROM Dual
UNION
SELECT 3 A, 3 B FROM Dual
;
*/
//--[ BLY OPERATING UNITS: ]--//
SELECT
OPUN.NAME
,OPUN.ORGANIZATION_ID
,OPUN.LOCATION_ID
,OPUN.BUSINESS_GROUP_ID
FROM
APPS.HR_ORGANIZATION_UNITS OPUN
WHERE 1=1
AND OPUN.NAME LIKE '%_OU'
AND OPUN.NAME NOT LIKE '(DO NOT USE)%'
ORDER BY OPUN.NAME ASC
;
// CHECK NUMBER OF RECORDS RETRIEVED
LET vCheck_Data = NoOfRows('D2B_Test01');
IF $(vCheck_Data) > 0 THEN
Store D2B_Test01 into $(vProjectQvdDir)D2B_Test01__OperatingUnitsQuery.qvd (qvd);
//Store D2B_Test01 into $(vProjectQvdDir)D2B_Test01__DummyQuery.qvd (qvd);
END IF
// CLEAR TABLE FROM MEMORY
Drop table D2B_Test01;
// SET APPLICATION RUN END TIME
LET vEND = NOW();
// EXIT
EXIT SCRIPT;
Hi,
you are missing load statement, shold be:
D2B_Test01:
LOAD
OPUN.NAME,
,OPUN.ORGANIZATION_ID
,OPUN.LOCATION_ID
,OPUN.BUSINESS_GROUP_ID
SELECT
OPUN.NAME
,OPUN.ORGANIZATION_ID
,OPUN.LOCATION_ID
,OPUN.BUSINESS_GROUP_ID
FROM
APPS.HR_ORGANIZATION_UNITS OPUN
WHERE 1=1
AND OPUN.NAME LIKE '%_OU'
AND OPUN.NAME NOT LIKE '(DO NOT USE)%'
ORDER BY OPUN.NAME ASC
;
Also, try to load data from database using "SELECT" button in Edit Script window and take a look how select statement was generated, pay attention on quote characters in select string.
regards
The LOAD statement isn't a requirement so I don't think that's the problem. The only thing I can think of right now is that the user you connect as is missing some grants. Are you connecting with the same user in Toad?
Do you get any rows back without the WHERE clause?
why where 1=1 ??
to unoptimize qvd load ??
Hi,
My few ideas:
- Check the user permissions
- Remove the SQL comments - coz SQL Statement is not formatting in the way you write it; especially CR/LF are ignored
- Create a new simple QVW to check if you can retrieve any kind of data (mind the ODBC connection)
Best regards,
Tom
Hi Gysbert,
Yes, I am able to connect using TOAD, SQLDeveloper and other applications that hangs from same ODBC and/or TNSNAMES.ORA file
Thanks for your time and reply. Regards!
Hi flipside,
Out of QlikView (Toad, SQLDeveloper, SQL Plus, etc ) I am getting rows. But, when inside QV, I am not getting anything.
Thanks for your time and reply! Cheers!
Just because it is a sample query generated by a 3rd party application. It is not relevant for the issue as the Explain Plan changes only in a few MS with or without it.
Thanks.
Hi Thomas,
Thanks for your response and ideas. I followed them and I was able to reduce a bit the scope of the issue. Now I know the problem is at some point between the combination of my Windows 7 64-Bit OS + ODBC + Oracle 11g R2 Client.
After I saw your comments, I installed the QV 32-Bit client over a Windows 2003 Server using Oracle 11gR2 Client, hosted on a VirtualBox VM and voilà, my code worked correctly:
Following all the advices, I have modified the code to not use includes, remove comments, aliases and the "WHERE 1=1" statement, so now the code looks like this:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
// SET APPLICATION RUN START TIME
LET vSTART = NOW();
//$(Include=..\config\path_variables.txt);
SET vProjectQvdDir = '../qvd/';
SET vProjectDataDir = '../data/';
SET vSharedQvdDir = '../../shared/qvd/';
SET vSharedDataDir = '../../shared/data/';
SET vCorpSharedQvdDir = '../../../shared/qvd/';
SET vCorpSharedDataDir = '../../../shared/data/';
// DB CONNECTION STRING
ODBC CONNECT TO [blprdstb;DBQ=BLPRDSTB] (XUserId is XUNKOKO, XPassword is XUNKOKO);
// MY FIRST TEST: EXTRACT DATA FROM THE EBS DATABASE AND SAVE IT TO A QVD FILE USING A VERY SIMPLE SQL QUERY:
D2B_Test01:
SQL
SELECT NAME, ORGANIZATION_ID, LOCATION_ID, BUSINESS_GROUP_ID FROM APPS.HR_ORGANIZATION_UNITS WHERE NAME LIKE '%_OU' AND NAME NOT LIKE '(DO NOT USE)%' ORDER BY NAME ASC;
// CHECK NUMBER OF RECORDS RETRIEVED
LET vCheck_Data = NoOfRows('D2B_Test01');
IF $(vCheck_Data) > 0 THEN
Store D2B_Test01 into $(vProjectQvdDir)D2B_Test01__OperatingUnitsQuery.qvd (qvd);
//Store D2B_Test01 into $(vProjectQvdDir)D2B_Test01__DummyQuery.qvd (qvd);
END IF
// CLEAR TABLE FROM MEMORY
Drop table D2B_Test01;
D2B_Test02:
SQL
SELECT 1 A, 2 B FROM Dual
UNION
SELECT 2 A, 1 B FROM Dual
UNION
SELECT 3 A, 3 B FROM Dual
;
// CHECK NUMBER OF RECORDS RETRIEVED
LET vCheck_Data = NoOfRows('D2B_Test02');
IF $(vCheck_Data) > 0 THEN
Store D2B_Test02 into $(vProjectQvdDir)D2B_Test02__DummyQuery.qvd (qvd);
END IF
// CLEAR TABLE FROM MEMORY
Drop table D2B_Test02;
// SET APPLICATION RUN END TIME
LET vEND = NOW();
// EXIT
EXIT SCRIPT;
In addition to this, I have validated that I am using the same TNSNAMES.ORA file in both environments (the VM and the native Windows 7 64-Bit OS of my laptop).
So, following your comments and these tests, I can now affirm there is some kind of weird communication issue between the QV 64-Bit client, hosted in Windows 7 64-Bit and using
Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64)) and my Oracle Database, which is hosted on a Linux 64-bit OS:
Sounds like a known issue? Could it be a problem between that specific Linux Version + Oracle DB and My configuration ?
Any further ideas will be appreciated.
Thanks again Thomas and everyone for your time and response. Cheers!