Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

0 lines fetched in QlikView but 55 in any other application

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:

02.jpg

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:

03.jpg

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;

Tags (3)
14 Replies
Highlighted
Not applicable

Re: 0 lines fetched in QlikView but 55 in any other application

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: 0 lines fetched in QlikView but 55 in any other application

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?


talk is cheap, supply exceeds demand
Highlighted
Valued Contributor II

Re: 0 lines fetched in QlikView but 55 in any other application

Do you get any rows back without the WHERE clause?

Highlighted
Contributor

Re: 0 lines fetched in QlikView but 55 in any other application

why where 1=1 ??

to unoptimize qvd load ??

Highlighted
New Contributor III

Re: 0 lines fetched in QlikView but 55 in any other application

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

Highlighted
Not applicable

Re: 0 lines fetched in QlikView but 55 in any other application

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!

Highlighted
Not applicable

Re: 0 lines fetched in QlikView but 55 in any other application

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!

Highlighted
Not applicable

Re: 0 lines fetched in QlikView but 55 in any other application

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.

Highlighted
Not applicable

Re: 0 lines fetched in QlikView but 55 in any other application

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:

Day2_01.jpg

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:

Day2_02.jpg

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!