topic Re: Load with a where clause in QlikView

Load with a where clause

Anonymous — Tue, 12 Mar 2013 15:59:35 GMT

Hi,

The syntax gives an error below

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC OPENEDGE driver][OPENEDGE]Syntax error in SQL statement at or about "DATE(trndat,'dd/MM/yyyy')>= '2010/01/01'" (10713)

SQL SELECT *

FROM PUB.uwm100

/////////////////////////////////////////////////////////////////////////////

load

DATE(trndat,'dd/MM/yyyy') as %DATE;

SQL SELECT *

FROM PUB.uwm100

DATE(trndat,'dd/MM/yyyy')>= '2010/01/01';

Re: Load with a where clause

alexandros17 — Tue, 12 Mar 2013 16:02:34 GMT

Probably it is

DATE(trndat,'dd/MM/yyyy')>= '01/01/2010';

Re: Load with a where clause

Anonymous — Tue, 12 Mar 2013 16:25:25 GMT

change the format but the error still persist

Re: Load with a where clause

Tue, 12 Mar 2013 16:51:34 GMT

Hi,

Try with this

LOAD

DATE(trndat,'dd/MM/yyyy') as %DATE

WHERE trndat >= MakeDate(2010, 1, 1);

SQL SELECT *

FROM PUB.uwm100;

Re: Load with a where clause

Tue, 12 Mar 2013 17:00:56 GMT

Its probably because '2010/01/01' is not treated as date in your database (is it SQL Server/Sybase ?)

You can probably modify your where clause as;

WHERE datediff('2010-01-01', date(trndat,'dd/MM/yyyy')) >= 0

Regards

Vikas

Re: Load with a where clause

Anonymous — Tue, 12 Mar 2013 17:06:16 GMT

Hi Guys,

Finally resloved it.

load

DATE(trndat,'dd/MM/yyyy') as %DATE;

SQL SELECT *

FROM PUB.uwm100

WHERE trndat >= ' 31/12/2010;