If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information:
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply).
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information:
I am switching my database over to pass through queries instead of the slower datasheet method I had been using. It runs quite fast even in unoptimized mode when I filter the dates for the past few days. I only need the records from at most the past 10 days. Is there an SQL line I can write that would give me the records for [Today-10] instead of using Txndate >= {d '2006-04-15'}. I don't want to have to keep on changing that date every few days. Thanks for all your help!
In QODBC, the function CURDATE() – Returns the current computer system date as a date value.
For example, for Today, April 18, 2006, when the following query :-
SELECT {fn CURDATE()} as Today, ({fn CURDATE()}-30) as "30 Days Ago", TxnDate, RefNumber, InvoiceLineDesc FROM invoiceline WHERE TxnDate >= ({fn CURDATE()}-30)
Ok, I tried that, and it looks like it's trying to load every record instead of limiting it to that date range. Here is my SQL: select {fn CURDATE()} as Today, ({fn CURDATE()}-15) as "15 Days Ago", ShipDate, CustomerRefFullName, ShipAddressCity, PONumber, InvoiceLineQuantity, InvoiceLineItemRefFullName, InvoiceLineDesc, TemplateRefFullName, RefNumber, IsPending from invoiceLine unoptimized WHERE Txndate >= ({fn CURDATE()}-15)
Anything I can change to make it run as fast as it does when the SQL is like Txndate >= {d '2006-04-25'} ?
Ok, I tried that, and it still doesn't work. I have the optimizer status window enabled and it's trying to go through all 23,000 records in the invoiceline table instead of the 300 I need for Today-15. Again, my goal is to be able to access the records for the past 15 days as fast as possible. It works really well when I do something like Txndate >= {d '2006-04-25'}. But, I would need to change the date every few days to keep the speed fast. I need to always be able to have the past 15 days available.
When I use this SQL, it wants to run all 23,000 records:
select {fn CURDATE()} as Today, ({fn CURDATE()}-15) as "15 Days Ago", ShipDate, CustomerRefFullName, ShipAddressCity, PONumber, InvoiceLineQuantity, InvoiceLineItemRefFullName, InvoiceLineDesc, TemplateRefFullName, RefNumber, IsPending from invoiceLine unoptimized WHERE TxnDateMacro='Today-15'
This is normal bahaviour, a literal is faster as it can use a QODBC jump-in, while an evaluated value needs to be tested against each record. What should be actually done is to generate the SQL statement via a program with the dates required as literals.
In Access you would create an Unbound Form and use the On Load event procedure to call the function uf_DisplayRecord that would contain the VBA of a select statement that would generate the date value literals based on the system date - 15.
Books like the Access 2003 Bible found on Amazon outline the procedure.
