when i execute same query in back end (sql tool) i am getting 1 million record
which makes it a very weird issue because the execution is done in both cases by the database engine. Could it be permission-related? Seems improbable...
Sriram, try rewriting or removing parts of the WHERE condition in your SELECT statement. For example, it could be that the database doesn't understand the between date values anymore in your QlikView script context. Can you rewrite them as default database dates? Like
WHERE trunc(document_date) between '2013-04-01' and '2014-03-31'
Maybe you can try comparing the values in your query ('01-APR-2013') with the result of the trunc(document_date) and see if their format matches. I dont see any other possible issue in your query, also company 'TEST' should exists.
Please let us know if you need anything else or if you found a solution for your issue.
Thanks for your quick reply , Kindly check my attached image . i extracted qvd without date filter and placed document date column in table box , where i am getting record for the year 2013 and 2014 .when i add date filter its not fetching any record . i tried with load statement as per settu_periasamy still getting same issue .
Because you cannot use between in QlikView. QlikView doesn't support between function. Plus, I won't even recommend using the where statement in the Load statement. I would filter out things in your sql statement so that the query has to bring less number of records to QlikView.
I would try to check if the query runs in SQL and try to resolve the issue there and then try to run with the fixed SQL query