topic Problem in conditional SELECT? in QlikView

Problem in conditional SELECT?

Mon, 01 Aug 2011 22:53:05 GMT

Hello Everyone,

I hope it's a very basic questions, but as I recently started working on Qlikview, i don't know how to write the correct syntax for this. I've connected through a big data table, but I don't want to select the whole table as it has 11 million record so wanted to know how can we give the conditional select statement statement?

Oracle connection string;

SQL SELECT *

FROM table1

Where Snapshot_Date> '9/28/2010';

I added this where clause but I am getting an error which says :

ErrorSource: Microsoft OLE DB Provider for Oracle, ErrorMsg: ORA-01843: not a valid month

SQL SELECT *

FROM Table1

WHERE SNAPSHOT_DATE >= '09/8/2009'

the snapshot date is a field in table1.

Thanks. I will appreciate your replies.

Problem in conditional SELECT?

rwunderlich — Mon, 01 Aug 2011 23:02:31 GMT

I believe the correct date format for Oracle is:

WHERE SNAPSHOT_DATE >= DATE 'YYYY-MM-DD'

Problem in conditional SELECT?

Mon, 01 Aug 2011 23:07:33 GMT

Hi Rob,

thanks for replying. but the format is MM/DD/YYYY 5:23:06 AM...i.e there is an additonal time constraint in it.

Yaman

Re: Problem in conditional SELECT?

rwunderlich — Mon, 01 Aug 2011 23:59:24 GMT

Then use:

TO_DATE('09-08-2008 05:23:96 AM', 'MM-DD-YYY hh:mm:ss mi')

See http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm for precise syntax.

-Rob

Problem in conditional SELECT?

Tue, 02 Aug 2011 00:07:18 GMT

Thanks. so after that how should I write this in where clause? Is it something like:

SQL SELECT *

FROM SYSADM.DW_EMPLOYEES_VW

WHERE TO_DATE('SNAPSHOT_DATE', 'MM-DD-YYY hh:mm:ss mi') > '09-08-2008 05:23:96 AM';

I used the above statement and stillg ot the error. Sorry but i still didn't get it.

Re: Problem in conditional SELECT?

rwunderlich — Tue, 02 Aug 2011 00:23:30 GMT

WHERE SNAPSHOT_DATE >= TO_DATE('09-08-2008 05:23:96 AM', 'MM-DD-YYY hh:mm:ss mi')

-Rob

Problem in conditional SELECT?

Tue, 02 Aug 2011 00:28:18 GMT

thanks for replying.

SQL SELECT *

FROM table 1

WHERE SNAPSHOT_DATE >= TO_DATE('09/08/2009 05:23:09 AM', 'MM-DD-YYY hh:mm:ss mi');

I used the above code and got the folllowing error:

ErrorSource: Microsoft OLE DB Provider for Oracle, ErrorMsg: ORA-01810: format code appears twice

SQL SELECT *

FROM table1

WHERE SNAPSHOT_DATE >= TO_DATE('09/08/2009 05:23:09 AM', 'MM-DD-YYY hh:mm:ss mi')

Problem in conditional SELECT?

danielrozental — Tue, 02 Aug 2011 01:01:08 GMT

try this

SQL SELECT *

FROM table 1

WHERE SNAPSHOT_DATE >= TO_DATE('09/08/2009 05:23:09 AM', 'MM-DD-YYY HH:MI:SS PM');

Problem in conditional SELECT?

Tue, 02 Aug 2011 18:20:50 GMT

Hello,

It's still giving me the same error. Can you suggest something else?

Thanks.

Problem in conditional SELECT?

danielrozental — Tue, 02 Aug 2011 18:23:21 GMT

Just read about oracle's to_date funciton and keep testing until you get the right one.

http://www.techonthenet.com/oracle/functions/to_date.php

Problem in conditional SELECT?

Wed, 28 Mar 2012 17:56:03 GMT

try

SQL SELECT *

FROM Table1

WHERE SNAPSHOT_DATE >= to_date('09/aug/2009')

Problem in conditional SELECT?

Wed, 28 Mar 2012 18:16:01 GMT

try this

SQL SELECT *

FROM table 1

WHERE SNAPSHOT_DATE >= to_date('09/08/2009 05:23:09', 'mm-dd-yyyy hh:mi:ss');

Change 'MM-DD-YYY HH:MI:SS PM' to 'mm-dd-yyyy hh:mi:ss'