Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I need help in restricting data based on date in sql itself. We are using Oracle database. When using the following script, its not restricting data.
load *;
sql select
distinct cust_id,
min(event_time)
from Table1
where
Cust_type = 50
and to_char(event_time, 'yyyy') >= 2011
group by cust_id;
even though my sql has restriction to fetch data after greater than or equal to 2011, still qlikview gives me data before 2011 also.
When i use the same sql statement in oracle, it works perfect.
How weird...I've never used Oracle but if I was trying to achieve the same in SQL that would be fine (although it would be a different conversion of event_time to yyyy)
Maybe try using a varibale (I'm guessing here - maybe QV isn't passing the clause to Oracle properly).
LET vWHEREClause = 'to_char(event_time, ' & Chr(39) & 'yyyy' & Chr(39) & ') >= 2011';
load *;
sql select
distinct cust_id,
min(event_time)
from Table1
where
Cust_type = 50
and $(vWHEREClause)
group by cust_id;
Like I say, I'm guessing - but stranger things have happened...
Hope it helps,
Jason
Hi Neelima,
Two observations:
1. Performance – I will put function on variable rather than the date field, so instead of using
a. To_char(event_time), I will try to_date('2011',’YYYY’)
2. Second, I am bit surprised, that this code is not working as it is working on sql, can you please trying putting ‘2011’ in quotes (I have not tested it)
Regards
How weird...I've never used Oracle but if I was trying to achieve the same in SQL that would be fine (although it would be a different conversion of event_time to yyyy)
Maybe try using a varibale (I'm guessing here - maybe QV isn't passing the clause to Oracle properly).
LET vWHEREClause = 'to_char(event_time, ' & Chr(39) & 'yyyy' & Chr(39) & ') >= 2011';
load *;
sql select
distinct cust_id,
min(event_time)
from Table1
where
Cust_type = 50
and $(vWHEREClause)
group by cust_id;
Like I say, I'm guessing - but stranger things have happened...
Hope it helps,
Jason
See this post about some aspects of Oracle, hope it helps:
Thanks Jason, It worked perfect.
But using variable causes any performance issues?
Shouldn't do. Using the WHERE clause in the SQL script pushes the load onto the database server which is normally a good thing. And anyway, using the variable shouldn't really make any difference in this case but you get to know QV's little foibles after a while...like I say - your script should work!