Hey Folks,
i have tho following problem and im not able to solve it. I want to use a
Delete Command within a Merge-Statement between two tables (soruce and
target). But i only retrieve a ClassCastExcetion: Error: Error while
compiling statement: FAILED: ClassCastException null
(state=42000,code=4)
Following setup:
--source
CREATE TABLE source (
sl_rzbk decimal(4,0),
dat_abgeschlossen date,
dat_eingefuegt date,
nr_partner decimal(18,0),
value1 varchar(32),
value2 varchar(32)
)
STORED AS ORC;
-- Insert value
Insert into source values (8801, '-12-31','2019-02-02', 1234,'current
record', 'new');
Insert into source values (8801, '2019-02-01','2019-02-01', 1234,'old
record', 'old');
Insert into source values (8801, '-12-31','2019-02-02', 123,'current
record', 'new');
Insert into source values (8801, '2019-02-01','2019-02-01', 123,'old
record', 'old');
-- target
CREATE TABLE target (
sl_rzbk decimal(4,0),
dat_abgeschlossen date,
dat_eingefuegt date,
nr_partner decimal(18,0),
value1 varchar(32),
value2 varchar(32)
)
STORED AS ORC;
-- Merge into
MERGE INTO target t
USING (Select * from source ) AS s
ON s.sl_rzbk = t.sl_rzbk and s.dat_eingefuegt = t.dat_eingefuegt and
s.nr_partner = t.nr_partner
WHEN MATCHED THEN UPDATE SET dat_abgeschlossen = s.dat_abgeschlossen,
value1 = s.value1,value2 = s.value2
WHEN NOT MATCHED THEN INSERT VALUES (s.sl_rzbk, s.dat_abgeschlossen,
s.dat_eingefuegt, s.nr_partner, s.value1, s.value2)
-- Delete from Source
DELETE from source where nr_partner = 123;
-- MERGE DELETE
MERGE INTO target tt
USING ( with tmp as(select * from source )
Select tmp.sl_rzbk ,
tmp.dat_abgeschlossen,
tmp.dat_eingefuegt,
tmp.value1,
tmp.value2,
tmp.nr_partner,
CASE WHEN tmp.nr_partner is NULL
THEN 1 ELSE 0 END
delete_flag FROM tmp
FULL JOIN target t ON (t.sl_rzbk= tmp.sl_rzbk AND t.dat_eingefuegt =
tmp.dat_eingefuegt AND t.nr_partner = tmp.nr_partner)) tmp
ON (tt.sl_rzbk = tmp.sl_rzbk, tt.dat_eingefuegt = tmp.dat_eingefuegt,
tt.nr_partner = tmp.nr_partner)
WHEN MATCHED AND tmp.delete_flag=0 THEN UPDATE SET sl_rzbk = tmp.sl_rzbk,
dat_abgeschlossen = tmp.dat_abgeschlossen , value1=tmp.value1, value2 =
tmp.value2
Any ideas ?
Thanks in advance, greets and stay healthy
Florian
Fiducia & GAD IT AG | www.fiduciagad.de
AG Frankfurt a. M. HRB 102381 | Sitz der Gesellschaft: Frankfurt a. M. |
USt-IdNr. DE 143582320
Vorstand: Martin Beyer (Vorstandssprecher), Ulrich Coenen, Birgit
Frohnhoff, Jörg Staff
Vorsitzender des Aufsichtsrats: Jürgen Brinkmann
