topic Re: How to give where condition on date in SQL in QlikView

How to give where condition on date in SQL

Sat, 16 Mar 2013 05:44:29 GMT

Hi,

I am loading the data from a VIEW which is created in SQL but WHERE condition is not working.

SQL

SELECT * FROM NEWC."VIW_MONTHLY" WHERE CALLDATE>='01/01/2013 00:00:00';

condition is not working im getting all 2010 data also. Kindly help me...

Re: How to give where condition on date in SQL

kaushiknsolanki — Sat, 16 Mar 2013 06:03:18 GMT

Try this.

SQL

Select * from NWEC."VIW_MONTHLY" where CALLDATE >= '01-Jan-2013';

Regards,

Kaushik Solanki

Re: How to give where condition on date in SQL

Sat, 16 Mar 2013 07:45:54 GMT

No it is not working it pulling all 2010 records also. this field contains Date & Time also.

Re: How to give where condition on date in SQL

udit_kumar_sana — Sat, 16 Mar 2013 08:38:43 GMT

Hi ,

Try to do this in QVW i.e after making Qvd of this query ,fetch this qvd in new qvw and apply this where condition w.r.t to date format that is fetched from qvd.

Regards,

Udit

Re: How to give where condition on date in SQL

Sat, 16 Mar 2013 08:42:24 GMT

But whole data cant be fetch because the VIEW dont have WHERE clause so it will pull everything from database and database contains records from 2010. for one month only data will be 80-90k records.

Re: How to give where condition on date in SQL

udit_kumar_sana — Sat, 16 Mar 2013 08:49:33 GMT

Hi,

Then fetched the whole data for one time and then put it to incremental load.

Regards,

Udit

Re: How to give where condition on date in SQL

Sat, 16 Mar 2013 08:51:35 GMT

This has to done in daily basis so it is not possible

Re: How to give where condition on date in SQL

kaushiknsolanki — Sat, 16 Mar 2013 08:54:49 GMT

Dear Rashmi,

Try this.

Select * from NWEC."VIW_MONTHLY" where to_date(CALLDATE,'DD-Mon-YYYY') >= '01-Jan-2013';

Regards,

Kaushik Solanki

Re: How to give where condition on date in SQL

Sat, 16 Mar 2013 08:59:30 GMT

It is giving for this query...

ErrorSource: OraOLEDB, ErrorMsg: ORA-01843: not a valid month

Re: How to give where condition on date in SQL

kaushiknsolanki — Sat, 16 Mar 2013 09:02:34 GMT

Try this.

Select * from NWEC."VIW_MONTHLY" where CALLDATE >= to_date('01-Jan-2013' ,'DD-MON-YYYY')

Regards,

Kaushik Solanki

Re: How to give where condition on date in SQL

Sat, 16 Mar 2013 09:05:59 GMT

again error:

ErrorSource: OraOLEDB, ErrorMsg: ORA-01861: literal does not match format string

Re: How to give where condition on date in SQL

kaushiknsolanki — Sat, 16 Mar 2013 09:08:11 GMT

Dear Rashmi,

What is the version of your Oracle?

Regards,

Kaushik Solanki

Re: How to give where condition on date in SQL

Sat, 16 Mar 2013 09:11:16 GMT

Version 11

Re: How to give where condition on date in SQL

Sat, 16 Mar 2013 09:16:06 GMT

Version 11

Re: How to give where condition on date in SQL

kaushiknsolanki — Sat, 16 Mar 2013 09:18:04 GMT

Try this.

Select * from NWEC."VIW_MONTHLY" where CALLDATE >= to_date('01-JAN-2013 12:00:00','DD-MON-RRRR HH24:MI:SS')

Regards,

Kaushik Solanki