Need to restrict data based on date in SQL Statement

[#1]

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.       

[#2]

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

[Jason_Michaelides · Accepted solution]

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

[Anonymous]

See this post about some aspects of Oracle, hope it helps:

http://community.qlik.com/docs/DOC-1321

[#5]

Thanks Jason, It worked perfect. 

But using variable causes any performance issues?

[Jason_Michaelides]

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!