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!

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!

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]

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]

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]

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]

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]