Page 1 of 1

SQL Explorer

PostPosted: Fri Jul 22, 2016 11:00 am
by realdata
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!!

Re: SQL Explorer

PostPosted: Fri Jul 22, 2016 3:20 pm
by John Sindelar
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

Re: SQL Explorer

PostPosted: Sat Jul 23, 2016 12:55 pm
by realdata
Thank You Sir!

Re: SQL Explorer

PostPosted: Tue Jul 26, 2016 7:29 am
by realdata
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!

Re: SQL Explorer

PostPosted: Tue Jul 26, 2016 11:02 am
by DanW
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,

Re: SQL Explorer

PostPosted: Wed Jul 27, 2016 5:29 am
by realdata
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.

Re: SQL Explorer

PostPosted: Wed Jul 27, 2016 11:15 am
by DanW
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!