Devart Forums

Posted: **Fri 07 Mar 2014 15:20**

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

Posted: **Tue 11 Mar 2014 08:34**

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
)

Posted: **Mon 17 Mar 2014 09:19**

Hello,

The big problem it is that query like :

Code: Select all

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

Return read only result !

Posted: **Wed 26 Mar 2014 11:30**

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.

Devart Forums

Addwhere and Union

Addwhere and Union

Re: Addwhere and Union

Re: Addwhere and Union

Re: Addwhere and Union