Subject Re: [IBO] TIBOQuery.SQL was TIBOTable.SQL
Author Helen Borrie
At 11:06 AM 11-10-01 +0000, you wrote:
>I have the simple query(s)
>update salenum
>set customernum = 1 + (select customernum
> from salenum);
>select customernum
>from salenum
>This work 100% in the interbase interactive sql.
>It does not work in TIBOTable.SQL. (**read TIBOQuery.SQL**) I assume this is because I cant
>have 2 SQL statements per tIBOTable. ** TIBOQuery **

You can't have two statements per query. A query encapsulates one and only one statement. A "usual" TIBOQuery would be a SELECT statement returning a dataset consisting of a number of columns X a number of rows. Any row that the user edits would be updated by a statement automatically generated by IBO. Likewise, any inserted row would be set up for you by IBO; and deletions too.

However, if you need _custom_ SQL for your updates, inserts and/or deletes, you can plug special statements into the respective UpdateSQL properties. Look at InsertSQL, EditSQL and DeleteSQL. These properties need valid __executable__ SQL statements (i.e. not SELECT statements) and the replace the respective automatic statements that IBO creates when you call Insert, Edit or Delete.

This statement:
update salenum
set customernum = 1 + (select customernum
from salenum);

is not valid SQL. A sub-select is only permitted to return a single row. The statement in that subselect would return a row for each row in salenum.

select customernum
from salenum

I have no idea why you need this statement. Could you talk more about what you want to achieve?


>Is this true, or is there someting I must set ?
>Your use of Yahoo! Groups is subject to

All for Open and Open for All
InterBase Developer Initiative ยท