Request live query gives ORA-00904: invalid column name 
Author Message
 Request live query gives ORA-00904: invalid column name

When I try to activate a query (TwwQuery or TQuery) it fails when I
have set RequestLive=true and returns a ORA-00904: invalid column
name.
I have tried using SQL Monitor to find out what column it is trying to
access, but the query does not appeare there before I set
RequestLive=false and then
activate it.

The table has a unique index consisting of three columns: Date,
number(1,0) and number(2,0).
In the query's sql I have tried both to select * and to name all the
columns (there are ca. 100 columns and I need ca. 25 of them, but I
have experienced the same problem with other tables with fewer
columns). None of the columns are long (they are e.g.: number(1,0),
number, date, varchar2(50)). Can columns defined as number without
specified number of digits be a problem? Three non-key columns are
defined as Not Null in the database.

In the help for RequestLive it says:
Note:     All multi-table queries return read-only result sets. Other
conditions  may cause a query to return a read-only result set. (...)
 For other table types (such as InterBase, Oracle, and Sybase),
consult the documentation for the specific database system.

I do not see what part of the Oracle manuals that covers this topic.

When I executes the same sql (as I use in Delphi) as an "updatable
query" in SQL Navigator 3 it works fine, and I am able to
insert/update/delete data from the table.

BTW: The whole purpose of this is to include a "order by"-clause in
the query so the data appeare in the same order in delphi each time I
access the dataset - If there are other nice ways of handling this I
can probably use that. As I understand, setting the index on a TTable
will also sort it, but I do not want to use the same index (some
tables has other indexes in addition to the primary
key) for updating the row as I would like to sort by.  I think I
earlier also tried to put the query in the SQL-property of a TTable
instead of a TQuery, but got the same error as soon as I added a
"order by"-clause in the query...

I am using Delphi 5 Enterprise with Update pack 1 to access a Oracle
7.3.4. server through BDE (will migrate to 8.1.7 soon, but have not
been able to test it on a 8.1.7 yet and it HAS to work before we
migrate to Ora8)



Tue, 22 Jul 2003 20:18:25 GMT  
 Request live query gives ORA-00904: invalid column name

Quote:

>When I try to activate a query (TwwQuery or TQuery) it fails when I
>have set RequestLive=true and returns a ORA-00904: invalid column
>name.
><snip>

Roy,
I'm not sure about an Oracle table, but with a local DB [Paradox or
dBase], an 'Order By' clause will definitly make the query readonly.
Have you tried using RequestLive=false and then use UpdateSQL to post
your changes? It is more work to set up, but it will give you better
control over what happenes and better visibility of the process for
debugging.

HTH,
Dan
--
Dan Brennand
CMDC systems, inc.
Configuration Management and Document Control:
visit us at www.cmdcsystems.com



Wed, 23 Jul 2003 00:27:32 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Query Request Live Question

2. ORA-01410 invalid ROWID

3. ORA-1722 Invalid number when inserting a value

4. oracle 8: ORA-01410 invalid rowid

5. Request live...?

6. Live Request SQL

7. Error add records en TQuery request live Oracle 7.3

8. SQL request-live

9. DBGrid, sorting, Request live, Index files

10. Sybase and live request-Catch 22?

11. Request Live

12. TQuery Request Live and Oracle

 

 
Powered by phpBB® Forum Software