begin; -- handle NULL chart_id's insert into chart (accno, description, charttype) values ('1000000000', 'Broken SQL-Ledger Data', 'L'); --handle duplicate transaction id's. CREATE TEMPORARY TABLE id_agregator (id INT, table_name text); insert into id_agregator (id, table_name) SELECT id, 'ar' from ar; insert into id_agregator (id, table_name) SELECT id, 'ap' from ap; insert into id_agregator (id, table_name) SELECT id, 'business' from business; insert into id_agregator (id, table_name) SELECT id, 'chart' from chart; insert into id_agregator (id, table_name) SELECT id, 'customer' from customer; insert into id_agregator (id, table_name) SELECT id, 'department' from department; insert into id_agregator (id, table_name) SELECT id, 'employee' from employee; insert into id_agregator (id, table_name) SELECT id, 'gl' from gl; insert into id_agregator (id, table_name) SELECT id, 'oe' from oe; insert into id_agregator (id, table_name) SELECT id, 'parts' from parts; insert into id_agregator (id, table_name) SELECT id, 'partsgroup' from partsgroup; insert into id_agregator (id, table_name) SELECT id, 'project' from project; insert into id_agregator (id, table_name) SELECT id, 'vendor' from vendor; insert into id_agregator (id, table_name) SELECT id, 'warehouse' from warehouse; CREATE TEMPORARY VIEW id_view1 AS SELECT id, count(*) AS num_rows FROM id_agregator GROUP BY id HAVING num_rows > 1; select setval('id', select max(id) + 1 from id_agregator); create function fix_dupes() RETURNS OPAQUE AS ' DECLARE dupe_id id_agregator%ROWTYPE; BEGIN FOR dupe_id IN SELECT id FROM id_agregator WHERE id IN (SELECT id FROM id_view1) LOOP EXECUTE ''UPDATE '' || dupe_id.table_name || SET id = nextval(''''id'''') WHERE id = '' ||dupe_id.id; UPDATE acc_trans SET trans_id = currval(''id'') WHERE id = '' ||dupe_id.id; INSERT INTO acc_trans (trans_id, amount, chart_id) VALUES ( currval(''id''), '1', ( SELECT id FROM chart WHERE accno = ''1000000000'' ) ); INSERT INTO acc_trans (trans_id, amount, chart_id) VALUES ( currval(''id''), '-1', ( SELECT id FROM chart WHERE accno = ''1000000000'' ) ); END; ' LANGUAGE PLPGSQL; SELECT fix_dupes (); drop function fix_dupes(); commit; h'/>

path: root/tags/5c/c6/040e01c6cb40%24efb0ba80%246401a8c0@CrealizeIBM

diff options

author	Jonas Smedegaard <dr@jones.dk>	2023-06-27 09:35:08 +0200
committer	Jonas Smedegaard <dr@jones.dk>	2023-06-27 09:35:08 +0200
commit	fd54908da2b05c526dd3bee9b6dcd093214a220d (patch)
tree	c69c845069c99d1d01044f6fafda7c08433329c6 /tags/5c/c6/040e01c6cb40%24efb0ba80%246401a8c0@CrealizeIBM
parent	ba46132213560cf3335d53560d519c0ec0190da2 (diff)

master

Diffstat (limited to 'tags/5c/c6/040e01c6cb40%24efb0ba80%246401a8c0@CrealizeIBM')


context:
space:
mode: