posconsultant
asked on
How can I make a TQuery with a join editable?
Using Delphi 4, Oracle 8, BDE Woll2Woll components...
How can I make a TQuery with a join editable? I tried using lookups and calculated fields, the problem is that one of the tables will have over 3,000,000 records, so doing client side stuff like lookups and calc fields takes way, way too long. I've scoured the newsgroups and found a few clues how to do this (cached updates?) but I want to think this through before I begin, and I really do need a few details. Thanks for any help,
PC.
How can I make a TQuery with a join editable? I tried using lookups and calculated fields, the problem is that one of the tables will have over 3,000,000 records, so doing client side stuff like lookups and calc fields takes way, way too long. I've scoured the newsgroups and found a few clues how to do this (cached updates?) but I want to think this through before I begin, and I really do need a few details. Thanks for any help,
PC.
kretzschmar
why not use editable view on oracle side for the join ?
I have used a TQuery before set the RequestLive property to true.
It will be really picky about the sql when you do this. I had to use the query editor to create the sql and paste it into the query object how I wanted it.
It worked great though.
bozo
It will be really picky about the sql when you do this. I had to use the query editor to create the sql and paste it into the query object how I wanted it.
It worked great though.
bozo
Have you thought of using parameters or of creating the sql you want to on the fly??
eg: with a tquery you can write the following in the sql editor:
select * from clients where custid = :ID order by lastdate
Then you set the datatype of the parameter in the parameters section. You can at this point also link the query to a previous query, and create a link on the field name of the linked/parent dataset. The parameter name must be the same as a field name for this to work. I have found that it works wonderfully for reports.
Anyway, when you want to make dynamic changes to the statement within the defined parameter above, you just modify the parameter value to change your criteria. You can also edit the sql by using the sql property of the tquery, but this will require that the query be closed and then opened.
Another option is to filter your data or use selectrange on the current query to narrow your search down afterwards, but this defeats the purpose of using SQL and queries.
eg: with a tquery you can write the following in the sql editor:
select * from clients where custid = :ID order by lastdate
Then you set the datatype of the parameter in the parameters section. You can at this point also link the query to a previous query, and create a link on the field name of the linked/parent dataset. The parameter name must be the same as a field name for this to work. I have found that it works wonderfully for reports.
Anyway, when you want to make dynamic changes to the statement within the defined parameter above, you just modify the parameter value to change your criteria. You can also edit the sql by using the sql property of the tquery, but this will require that the query be closed and then opened.
Another option is to filter your data or use selectrange on the current query to narrow your search down afterwards, but this defeats the purpose of using SQL and queries.
Have you thought of using parameters or of creating the sql you want to on the fly??
eg: with a tquery you can write the following in the sql editor:
select * from clients where custid = :ID order by lastdate
Then you set the datatype of the parameter in the parameters section. You can at this point also link the query to a previous query, and create a link on the field name of the linked/parent dataset. The parameter name must be the same as a field name for this to work. I have found that it works wonderfully for reports.
Anyway, when you want to make dynamic changes to the statement within the defined parameter above, you just modify the parameter value to change your criteria. You can also edit the sql by using the sql property of the tquery, but this will require that the query be closed and then opened.
Another option is to filter your data or use selectrange on the current query to narrow your search down afterwards, but this defeats the purpose of using SQL and queries.
eg: with a tquery you can write the following in the sql editor:
select * from clients where custid = :ID order by lastdate
Then you set the datatype of the parameter in the parameters section. You can at this point also link the query to a previous query, and create a link on the field name of the linked/parent dataset. The parameter name must be the same as a field name for this to work. I have found that it works wonderfully for reports.
Anyway, when you want to make dynamic changes to the statement within the defined parameter above, you just modify the parameter value to change your criteria. You can also edit the sql by using the sql property of the tquery, but this will require that the query be closed and then opened.
Another option is to filter your data or use selectrange on the current query to narrow your search down afterwards, but this defeats the purpose of using SQL and queries.
Sorry about the double submission, I received an error from our firewall here and was not sure if the comment had been posted.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is that your final answer?...:-) Thanks for posting code sample, I really appreciate this.