topic Re: where clause in SQL statement in QlikView

where clause in SQL statement

Anonymous — Sat, 15 Nov 2014 00:51:13 GMT

Here is my load statement

LOAD eventid,

itemid,

soldon,

Year(soldon) as Year,

Month(soldon) as Month,

Day(soldon) as Day,

price;

SQL SELECT *

FROM advast

where soldon>'11/11/2014';

I get an error saying

SQL SELECT *

FROM advast

where soldon>'11/11/2014'

I'm not sure what is wrong with my script, without the WHERE clause it works fine.

Does the where clause just not work in a SQL statement in QV?

Re: where clause in SQL statement

JonnyPoole — Sat, 15 Nov 2014 01:37:36 GMT

It works, but the SQL syntax could be wrong. What data type is soldon in the database ? and what database is this ? (oracle, SQL Server etc...)

Re: where clause in SQL statement

jer_2011 — Sat, 15 Nov 2014 01:43:58 GMT

hi,

apparently this error is obtained.

already dealt with the connection, ODBC or OLE DB, you must apply before your code

I leave this link can help

http://community.qlik.com/message/607512#607512

Re: where clause in SQL statement

Sat, 15 Nov 2014 09:41:18 GMT

For most of the Sql based databases date has to be in this format '11-Nov-2014'.

Like this it should be, This is a SQL Query successfully executed on a Oracle Database.

Re: where clause in SQL statement

Anonymous — Mon, 17 Nov 2014 18:00:56 GMT

I have an OLE DB connection for Visual FoxPro

I tried to apply the code before the connection, not sure how it's supposed to work but I still get a script error

Re: where clause in SQL statement

marcus_malinow — Mon, 17 Nov 2014 18:07:16 GMT

You could try using the canonical date format 'YYYY-MM-DD', and see if this works.

It may be that your localisation settings are causing the date to be misinterpreted.

Re: where clause in SQL statement

Anonymous — Mon, 17 Nov 2014 18:08:06 GMT

Did anyone apply a successfully where clause on a sql statement in qlikview, does anyone know any documentation on that?

I tried applying a different where clause on a simple numeric field and this format keeps giving me an error

LOAD imach,

`trans_act`,

locationid,

reftrans;

SQL SELECT *

FROM todin

where locationid='22';

Re: where clause in SQL statement

marcus_malinow — Mon, 17 Nov 2014 18:10:25 GMT

Many of us regularly use where clauses in our QlikView SQL extracts.

In this instance it may be that there's an issue with string / numeric conversion. If your locationid field is numeric, try removing the single quotes from your where clause

Re: where clause in SQL statement

marcus_malinow — Mon, 17 Nov 2014 18:15:03 GMT

also, remove the single quotes from around 'trans_act'

Re: where clause in SQL statement

Anonymous — Mon, 17 Nov 2014 18:33:23 GMT

This worked for the numeric field, removing the single quotes, now I'm working on formatting the date field for my where clause to work

Re: where clause in SQL statement

Mon, 17 Nov 2014 18:41:58 GMT

Try this:

WHERE

date(soldon) > date(11/11/2014)

Re: where clause in SQL statement

Anonymous — Mon, 17 Nov 2014 19:01:12 GMT

i tried it, it didn't work

Re: where clause in SQL statement

pljsoftware — Tue, 18 Nov 2014 10:09:46 GMT

Hi Smaranda,

what is your DB?

If you run the script in debug you can see the exact query executed, you can copy and past this query in your SQL Tool and execute it.

You have to check the correct sintax for the query and try to create in the script a similar query.

Usually for the date in query I like to write date in format YYYY-MM-DD.

Regards

Luca Jonathan Panetta

PLJ Software

Re: where clause in SQL statement

Tue, 18 Nov 2014 15:26:04 GMT

ok. If I were you I would try to narrow down the issue.

1) Does this work?

WHERE date(soldon) = today() ;

2) On your load statement try both soldon, date(soldon) and see what date format you get without the where clause. This is to see how date() function converts soldon field. Is that a true date or string field?

3) Depending on the result of no. 2 above, try using similar date values in your WHERE clause and see what happens.

It should be that difficult but think something is missing as I tried the no. 1 step and worked for me. But of course, i'm using DB2 and SQL server databases.

Re: where clause in SQL statement

rustyfishbones — Tue, 18 Nov 2014 15:41:59 GMT

Try changing the Date around

LOAD eventid,

itemid,

soldon,

Year(soldon) as Year,

Month(soldon) as Month,

Day(soldon) as Day,

price;

SQL SELECT *

FROM advast

where soldon > '2014/11/11';

Re: where clause in SQL statement

MarcoWedel — Tue, 18 Nov 2014 15:57:05 GMT

LOAD eventid,

itemid,

soldon,

Year(soldon) as Year,

Month(soldon) as Month,

Day(soldon) as Day,

price

where soldon>'11/11/2014';

SQL SELECT *

FROM advast;

should work but could take a longer time to load.

Re: where clause in SQL statement

anbu1984 — Tue, 18 Nov 2014 17:08:23 GMT

Which database? Are you getting error while connecting to database? Or Are you not getting correct results?

Re: where clause in SQL statement

Anonymous — Tue, 18 Nov 2014 17:33:32 GMT

I tried a different ways of querying

soldon>'11/11/2014'

soldon>'2014/11/11'

date(soldon)>date(11/11/2014)

date(soldon)>today()

I confirmed the format is MM/DD/YYYY

the only way it worked so far was this

LOAD eventid,

itemid,

soldon,

Year(soldon) as Year,

Month(soldon) as Month,

Day(soldon) as Day,

price

where soldon>'11/11/2014';

SQL SELECT *

FROM advast;

Re: where clause in SQL statement

JonnyPoole — Tue, 18 Nov 2014 17:37:52 GMT

This is a guess without having a visual foxpro database to test, but this link provided some ideas....

Visual FoxPro ODBC Driver query fails if the date format is not the AMERICAN date format

You may also need to pass in the data as a variable

IE:

Set vDate='{07/22/93}';

And then...

Select * FROM ORDERS WHERE ORDER_DATE < $(vDate)

Re: where clause in SQL statement

Anonymous — Tue, 18 Nov 2014 17:42:02 GMT

This work but as you said it's slow and I'm worried it might slow down the database