Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
smaranda_fetila
New Contributor II

where clause in SQL statement

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?

Tags (1)
1 Solution

Accepted Solutions

Re: where clause in SQL statement

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.

19 Replies
Employee
Employee

Re: where clause in SQL statement

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...)

jer_2011
Contributor II

Re: where clause in SQL statement

hi,  

apparently this error is obtained.  

ejemplo_14112014.png

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

ejemplo_141120142.png

I leave this link can help

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

Not applicable

Re: where clause in SQL statement

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.

Capture.PNG.png

smaranda_fetila
New Contributor II

Re: where clause in SQL statement

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

marcus_malinow
Valued Contributor III

Re: where clause in SQL statement

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.

smaranda_fetila
New Contributor II

Re: where clause in SQL statement

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';

marcus_malinow
Valued Contributor III

Re: where clause in SQL statement

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

marcus_malinow
Valued Contributor III

Re: where clause in SQL statement

also, remove the single quotes from around 'trans_act'

smaranda_fetila
New Contributor II

Re: where clause in SQL statement

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

Community Browser