I LOVE SQL EXPLORER!!

Thanks so much for such a great resource.

So I hooked up one of my tabkles that had over a million records.

These are Products for 3 different companies. So I did a very simple query:

Code: Select all

SELECT a."Description", a."CatOne", a."CatTwo"
FROM "Products" a
WHERE a."company" = ?
ORDER BY a."Description" ASC

So I did a query of Company A which has 40,000 records and it gave me no results. Although it looked like it was trying. Tried Company B which had 500,000 and same thing. So I typed Company Test in 1 record and found no problem. I also took Company A and put in only 1500 of the records and it worked. I PROMISE I USED SAME EXACT VALUES SPELLED IDENTICALLY!!! Any idea why I am not getting results on this large data set? My Company field is Text....Thanks so much!!

This doesn't ring any bells for me, Michael. I could tell more if I could get a copy of your files but the sound kind of big. Email me at support at seedcode.com if you'd like to send them over and we'll figure something out.

Best,

John

Thank You Sir!

OK..date ranges...
This works with date =

Code: Select all

SELECT a."Receiving Desc", a."DateRec", a."_cu.timestamp_created"
FROM "Receiving" a
WHERE a."DateRec" = ?
ORDER BY a."_cu.timestamp_created" DESC

When I try > or < or with same date it fails..
also range fails using

Code: Select all

WHERE a."DateRec" BETWEEN #07/04/2016# AND #07/25/2016#

how would you set up date range or > than certain date?

Thanks!

Hi Michael,

I've been able to set up and test < and > queries on date fields using SQLExplorer's sample Tasks table. Are you able to get a query like this working with the sample table as well? To confirm, is your DateRec field formatted as a "Date" type field?

Thank you,

yes sir it is...

What about range?

So I exported out July into xlsx...

Created a fresh FM file with just that data. Same thing..

However I did notice this on both files:
= always works
> works if date is 07/20/2016 or greater
>= does not work even with 07/20/2016

Ok I just tried with another file; and had no problem. So something is wrong with my file. HOWEVER; I can't explain why if I export out to xlsx and create a new file from that data; why that doesn't work either. The original file was converted from 11. But like I say; shouldn't starting with data from xlsx work? Even though it was exported from bad file.

Michael,

I wish I had an answer regarding the data in your file. I might try an export of just a small subset of your data, even one record, and see if you can get that to work.

As far as date range, SQLExplorer doesn't offer the BETWEEN operator as an option. However, you *can* add your DateRec field twice under tab #4, and then have two separate rules applied, one using ">" and the other using "<". That would allow you to constrain results between two dates.

Hope that helps!