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=40000) 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, '9999-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, '9999-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