Oracle Count(*) query yielding different results in client vs. QV script

[evan_kurowski]

Greetings All,

I am working on a dynamically assembled query in an Oracle environment.  The following is an example of one of the iteration passes through the script as generated when running the .QVW.  This is cut and paste from the debugger window, the output window, traced.. 

This exact query verbatim will generate a row count when I test it in an Oracle client, however within the QlikView script the same exact syntax returns a rowcount of zero even though I can read data from the table into QlikView.

Any ideas on why I could have the differential with identical syntax?

SELECT Count(*) AS  COUNT

FROM INV.MTL_ITEM_SUB_INVENTORIES

WHERE ROWNUM > 0

AND INV.MTL_ITEM_SUB_INVENTORIES.LAST_UPDATE_DATE  BETWEEN ('01-JUN-2011') AND('01-SEP-2012');

[Anonymous]

Hello Qlik Community Members- this discussion has was posted previously but accidentally deleted we are recreating the thread. If you have any helpful answers please feel free to respond.

Michael Solomovich Sep 17, 2012 4:39 PM (in response to EvanKurowski)

Maybe different Oracle users with the different data access (?)

---------------------------------------------

Krunoslav Pap Sep 18, 2012 2:16 AM (in response to EvanKurowski)

Hi,

in addition to previous suggestion, date format could be possible cause of issue; try to execute select on Oracle and QV without dates condition and compare results.

regards

------------------------

EvanKurowski Sep 18, 2012 9:56 AM (in response to EvanKurowski)

Michael, to answer your question, this is producing the same row counts when run on the same machine.  So the user credentials executing the query in an Oracle client are the same to those executing the query in QlikView.

Krunoslav, the default date format variable at the beginning of the script remains unchanged

SET DateFormat='M/D/YYYY';

The proper row counts are returned when the date range filtration is applied on the QlikView side

Table:

LOAD * WHERE

LAST_UPDATE_DATE >= Num(Date#('01-JUN-2011','DD-MMM-YYYY'))  AND

LAST_UPDATE_DATE < Num(Date#('01-SEP-2012','DD-MMM-YYYY')) ;

SQL SELECT *

FROM INV.MTL_ITEM_SUB_INVENTORIES

//WHERE ROWNUM > 0 AND INV.MTL_ITEM_SUB_INVENTORIES.LAST_UPDATE_DATE

//BETWEEN ('01-//JUN-2011') AND ('01-SEP-2012')

However if the date filtration is engaged on the Oracle side, the query returns the correct amount of rows in an Oracle client, but either the full table when pulling the data, or a zero row count when counting the rows.

Table:

SQL SELECT *
FROM INV.MTL_ITEM_SUB_INVENTORIES
WHERE ROWNUM > 0 AND INV.MTL_ITEM_SUB_INVENTORIES.LAST_UPDATE_DATE
BETWEEN ('01-JUN-2011') AND ('01-SEP-2012')
(the above returns the full table in QV, but properly filtered date range in the client)

[maxgro]

I think is always better to specify a format for oracle date,

example in your sql

.......         between to_date('01-06-2011', 'DD-MM-YYYY') and ..............   

[maxgro]

in addition to your question (I was trying to simulate it) I found another strange thing

// first query, result 0 in qlikview, 1 with oracle tool (sqlplus, toad)

Table:

SQL

SELECT * FROM TEST.EMP

WHERE ROWNUM > 0 AND HIREDATE BETWEEN '01-GIU-2011' AND '01-FEB-2014'

;

// second query, result 1 in qlikview, 1 with oracle tool

SQL

SELECT * FROM TEST.EMP

WHERE ROWNUM > 0 AND HIREDATE BETWEEN to_date('01-06-2011', 'DD-MM-YYYY') AND to_date('01-02-2014', 'DD-MM-YYYY')

;

first and second in qlik

first in sql plus