Mr. Lolman
2008-04-22, 13:13:22
Hi, ich verzweifle gerade an einer Schleife, die folgendes Statement ausführen soll, wobei die Variable 'num' (5. letzte Zeile) Werte von 1 bis 974 annehmen soll:
INSERT INTO tmp_sis (
sis_zus_text_1,
sis_zus_text_2,
sis_zus_text_3,
sis_zus_text_4,
sis_zus_text_5,
sis_zus_text_6,
sis_zus_text_7,
sis_zus_text_8,
sis_zus_text_9,
sis_zus_text_10,
sis_zus_text_11,
sis_zus_text_12,
sis_zus_text_13,
sis_zus_text_14,
sis_zus_text_15,
sis_zus_text_16,
sis_zus_text_17,
sis_zus_text_18,
sis_zus_text_19,
sis_zus_text_20
)
SELECT
MAX(tmp_sis.sis_zus_text_1),
MAX(tmp_sis.sis_zus_text_2),
MAX(tmp_sis.sis_zus_text_3),
MAX(tmp_sis.sis_zus_text_4),
MAX(tmp_sis.sis_zus_text_5),
MAX(tmp_sis.sis_zus_text_6),
MAX(tmp_sis.sis_zus_text_7),
MAX(tmp_sis.sis_zus_text_8),
MAX(tmp_sis.sis_zus_text_9),
MAX(tmp_sis.sis_zus_text_10),
MAX(tmp_sis.sis_zus_text_11),
MAX(tmp_sis.sis_zus_text_12),
MAX(tmp_sis.sis_zus_text_13),
MAX(tmp_sis.sis_zus_text_14),
MAX(tmp_sis.sis_zus_text_15),
MAX(tmp_sis.sis_zus_text_16),
MAX(tmp_sis.sis_zus_text_17),
MAX(tmp_sis.sis_zus_text_18),
MAX(tmp_sis.sis_zus_text_19),
MAX(tmp_sis.sis_zus_text_20)
FROM
(
SELECT
CASE WHEN ccc_as_s.NR = 1 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_1,
CASE WHEN ccc_as_s.NR = 2 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_2,
CASE WHEN ccc_as_s.NR = 3 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_3,
CASE WHEN ccc_as_s.NR = 4 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_4,
CASE WHEN ccc_as_s.NR = 5 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_5,
CASE WHEN ccc_as_s.NR = 6 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_6,
CASE WHEN ccc_as_s.NR = 7 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_7,
CASE WHEN ccc_as_s.NR = 8 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_8,
CASE WHEN ccc_as_s.NR = 9 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_9,
CASE WHEN ccc_as_s.NR = 10 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_10,
CASE WHEN ccc_as_s.NR = 11 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_11,
CASE WHEN ccc_as_s.NR = 12 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_12,
CASE WHEN ccc_as_s.NR = 13 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_13,
CASE WHEN ccc_as_s.NR = 14 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_14,
CASE WHEN ccc_as_s.NR = 15 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_15,
CASE WHEN ccc_as_s.NR = 16 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_16,
CASE WHEN ccc_as_s.NR = 17 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_17,
CASE WHEN ccc_as_s.NR = 18 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_18,
CASE WHEN ccc_as_s.NR = 19 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_19,
CASE WHEN ccc_as_s.NR = 20 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_20
FROM
(
SELECT ROWNUM NR,
ccc_as_s_text_2
FROM ccc_as_s, ccc_r_pr_s_as_s, tmp2_sis
where
tmp2_sis.sis_prod_alias = (select sis_prod_alias from (select sis_prod_alias, rownum num from tmp2_sis) where num = 1) and
tmp2_sis.sis_prod_alias = ccc_r_pr_s_as_s.ccc_pr_s_id and
ccc_as_s.ccc_as_s_id = ccc_r_pr_s_as_s.ccc_as_s_id
) ccc_as_s
) tmp_sis;
Hat irgendjemand eine Idee?
INSERT INTO tmp_sis (
sis_zus_text_1,
sis_zus_text_2,
sis_zus_text_3,
sis_zus_text_4,
sis_zus_text_5,
sis_zus_text_6,
sis_zus_text_7,
sis_zus_text_8,
sis_zus_text_9,
sis_zus_text_10,
sis_zus_text_11,
sis_zus_text_12,
sis_zus_text_13,
sis_zus_text_14,
sis_zus_text_15,
sis_zus_text_16,
sis_zus_text_17,
sis_zus_text_18,
sis_zus_text_19,
sis_zus_text_20
)
SELECT
MAX(tmp_sis.sis_zus_text_1),
MAX(tmp_sis.sis_zus_text_2),
MAX(tmp_sis.sis_zus_text_3),
MAX(tmp_sis.sis_zus_text_4),
MAX(tmp_sis.sis_zus_text_5),
MAX(tmp_sis.sis_zus_text_6),
MAX(tmp_sis.sis_zus_text_7),
MAX(tmp_sis.sis_zus_text_8),
MAX(tmp_sis.sis_zus_text_9),
MAX(tmp_sis.sis_zus_text_10),
MAX(tmp_sis.sis_zus_text_11),
MAX(tmp_sis.sis_zus_text_12),
MAX(tmp_sis.sis_zus_text_13),
MAX(tmp_sis.sis_zus_text_14),
MAX(tmp_sis.sis_zus_text_15),
MAX(tmp_sis.sis_zus_text_16),
MAX(tmp_sis.sis_zus_text_17),
MAX(tmp_sis.sis_zus_text_18),
MAX(tmp_sis.sis_zus_text_19),
MAX(tmp_sis.sis_zus_text_20)
FROM
(
SELECT
CASE WHEN ccc_as_s.NR = 1 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_1,
CASE WHEN ccc_as_s.NR = 2 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_2,
CASE WHEN ccc_as_s.NR = 3 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_3,
CASE WHEN ccc_as_s.NR = 4 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_4,
CASE WHEN ccc_as_s.NR = 5 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_5,
CASE WHEN ccc_as_s.NR = 6 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_6,
CASE WHEN ccc_as_s.NR = 7 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_7,
CASE WHEN ccc_as_s.NR = 8 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_8,
CASE WHEN ccc_as_s.NR = 9 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_9,
CASE WHEN ccc_as_s.NR = 10 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_10,
CASE WHEN ccc_as_s.NR = 11 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_11,
CASE WHEN ccc_as_s.NR = 12 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_12,
CASE WHEN ccc_as_s.NR = 13 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_13,
CASE WHEN ccc_as_s.NR = 14 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_14,
CASE WHEN ccc_as_s.NR = 15 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_15,
CASE WHEN ccc_as_s.NR = 16 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_16,
CASE WHEN ccc_as_s.NR = 17 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_17,
CASE WHEN ccc_as_s.NR = 18 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_18,
CASE WHEN ccc_as_s.NR = 19 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_19,
CASE WHEN ccc_as_s.NR = 20 THEN ccc_as_s.ccc_as_s_text_2 END sis_zus_text_20
FROM
(
SELECT ROWNUM NR,
ccc_as_s_text_2
FROM ccc_as_s, ccc_r_pr_s_as_s, tmp2_sis
where
tmp2_sis.sis_prod_alias = (select sis_prod_alias from (select sis_prod_alias, rownum num from tmp2_sis) where num = 1) and
tmp2_sis.sis_prod_alias = ccc_r_pr_s_as_s.ccc_pr_s_id and
ccc_as_s.ccc_as_s_id = ccc_r_pr_s_as_s.ccc_as_s_id
) ccc_as_s
) tmp_sis;
Hat irgendjemand eine Idee?