I suspect I’m misunderstanding something about SQL queries. The following code sometimes presents an incomplete set of qualifying records.
While trying to figure out what’s going on, I’ve done some manual searches, some manual sorts of columns. This action alone seems to shake loose the SQL query to find all qualifying records. In other words, I’ve not changed either the SQL query code or the data. I’m using Sybase ASA.
I’m asking because I’m seeing this also in various other similar SQL queries. What can account for this?
var vMaxReturnedRows = 100000;
globals.gsevid = 2; // test
vSeven = globals.gsevid;
var vActiveDays = gsevid$to_sev.active_days;
var vToday = new Date();
vToday.setDate(vToday.getDate() - vActiveDays); // reduce today to the threshold date
var vQuery = "SELECT * FROM people WHERE ";
vQuery += "people.raw = '' AND people.peo_sev_id = ? AND people.people_id IN ";
vQuery += "(SELECT cr.peoid FROM cr GROUP BY cr.peoid HAVING max(cr.creation_date) >= ? ) ";
var vDataset = databaseManager.getDataSetByQuery(controller.getServerName(),vQuery,[vSeven, vToday],vMaxReturnedRows);
controller.loadRecords(vDataset);
var vSize = foundset.getSize();
You cannot just subtract days from a date. You need to convert the date to milliseconds - and then subtract the number of milliseconds in a day - then turn the result back into a date object.
Bob, this is legitimate code, if that’s what you were referring to.
vToday.setDate(vToday.getDate() - vActiveDays); // reduce today to the threshold date
The following code sometimes presents an incomplete set of qualifying records.
Morley, can you give us some more context?
No idea what is sometimes missing, based on the code you’re showing us.
One little thing
people.raw = ''
…will not give you back nulls !
Unless you are prefilling this column with an empty string on record creation, you’re heading for trouble here.
this search would cover all:
this search would cover all:
Code:
(people.raw = '' OR people.raw is null)
This will not work on all databases. Oracle for example doesn’t like “people.raw = ‘’”, because ‘’ is NULL in Oracle. So actually it is not so eays to properly search for “empties” with one statement on all databases.
We’re running this code within our batch processor. We’ve set up a solution which runs once a day through the headless client (very nice) to massage the data.
Every client record has a “raw” text field (badly named but we’re stuck with it since field names can’t be changed). The field has three states – No Call Reports, indicating there are no related records in the Call Reports table; Inactive, indicating there are related call reports but they’re all older than the user-defined “active” period, i.e., they’re all old; and Active, there are call reports and they fall within the active period.
Once a day all records need to be re-assessed. If the “raw” field is empty and there are related call reports and the newest among them falls before the active threshold date, then set this set of records to read “Inactive”. And so on through each of the permutations.
We’re using Sybase’s ASA database and no one else has access to it but us. So Sybase-centric coding is fine.
Recently I discovered some data anomolies, so I went into the batch processing solution to test and tinker. It was then I discovered I could run the SQL query and return some but not all qualifying records. Which prompted me to visually check the data, to see if that “raw” field really was empty, to check whether there really were related call report records, to check the date of the most recent CR and compare it against the active threshold date.
In other words, I changed nothing, but I did manually search and sort. After doing so I ran the unchanged SQL query and found it now found all of the qualifying records. I proceeded to check various iterations of the SQL queries and found the same phenomena – sometimes finding all the qualifying records and sometimes not, but then would do a complete find once I’d sorted columns and/or placed a cursor into a field to see if it really was empty.
badly named but we’re stuck with it since field names can’t be changed
Just a remark: of course you can change column names. You just can’t do from inside Servoy. Use SQL or your database frontend to do that and you are fine. Of course, you will have to update several things inside your solution, if the field name is used somewhere.
Every client record has a “raw” text field (badly named but we’re stuck with it since field names can’t be changed). The field has three states – No Call Reports, indicating there are no related records in the Call Reports table; Inactive, indicating there are related call reports but they’re all older than the user-defined “active” period, i.e., they’re all old; and Active, there are call reports and they fall within the active period.
Once a day all records need to be re-assessed. If the “raw” field is empty and there are related call reports and the newest among them falls before the active threshold date, then set this set of records to read “Inactive”. And so on through each of the permutations.
Morley, total different question, why are you using a seperate (headless) client for that? You can do that perfectly with calculations, if you ask me!
HJK:
Morley, total different question, why are you using a seperate (headless) client for that? You can do that perfectly with calculations, if you ask me!
Yes, probably could be done by calcs. But a quick SQL query and field update seems simpler.
This solution will be serving multiple independent clients who may be in very different time zones. I want this daily update of data to trigger in the middle of the night in each time zone, processing only clients located in that zone. There are several other routines within the same solution. All clients who were scheduled to be called today, and weren’t, are rolled over to the next day.