Addwhere and Union

[Fabrice]

Hello,

Addwhere method (TibcQuery) is really usefull but it doesn't work with "Union" clause.
Any solution ?

Easy to reproduce with a query like :

Code: Select all

select *
from customer qc
union
select *
from customercopy qc2

If you do addwhere('(0=0)');

Result is :

Code: Select all

select *
from customer qc
where (0=0)
union
select *
from customercopy qc2

Of course same problem with deletewhere...

Best regards,
Fabrice

[ZEuS]

The AddWhere method inserts the WHERE statement into the first found SELECT clause. So, when you need to apply AddWhere to the whole UNION statement, you should rewrite your query like the following:

Code: Select all

select * from (
  select *
  from customer qc
  union
  select *
  from customercopy qc2
)

[Fabrice]

Hello,

The big problem it is that query like :

Code: Select all

select * from (
  select *
  ...
 union
 select *
)

Return read only result !

[ZEuS]

You can avoid this problem by providing your own SQL statements in the SQLInsert, SQLUpdate, SQLDelete, etc. properties. For example:

Code: Select all

IBCQuery1.SQL.Text := 'select * from (select * from customer qc union select * from customercopy qc2)';
IBCQuery1.SQLInsert.Text := 'insert into customer(ID, CNAME) values(:ID, :CNAME)';
IBCQuery1.SQLUpdate.Text := 'update customer set ID = :ID, CNAME = :CNAME where ID = :Old_ID';

For more information, please refer to the IBDAC documentation.