Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Select on a timestamp |
Tue, Apr 9 2013 7:36 AM | Permanent Link |
Robert D. Smith | I'm VERY new to SQL, so please forgive if this is a stupid question.
I have a master table that has a Timestamp field. I have a detail table that also has a Timestamp field. I'd like to perform an SQL query when the master table scrolls a record. The general construction of the query would be: SELECT * FROM detail WHERE detail.Timestamp = master.Timestamp I'm just not sure how to actually write this (what format do I need to use so that it will work with the query)? Can I use something like detail.FieldByName('Timestamp').AsString = master.FieldByName('Timestamp').AsString? Or use QuotedStr with the above? Thanks! |
Tue, Apr 9 2013 7:53 AM | Permanent Link |
Robert D. Smith | Or should I use parameters?
Robert D. Smith wrote: I'm VERY new to SQL, so please forgive if this is a stupid question. I have a master table that has a Timestamp field. I have a detail table that also has a Timestamp field. I'd like to perform an SQL query when the master table scrolls a record. The general construction of the query would be: SELECT * FROM detail WHERE detail.Timestamp = master.Timestamp I'm just not sure how to actually write this (what format do I need to use so that it will work with the query)? Can I use something like detail.FieldByName('Timestamp').AsString = master.FieldByName('Timestamp').AsString? Or use QuotedStr with the above? Thanks! |
Tue, Apr 9 2013 8:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
Its not a stupid question but I think you could end up very disappointed with the result. From my old version manual <<Internally, TimeStamp fields are stored as a 64-bit floating-point number>> comparing floating point numbers is not a good idea Even if you're truncating the time to the nearest second you may find that they've been stored differently and compare differently. That said the procedure is quite simple. Set up a query and set the datasource to the master table. Again from my old manual <<If the SQL statement specified in the SQL property of the TDBISAMQuery component is a SELECT statement, the query is executed using the new field values each time the record pointer in the other data source is changed. It is not necessary to call the Open method of the TDBISAMQuery component each time. This makes using the DataSource property to dynamically filter a query result set useful for establishing master-detail relationships. Set the DataSource property in the detail query to the TDataSource component for the master data source.>> Naturally indices help speed Roy Lambert [Team Elevate] |
Tue, Apr 9 2013 8:30 AM | Permanent Link |
Robert D. Smith | Ok, I've set the DataSource in the Detail query to the DataSource of the Master Query. What is the SQL statement I need to include in the Detail query? I've tried 'SELECT * FROM [detailtable]', but that seems to pull everything still. I assume I still need to qualify it somehow?
Roy Lambert wrote: Robert Its not a stupid question but I think you could end up very disappointed with the result. From my old version manual <<Internally, TimeStamp fields are stored as a 64-bit floating-point number>> comparing floating point numbers is not a good idea Even if you're truncating the time to the nearest second you may find that they've been stored differently and compare differently. That said the procedure is quite simple. Set up a query and set the datasource to the master table. Again from my old manual <<If the SQL statement specified in the SQL property of the TDBISAMQuery component is a SELECT statement, the query is executed using the new field values each time the record pointer in the other data source is changed. It is not necessary to call the Open method of the TDBISAMQuery component each time. This makes using the DataSource property to dynamically filter a query result set useful for establishing master-detail relationships. Set the DataSource property in the detail query to the TDataSource component for the master data source.>> Naturally indices help speed Roy Lambert [Team Elevate] |
Tue, Apr 9 2013 8:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
<<I assume I still need to qualify it somehow?>> Yup - you still have to do some work Put into the query SELECT * FROM detail WHERE Timestamp = :Timestamp Make sure you set the database and the datasource and you should be good to go - apart from the fact that Timestamp may be a reserved word - I really can't remember so you might have to put square brackets round it. Essentially what Tim's done is save you the time and effort of closing the query, altering the parameter to the new value and opening the query. Its a nicer neater solution. Again I will emphasise look at the results very carefully. You are comparing floats and that is NOT a good idea. Roy Lambert [Team Elevate] |
Tue, Apr 9 2013 9:06 AM | Permanent Link |
Robert D. Smith | PERFECT! Thanks!
Roy Lambert wrote: Robert <<I assume I still need to qualify it somehow?>> Yup - you still have to do some work Put into the query SELECT * FROM detail WHERE Timestamp = :Timestamp Make sure you set the database and the datasource and you should be good to go - apart from the fact that Timestamp may be a reserved word - I really can't remember so you might have to put square brackets round it. Essentially what Tim's done is save you the time and effort of closing the query, altering the parameter to the new value and opening the query. Its a nicer neater solution. Again I will emphasise look at the results very carefully. You are comparing floats and that is NOT a good idea. Roy Lambert [Team Elevate] |
Tue, Apr 9 2013 9:12 AM | Permanent Link |
Robert D. Smith | I should also state that the name of my field was RecordTimestamp in both tables, so in case anyone reads this, I'm not sure if your field name was Timestamp if it would work or not.
Here is my finished SQL, for the record: SELECT * FROM OADHistory WHERE RecordTimestamp = :RecordTimestamp Robert D. Smith wrote: PERFECT! Thanks! Roy Lambert wrote: Robert <<I assume I still need to qualify it somehow?>> Yup - you still have to do some work Put into the query SELECT * FROM detail WHERE Timestamp = :Timestamp Make sure you set the database and the datasource and you should be good to go - apart from the fact that Timestamp may be a reserved word - I really can't remember so you might have to put square brackets round it. Essentially what Tim's done is save you the time and effort of closing the query, altering the parameter to the new value and opening the query. Its a nicer neater solution. Again I will emphasise look at the results very carefully. You are comparing floats and that is NOT a good idea. Roy Lambert [Team Elevate] |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |