-- create and seed test tables
CREATE DATABASE IF NOT EXISTS maria_bug;
USE maria_bug;

CREATE TABLE ABC_fact_at_reghrp (
   APO_ID INT NOT NULL,
   PZN_ID INT NOT NULL
) ENGINE=ColumnStore;  -- or your actual ColumnStore engine

CREATE TABLE ABC_tbr_adm_rpi_kunde (
   KUNDE_ID    INT NOT NULL,
   STRUKTUR_ID INT NOT NULL,
   APO_ID      INT NOT NULL,
   ADMAT_ID    INT NOT NULL
) ENGINE=InnoDB;  -- or your actual engine for this table

CREATE TABLE ABC_fact_at_reghrd (
   KUNDE_ID    INT NOT NULL,
   STRUKTUR_ID INT NOT NULL,
   APO_ID      INT NOT NULL,
   ADMAT_ID    INT NOT NULL,
   PZN_ID      INT NOT NULL
) ENGINE=ColumnStore;  -- or your actual ColumnStore engine

INSERT INTO ABC_tbr_adm_rpi_kunde (KUNDE_ID, STRUKTUR_ID, APO_ID, ADMAT_ID) VALUES (38, 1, 123, 401);
INSERT INTO ABC_fact_at_reghrp (APO_ID, PZN_ID) VALUES (123, 3520496);
INSERT INTO ABC_fact_at_reghrd (KUNDE_ID, STRUKTUR_ID, APO_ID, ADMAT_ID, PZN_ID) VALUES (38, 1, 99, 401, 3520496);

SELECT 
 CASE WHEN v.admat_id IN (401,402,403,404,405) THEN '$5' ELSE 'REST' END AS reggroup,
 v.PZN_ID AS `13999_PZN`,
 NULL AS VALUE_2025_12_M_8132_1_1
FROM (
   SELECT
       mcads.ADMAT_ID AS admat_id,
       v.APO_ID       AS APO_ID,
       v.PZN_ID       AS PZN_ID
   FROM
       maria_bug.ABC_fact_at_reghrp AS v
       JOIN maria_bug.ABC_tbr_adm_rpi_kunde AS mcads
         ON mcads.KUNDE_ID    = 38
        AND mcads.STRUKTUR_ID = 1
        AND v.APO_ID          = mcads.APO_ID
   UNION ALL
   SELECT
       v.ADMAT_ID AS admat_id,
       99         AS APO_ID,
       v.PZN_ID   AS PZN_ID
   FROM
       maria_bug.ABC_fact_at_reghrd AS v
   WHERE
       v.KUNDE_ID    = 38
       AND v.STRUKTUR_ID = 1
) AS v
WHERE
   v.admat_id IN (401,402,403,404,405)
   AND v.PZN_ID IN (3520496,5129827,5129833)
GROUP BY
   reggroup, `13999_PZN`;