Alternative for cursor
Poster needed to walk a table row-by-row to flag duplicate invoices (same invoice number and date but different client/address numbers) and update a temp table, but was told to avoid cursors for performance on ~100k-row tables. He also hit the restriction that SELECT FIRST 1 ... INTO TEMP isn't allowed. Resolution: do it in a single set-based UPDATE, e.g. UPDATE tmp SET repeat='Y', account=(SELECT account FROM c_client ...) WHERE EXISTS (SELECT * FROM tmp_repeat tr WHERE matching nr/date/addr_nr) — which the poster confirmed worked. Art Kagel noted cursors aren't inherently slow, but doing the work server-side avoids communication overhead; Jonathan Leffler offered a simple self-join query for the duplicate detection.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I have to process table row by row but without using cursor! Is there
any alternative for it in Informix?
I tried to get first row and put it in the tmp_table but the following
instruction is not allowed for a FIRST clause:
select first 1 * from xyz into temp tmp_xyz with no log;
So I can not take even first row save it to any tmp table or any
variables to process it.
I need to read each row from xyz table and using information from this
row update corresponding row in another table.
Something like this (it's not a code just to show you what I mean):
for each row in xyz do{
update invoice set powt="T"
where invoice.nr = xyz.nr
invoice.date = xyz.date}
Thanks for any help.
Max
I don't quite understand.
Can you do a standard update from a select like
update
invoice
set
powt="T"
where
exists(select * from
invoice.nr = xyz.nr
invoice.date = xyz.date
)
;
If you could explain the tables and what you want to update based on
what information I could help.
maxskalski@gmail.com wrote:
> Hi,
> I have to process table row by row but without using cursor! Is there
> any alternative for it in Informix?
> I tried to get first row and put it in the tmp_table but the following
> instruction is not allowed for a FIRST clause:
>
> select first 1 * from xyz into temp tmp_xyz with no log;>
> So I can not take even first row save it to any tmp table or any
> variables to process it.
>
> I need to read each row from xyz table and using information from this
> row update corresponding row in another table.
>
> Something like this (it's not a code just to show you what I mean):
> for each row in xyz do{
> update invoice set powt="T"
> where invoice.nr = xyz.nr
> invoice.date = xyz.date> }
>
> Thanks for any help.
> Max
I'll try to explain it more:
Problem: Find invoices with the same number and the same date but with
different client numbers. Report it.
/*I'm taking some data from tmp_art_move table into smaller tmp table -
client address number, invoice number and invoice date*/
SELECT
F.addr_nr,
F.invoice_nr,
F.invoice_date,
"N" as repeat,
"00000000" as account
FROM tmp_art_move F
GROUP BY F.addr_nr, F.invoice_date, F.invoice_nr
INTO TEMP tmp WITH NO LOG;
/*now I'm writing to the tmp_repeat table invoices with the same number
and date but written out for different clients*/
SELECT DISTINCT a.addr_nr, a.invoice_nr, a.invoice_date
FROM tmp a, tmp b
WHERE a.invoice_nr = b.invoice_nr and
a.invoice_date = b.invoice_date and
a.addr_nr != b.addr_nr
ORDER BY a.invoice_date, a.invoice_nr, a.addr_nr
INTO tmp_repeat;
What I have to do without using cursor?
Take each row from tmp_repeat table find the same row in tmp table
(with the same invoice_nr, invoice_date and addr_nr as in the
tmp_repeat table) and update repeat="Y"
and account=(select account from c_client where add_nr=....)
Thanks in advance,
Max
maxskalski@gmail.com wrote:
> I'll try to explain it more:
>
> Problem: Find invoices with the same number and the same date but with
> different client numbers. Report it.
I'm sure the explanation below makes clear as water sense to you, but it's
opaque to us. How about some example data for the tmp table before
processing, for the tmp_repeat table and for the desired results in tmp
after processing. Also why no CURSORs? Seems an obvious way to handle
things like this.
Art S. Kagel
> /*I'm taking some data from tmp_art_move table into smaller tmp table -
> client address number, invoice number and invoice date*/
> SELECT
> F.addr_nr,
> F.invoice_nr,
> F.invoice_date,
> "N" as repeat,
> "00000000" as account
> FROM tmp_art_move F
> GROUP BY F.addr_nr, F.invoice_date, F.invoice_nr
> INTO TEMP tmp WITH NO LOG;
>
> /*now I'm writing to the tmp_repeat table invoices with the same number
> and date but written out for different clients*/
>
> SELECT DISTINCT a.addr_nr, a.invoice_nr, a.invoice_date
> FROM tmp a, tmp b
> WHERE a.invoice_nr = b.invoice_nr and
> a.invoice_date = b.invoice_date and
> a.addr_nr != b.addr_nr
> ORDER BY a.invoice_date, a.invoice_nr, a.addr_nr
> INTO tmp_repeat;>
> What I have to do without using cursor?
> Take each row from tmp_repeat table find the same row in tmp table
> (with the same invoice_nr, invoice_date and addr_nr as in the
> tmp_repeat table) and update repeat="Y"
> and account=(select account from c_client where add_nr=....)
>
> Thanks in advance,
> Max
>
I don't think you need a cursor. Here is the single update that you
will need
update tmp_art_move set repeat = "y", account=(select account fromc_client where add_nr=....) ) where
exists(select * from tmp_repeat tr where tmp_art_move.invoice_nr =
tr.invoice_nr and tmp_art_move.invoice_date = tr.invoice_date and
tmp_art_move.addr_nr = tr.addr_nr)
should do the trick. The ... was because you didn't include the join to
the c_client table that I would need to make but it would be relative
to the tmp_art_move table.
PS. Don't order you repeat temp table there is no reason.
maxskalski@gmail.com wrote:
> I'll try to explain it more:
>
> Problem: Find invoices with the same number and the same date but with
> different client numbers. Report it.
>
> /*I'm taking some data from tmp_art_move table into smaller tmp table -
> client address number, invoice number and invoice date*/
> SELECT
> F.addr_nr,
> F.invoice_nr,
> F.invoice_date,
> "N" as repeat,
> "00000000" as account
> FROM tmp_art_move F
> GROUP BY F.addr_nr, F.invoice_date, F.invoice_nr
> INTO TEMP tmp WITH NO LOG;
>
> /*now I'm writing to the tmp_repeat table invoices with the same number
> and date but written out for different clients*/
>
> SELECT DISTINCT a.addr_nr, a.invoice_nr, a.invoice_date
> FROM tmp a, tmp b
> WHERE a.invoice_nr = b.invoice_nr and
> a.invoice_date = b.invoice_date and
> a.addr_nr != b.addr_nr
> ORDER BY a.invoice_date, a.invoice_nr, a.addr_nr
> INTO tmp_repeat;>
> What I have to do without using cursor?
> Take each row from tmp_repeat table find the same row in tmp table
> (with the same invoice_nr, invoice_date and addr_nr as in the
> tmp_repeat table) and update repeat="Y"
> and account=(select account from c_client where add_nr=....)
>
> Thanks in advance,
> Max
I'm new in Informix, these tables store many rows (more than 100000) I was told that I have try to avoid Cursor because they are not efficient. What do you think about this? some example data: tmp table ------------------------------------------------------------------------------------------- addr_nr invoice_nr invoice_date repeat account 1234 FS456 5/2/2005 N 00000000 1234 FS457 3/10/2006 N 00000000 778 FS458 11/12/2003 N 00000000 1214 FS458 9/12/2005 N 00000000 939 FS458 9/12/2005 N 00000000 123 FS421 7/4/2006 N 00000000 432 FS122 7/1/2006 N 00000000 332 FS122 7/1/2006 N 00000000 tmp_repeat table ------------------------------------------------------------------------------------------ addr_nr invoice_nr invoice_date 939 FS458 9/12/2005 1214 FS458 9/12/2005 332 FS122 7/1/2006 432 FS122 7/1/2006 tmp table after processing ------------------------------------------------------------------------------------------- addr_nr invoice_nr invoice_date repeat account 1234 FS456 5/2/2005 N 00000000 1234 FS457 3/10/2006 N 00000000 778 FS458 11/12/2003 N 00000000 1214 FS458 9/12/2005 T 121N2334 939 FS458 9/12/2005 T 233N4533 123 FS421 7/4/2006 N 00000000 432 FS122 7/1/2006 T 00000000 332 FS122 7/1/2006 T 212N2123
maxskalski@gmail.com wrote: > I'm new in Informix, these tables store many rows (more than 100000) I > was told that I have try to avoid Cursor because they are not > efficient. What do you think about this? I think I'm understanding. IB Bozon's second solution will do the trick. As for the cursor issue, cursor's are not inherently inefficient. It is however, true that if you can get everything done within the server itself that will usually entail other efficiencies like reduced, or in this case eliminated, communications volume. Art S. Kagel > some example data: > tmp table > ------------------------------------------------------------------------------------------- > addr_nr invoice_nr invoice_date repeat account > 1234 FS456 5/2/2005 N 00000000 > 1234 FS457 3/10/2006 N 00000000 > 778 FS458 11/12/2003 N 00000000 > 1214 FS458 9/12/2005 N 00000000 > 939 FS458 9/12/2005 N 00000000 > 123 FS421 7/4/2006 N 00000000 > 432 FS122 7/1/2006 N 00000000 > 332 FS122 7/1/2006 N 00000000 > > tmp_repeat table > ------------------------------------------------------------------------------------------ > addr_nr invoice_nr invoice_date > 939 FS458 9/12/2005 > 1214 FS458 9/12/2005 > 332 FS122 7/1/2006 > 432 FS122 7/1/2006 > > tmp table after processing > ------------------------------------------------------------------------------------------- > addr_nr invoice_nr invoice_date repeat account > 1234 FS456 5/2/2005 N 00000000 > 1234 FS457 3/10/2006 N 00000000 > 778 FS458 11/12/2003 N 00000000 > 1214 FS458 9/12/2005 T 121N2334 > 939 FS458 9/12/2005 T 233N4533 > 123 FS421 7/4/2006 N 00000000 > 432 FS122 7/1/2006 T 00000000 > 332 FS122 7/1/2006 T 212N2123 >
Yes, that's it! It works. Thanks
maxskalski@gmail.com wrote: > Problem: Find invoices with the same number and the same date but with > different client numbers. Report it. SELECT i1.*, i2.* FROM Invoice i1, Invoice i2 WHERE i1.invoice_nr = i2.invoice_nr AND i1.invoice_date = i2.invoice_date AND i1.addr_nr != i2.addr_nr; Where have I misinterpreted your requirement? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/