Update Failed On Complex Query

[iwancs]

Hi, i've installed latest Zeos 6.6-TRUNK from CVS.
I have a query that join 2 or more tables like this:

SELECT
IVDATGRP_LVL0.ID_IVDATGRP_LVL0 ID_KELOMPOK,
IVDATGRP_LVL0.KODE KODE_KELOMPOK,
IVDATGRP_LVL0.NAMA NAMA_KELOMPOK,
IVDATGRP_LVL0.KETERANGAN KETERANGAN_KELOMPOK,
IVDATGRP_LVL1.*
FROM IVDATGRP_LVL1
LEFT JOIN IVDATGRP_LVL0 ON IVDATGRP_LVL0.ID_IVDATGRP_LVL0 = IVDATGRP_LVL1.ID_IVDATGRP_PREV

When i apply update, it failed with Error: "Cannot update a complex query with more then one table"

So do I need to add TZUpdateSQL to handle them?Is there another way so that i don't need to add TZupdateSQL Component. Thx

Iwan Cahyadi Sugeng

[btrewern]

I think you could have two TZQuery components and work like that. You'd need to use transactions to keep your updates consistent but I suppose it would work.

Regards,

Ben

[iwancs]

Yes, i know that I Can used to TZQuery and use lookup field, but it will degrade the performance when loading the table if the main table must join to more than 2 tables.

I need this query to display data that consists of more than 2 tables and that table is used for transaction too.

Iwan Cahyadi Sugeng

[mdaems]

Hi Iwan,

Ben does not refer to lookup fields but master-detail queries. You can't show this in one grid, however.
I think this will have a similar effect on performance.
Conclusion : if you need one resultset or have problems with performance linking 2 queries you will need to use a ZUpdateSql component.

There might be a a database solution to this when you can create a database view that allows updating. (Some databases can do this under the right conditions)

Mark

[uwwin]

i have added TZUpdateSQL.. but still error?
why?
when i have a complex query text like 'select a,b from tbl_a a inner tbl_b on a.key=b.key inner join tbl_c on c.key=a.fkey';
and
ZUpdateSQL.ModifySQL.text='update k set k.name=:name where k.id=:k.id_OLD';

it doesn't work... the record did not updated.

why?

[uwwin]

I use Zeos 6.6.0

[mdaems]

The example isn't specific enough to make a conclusion.
Can you give the exact sql of the ZQuery and ZUpdateSql components that you linked in your application? In your example you are updating a table k using a parameter field k.id_old when there's no id field in the query which uses tables tbl_a and tbl_b.
That's a little confusing.
Mark

[Asis]

I got the same error lately.

You might have forgotten to fill up property UpdateObject of your ZQuery component.

If you havn't, try to place in ZUpdateSQL.SQL.Text this:
ZUpdateSQL.ModifySQL.text='update k set name=:name where id=:old_id';

and do not forget to insert field 'id' in SELECT clause of your ZQuery.

[uwwin]

I Have inserted UpdateObject with TUpdateSQL in my Zquery...
this application can work in Zeos 6.5.1...
but after i have used zeos 6.6.1 it didn't worked.
and this is the other case

Code: Select all

zquerymaster:='select * from tbl_master';

datasource1.dataset:=zquerymaster;

zquerydetail.datasource:=datasource1;
Zquerydetail.sql.text:='Select * from tbl_detail where id_master=:id_master';
zqueryDetail.UpdatedObject:=UpdateDetailQ;

UpdateDetailQ.modify:='update tbl_detail set  tbl_detail.name=:name where
  tbl_detail.id_master= :OLD_id_master and  tbl_detail.no = :OLD_no'

then i have code like this

Code: Select all

 if (add) then
   zquerydetail.append
  else if (send) then
     zquerydetail.post

it can run... but the record did not updated

[Asis]

Try to enumerate field names in zquerymaster SQL explicitly.