X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=sql%2FPg-upgrade2%2Fadd_tax_id_to_acc_trans.sql;h=64db3ff6f2e24d40ec89dbb42b28ba0507d42069;hb=491a3c27055cabce59b4d93a6e389a59125eeef7;hp=94cdb7613fba347f6001c3506542f05780b11860;hpb=357d134d265637fb37ed2cd47da2ce3ef44d015a;p=kivitendo-erp.git

diff --git a/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql b/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql
index 94cdb7613..64db3ff6f 100644
--- a/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql
+++ b/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql
@@ -1,24 +1,24 @@
 -- @tag: add_tax_id_to_acc_trans
 -- @description: Neue Spalte tax_id in der acc_trans
--- @depends: release_2_7_0 
+-- @depends: release_3_0_0 charts_without_taxkey
 
   --Neue Spalte tax_id in acc_trans:
   ALTER TABLE acc_trans ADD tax_id integer;
 
   --Spalte mit Werten füllen:
   UPDATE acc_trans ac SET tax_id=
-                (select tk.tax_id from taxkeys tk 
-                      where tk.taxkey_id=ac.taxkey 
+                (SELECT tk.tax_id FROM taxkeys tk 
+                      WHERE tk.taxkey_id=ac.taxkey 
                       AND tk.startdate <= COALESCE(
-                            (select ar.deliverydate from ar where ar.id=ac.trans_id), 
-                            (select ar.transdate from ar where ar.id=ac.trans_id), 
-                            (select ap.transdate from ap where ap.id=ac.trans_id), 
-                            (select gl.transdate from gl where gl.id=ac.trans_id), 
+                            (SELECT ar.deliverydate FROM ar WHERE ar.id=ac.trans_id), 
+                            (SELECT ar.transdate FROM ar WHERE ar.id=ac.trans_id), 
+                            (SELECT ap.transdate FROM ap WHERE ap.id=ac.trans_id), 
+                            (SELECT gl.transdate FROM gl WHERE gl.id=ac.trans_id), 
                             ac.transdate )
-                      order by startdate desc limit 1);
+                      ORDER BY startdate DESC LIMIT 1);
 
   --Spalten, die noch null sind (nur bei Einträgen möglich, wo auch taxkey null ist)
-  UPDATE acc_trans SET tax_id=0 where tax_id is null;
+  UPDATE acc_trans SET tax_id= (SELECT id FROM tax WHERE taxkey=0 LIMIT 1) WHERE tax_id IS NULL;
  
   --tax_id als Pflichtfeld definieren:
   ALTER TABLE acc_trans ALTER tax_id SET NOT NULL;