Update Failed On Complex Query
Moderators: gto, cipto_kh, EgonHugeist
Update Failed On Complex Query
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
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
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
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
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
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
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
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?
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?
- mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
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
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
![Question :?:](./images/smilies/icon_question.gif)
Mark
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.
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.
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
then i have code like this
it can run... but the record did not updated
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'
Code: Select all
if (add) then
zquerydetail.append
else if (send) then
zquerydetail.post