Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

19 Replies
Not applicable
Author

Try this:

WHERE

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

Anonymous
Not applicable
Author

i tried it, it didn't work

pljsoftware
Creator III
Creator III

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

Not applicable
Author


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.

rustyfishbones
Master II
Master II

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

MarcoWedel

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.

anbu1984
Master III
Master III

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

Anonymous
Not applicable
Author

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;

JonnyPoole
Employee
Employee

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

Capture.PNG.png

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)

Anonymous
Not applicable
Author

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