I have a table that eventually will hold bunchs of records.
Table dwBunch
field BranchNmbr as integer
FIELD FECHA AS DATETIME
field qty as decimal
field foo as char.... etc
INDEX ON FECHA (and some more index)
Table has index on date-time field. One process i'm developing, sumarizes the records by date. I need the last batch of records that day from every branch.. as that day may contain several batchs of records with same date (but time) for each branch warehouse, they are diferent from batch one hour earlier. So I came to this:
FOR EACH dwBunch
WHERE DATE(fecha) = ParameterDate:
....
END;.
I wonder, did I lost the advantage on the index to restrict the scan of all records, or otherwise database will bracket on other index and make some inefficient selection..
PS. I came to an algorithm I learned right here to preselect the records for every branch that are the newest according to datetime, and the query again the table, now knowing the datetime of interest.for each branch....
Yes - functions like this:
EACH TableName WHERE function(TableName.FieldName) = something
will do a table scan _except_ for functions like ROWID(TableName) and RECID(TableName).
This happens because there's no way for the db engine to do a straight match between the equation and what's in the table.
To do an equivalent-to-DATE matching on a DATETIME field and get the index bracketing, do this:
EACH TableName WHERE TableName.DateTime >= DateTime(dtVariable, 0) AND TableName.DateTime < DateTime(dtVariable + 1, 0)
I knew it....
And that bracketing tip is cool!!! I'll do as sugested. Thanks!!!!
pS. how do you format the code? (with number and stuff)
I knew it....
And that bracketing tip is cool!!! I'll do as sugested. Thanks!!!!
pS. how do you format the code? (with number and stuff)
Flag this post as spam/abuse.
do: thank = "you". a(). lot(). end.
Flag this post as spam/abuse.
do:
Tim:("Thanks").
Peter:("Tanks").
Brian:("Jajaja").
Neither:("Do I").
end.
i Sintax check: OK
Press scape and get to work