SQL Explorer

General support questions.
Posts: 4
Joined: Thu Jul 21, 2016 4:28 pm
PostPosted: Fri Jul 22, 2016 11:00 am
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!!
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Fri Jul 22, 2016 3:20 pm
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
John Sindelar
SeedCode
Posts: 4
Joined: Thu Jul 21, 2016 4:28 pm
PostPosted: Sat Jul 23, 2016 12:55 pm
Thank You Sir!
Posts: 4
Joined: Thu Jul 21, 2016 4:28 pm
PostPosted: Tue Jul 26, 2016 7:29 am
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!
SeedCode Staff
SeedCode Staff
Posts: 190
Joined: Tue Sep 22, 2015 2:27 pm
PostPosted: Tue Jul 26, 2016 11:02 am
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,
Posts: 4
Joined: Thu Jul 21, 2016 4:28 pm
PostPosted: Wed Jul 27, 2016 5:29 am
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.
SeedCode Staff
SeedCode Staff
Posts: 190
Joined: Tue Sep 22, 2015 2:27 pm
PostPosted: Wed Jul 27, 2016 11:15 am
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!

Return to General Support

Who is online

Users browsing this forum: No registered users and 4 guests

(855) SEEDCODE
[email protected]
Follow us: