0 lines fetched in QlikView but 55 in any other application

[#1]

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;

[flipside]

Hi Francisco,

My thoughts were that the WHERE clause may be being evaluated incorrectly by Qlikview or more likely the ODBC and hence resulting in an empty result set.  If removing the where clause from within the Qlikview load script then returns SOME data, you can identify it's the filter causing the problem.

Although it sounds like an ODBC 64-bit bug, you may have ways to code it differently to get round it.

flipside

[tombombadil]

Hi,

we have no problem using x64 with the Oracle 11g R2 Client.

Just to make sure. You have to install the x64 for Qlikview and Oracle Client. Also you have to configure the x64 ODBC. Otherwise it won't work.

I'm not at work anymore, so I cant test it. But there is the CONNECT32 which uses the x86 environment to retrieve data. Maybe you give it a try first. It works with OLEDB, but I dont know if it works with ODBC too.

Otherwise, I'm back at office on monday where I can test some stuff with the Oracle Client.

I hope you can fix it!

Best regards,

Tom

[#4]

Hi flipside,

My apologies if I was not clear. As per your suggestion, I also removed the WHERE...... section, but the issue remains there, so I would say the issue is not because of the filter.

So, I agree with your second statement: This looks like a 64-bit bug.

Thanks for your time and help! Regards!!

[#5]

Hi there Thomas,

I reinstalled everything but the issue remains there.

At this point I am curious about a phrase from your last post: "Also you have to configure the x64 ODBC. Otherwise it won't work." What exactly do you mean by "configure the x64 ODBC"?

Thanks in advance for any help and regards from Chile!

[tombombadil]

Hi Francisco,

on Windows x64 are two types of ODBC running, x64 and x86.

Both run independent from each other.

ODBC x86 configuration:

%SYSTEMROOT%\SysWOW64\odbcad32.exe

ODBC x64 configuration:

%SYSTEMROOT%\System32\odbcad32.exe

Processes can only run as x86 or x64. One process cannot run in both modes.

Thats why Qlikview introduced the CONNECT32 command which calls a x86 process to retrieve the data.

Be sure that your Oracle configuration is working under the x64 configuration.

It's always a pain in the ass to get the Oracle driver running. Our IT department does this part now, because they know exactly which version they need to install.

Hope the ODBC hint will help you!

Best regards,

Tom