Есть ошибка в процедуре, которую я вызываю в моей программе. Пожалуйста, помогите мне исправить это, это сбивает меня с толку :(. Я получил сообщение, подобное этому: «У вас есть ошибка в вашем синтаксисе SQL; проверьте руководство, соответствующее вашей версии сервера MySQL, для правильного синтаксиса, чтобы использовать рядом» ) ‘в строке 1 «
Это исходный код процедуры:
BEGIN
DECLARE tampung VARCHAR(1000);
DROP TEMPORARY TABLE IF EXISTS xxx_sensusdaftar_rajal;
SET @tabel = 'create TEMPORARY table xxx_sensusdaftar_rajal( tglreg date, shift1 int, pasienbaru int, kdpoly int, P int, L int, poly_rj1 int, poly_rj2 int, poly_rj3 int, poly_rj4 int, polyrujukan int, ';
select group_concat(concat('carabayar_kd',kode,' int') separator ', ') into tampung from m_carabayar order by kode;
set @tabel = concat(@tabel,tampung,')');
PREPARE stmt1 FROM @tabel;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
set @insert1 = "insert into xxx_sensusdaftar_rajal
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,";
set tampung = '';
select group_concat(concat('cast(sum(a.kdcarabayar/',kode,'*(1-abs(sign(a.kdcarabayar-',kode,')))) as UNSIGNED ) as carabayar_kd',kode) separator ', ') into tampung from m_carabayar order by kode;
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=1 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=2 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=3 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=4 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=5 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=6 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=7 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=8 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=28 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=29 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=30 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=9 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=10 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly
union
select a.tglreg,a.shift,a.pasienbaru,a.kdpoly,
sum( if(b.jeniskelamin='P',1, null)) as P,
sum(if(b.jeniskelamin='L', 1, null ))as L,
cast(sum(a.kdrujuk*(1-abs(sign(a.kdrujuk-1)))) as UNSIGNED ) as poly_rujuk_kd1,
cast(sum(a.kdrujuk/2*(1-abs(sign(a.kdrujuk-2)))) as UNSIGNED ) as poly_rujuk_kd2,
cast(sum(a.kdrujuk/3*(1-abs(sign(a.kdrujuk-3)))) as UNSIGNED ) as poly_rujuk_kd3,
cast(sum(a.kdrujuk/4*(1-abs(sign(a.kdrujuk-4)))) as UNSIGNED ) as poly_rujuk_kd4,
cast(sum(kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as poli_kd11,");
set @insert1 = concat(@insert1,tampung," FROM t_pendaftaran a , m_pasien b
where a.kdpoly=11 and a.nomr=b.nomr and tglreg between '",inTANGGAL,"' and '",inTANGGAL2,"' GROUP BY a.tglreg,a.pasienbaru,a.kdpoly;");
PREPARE stmt2 FROM @insert1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
DROP TEMPORARY TABLE IF EXISTS xxx_sensusdaftar_rajal2;
SET @tabel2 = 'create TEMPORARY table xxx_sensusdaftar_rajal2(
tglreg date,
shift1 int,
pasienbaru varchar(10),
P int,
L int,
poli1_rj1 int,
poli2_rj1 int,
poli3_rj1 int,
poli4_rj1 int,
poli5_rj1 int,
poli6_rj1 int,
poli7_rj1 int,
poli8_rj1 int,
poli9_rj1 int,
poli10_rj1 int,
poli28_rj1 int,
poli29_rj1 int,
poli30_rj1 int,
poli1_rj2 int,
poli2_rj2 int,
poli3_rj2 int,
poli4_rj2 int,
poli5_rj2 int,
poli6_rj2 int,
poli7_rj2 int,
poli8_rj2 int,
poli9_rj2 int,
poli10_rj2 int,
poli28_rj2 int,
poli29_rj2 int,
poli30_rj2 int,
poli1_rj3 int,
poli2_rj3 int,
poli3_rj3 int,
poli4_rj3 int,
poli5_rj3 int,
poli6_rj3 int,
poli7_rj3 int,
poli8_rj3 int,
poli9_rj3 int,
poli10_rj3 int,
poli28_rj3 int,
poli29_rj3 int,
poli30_rj3 int,
poli1_rj4 int,
poli2_rj4 int,
poli3_rj4 int,
poli4_rj4 int,
poli5_rj4 int,
poli6_rj4 int,
poli7_rj4 int,
poli8_rj4 int,
poli9_rj4 int,
poli10_rj4 int,
poli28_rj4 int,
poli29_rj4 int,
poli30_rj4 int,
rujukan int,';
set tampung = '';
select group_concat(concat('crbyr',kode,' int') separator ', ') into tampung from m_carabayar order by kode;
set @tabel2 = concat(@tabel2,tampung,', total int);');
PREPARE stmt3 FROM @tabel2;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
set @insert2 = "insert into xxx_sensusdaftar_rajal2
select tglreg, a.shift1,case a.pasienbaru when 0 then 'LAMA' when 1 then 'BARU' end as pasienbaru,
sum(P) as P,
sum(L) as L,
cast(sum(poly_rj1*kdpoly*(1-abs(sign(kdpoly-1)))) as UNSIGNED ) as poli1_rj1,
cast(sum(poly_rj1*kdpoly/2*(1-abs(sign(kdpoly-2)))) as UNSIGNED ) as poli2_rj1,
cast(sum(poly_rj1*kdpoly/3*(1-abs(sign(kdpoly-3)))) as UNSIGNED ) as poli3_rj1,
cast(sum(poly_rj1*kdpoly/4*(1-abs(sign(kdpoly-4)))) as UNSIGNED ) as poli4_rj1,
cast(sum(poly_rj1*kdpoly/5*(1-abs(sign(kdpoly-5)))) as UNSIGNED ) as poli5_rj1,
cast(sum(poly_rj1*kdpoly/6*(1-abs(sign(kdpoly-6)))) as UNSIGNED ) as poli6_rj1,
cast(sum(poly_rj1*kdpoly/7*(1-abs(sign(kdpoly-7)))) as UNSIGNED ) as poli7_rj1,
cast(sum(poly_rj1*kdpoly/8*(1-abs(sign(kdpoly-8)))) as UNSIGNED ) as poli8_rj1,
cast(sum(poly_rj1*kdpoly/9*(1-abs(sign(kdpoly-9)))) as UNSIGNED ) as poli9_rj1,
cast(sum(poly_rj1*kdpoly/10*(1-abs(sign(kdpoly-10)))) as UNSIGNED ) as poli10_rj1,
cast(sum(poly_rj1*kdpoly/28*(1-abs(sign(kdpoly-28)))) as UNSIGNED ) as poli28_rj1,
cast(sum(poly_rj1*kdpoly/29*(1-abs(sign(kdpoly-29)))) as UNSIGNED ) as poli29_rj1,
cast(sum(poly_rj1*kdpoly/30*(1-abs(sign(kdpoly-30)))) as UNSIGNED ) as poli30_rj1,
cast(sum(poly_rj2*kdpoly*(1-abs(sign(kdpoly-1)))) as UNSIGNED ) as poli1_rj2,
cast(sum(poly_rj2*kdpoly/2*(1-abs(sign(kdpoly-2)))) as UNSIGNED ) as poli2_rj2,
cast(sum(poly_rj2*kdpoly/3*(1-abs(sign(kdpoly-3)))) as UNSIGNED ) as poli3_rj2,
cast(sum(poly_rj2*kdpoly/4*(1-abs(sign(kdpoly-4)))) as UNSIGNED ) as poli4_rj2,
cast(sum(poly_rj2*kdpoly/5*(1-abs(sign(kdpoly-5)))) as UNSIGNED ) as poli5_rj2,
cast(sum(poly_rj2*kdpoly/6*(1-abs(sign(kdpoly-6)))) as UNSIGNED ) as poli6_rj2,
cast(sum(poly_rj2*kdpoly/7*(1-abs(sign(kdpoly-7)))) as UNSIGNED ) as poli7_rj2,
cast(sum(poly_rj2*kdpoly/8*(1-abs(sign(kdpoly-8)))) as UNSIGNED ) as poli8_rj2,
cast(sum(poly_rj2*kdpoly/9*(1-abs(sign(kdpoly-9)))) as UNSIGNED ) as poli9_rj2,
cast(sum(poly_rj2*kdpoly/10*(1-abs(sign(kdpoly-10)))) as UNSIGNED ) as poli10_rj2,
cast(sum(poly_rj2*kdpoly/28*(1-abs(sign(kdpoly-28)))) as UNSIGNED ) as poli28_rj2,
cast(sum(poly_rj2*kdpoly/29*(1-abs(sign(kdpoly-29)))) as UNSIGNED ) as poli29_rj2,
cast(sum(poly_rj2*kdpoly/30*(1-abs(sign(kdpoly-30)))) as UNSIGNED ) as poli30_rj2,
cast(sum(poly_rj3*kdpoly*(1-abs(sign(kdpoly-1)))) as UNSIGNED ) as poli1_rj3,
cast(sum(poly_rj3*kdpoly/2*(1-abs(sign(kdpoly-2)))) as UNSIGNED ) as poli2_rj3,
cast(sum(poly_rj3*kdpoly/3*(1-abs(sign(kdpoly-3)))) as UNSIGNED ) as poli3_rj3,
cast(sum(poly_rj3*kdpoly/4*(1-abs(sign(kdpoly-4)))) as UNSIGNED ) as poli4_rj3,
cast(sum(poly_rj3*kdpoly/5*(1-abs(sign(kdpoly-5)))) as UNSIGNED ) as poli5_rj3,
cast(sum(poly_rj3*kdpoly/6*(1-abs(sign(kdpoly-6)))) as UNSIGNED ) as poli6_rj3,
cast(sum(poly_rj3*kdpoly/7*(1-abs(sign(kdpoly-7)))) as UNSIGNED ) as poli7_rj3,
cast(sum(poly_rj3*kdpoly/8*(1-abs(sign(kdpoly-8)))) as UNSIGNED ) as poli8_rj3,
cast(sum(poly_rj3*kdpoly/9*(1-abs(sign(kdpoly-9)))) as UNSIGNED ) as poli9_rj3,
cast(sum(poly_rj3*kdpoly/10*(1-abs(sign(kdpoly-10)))) as UNSIGNED ) as poli10_rj3,
cast(sum(poly_rj3*kdpoly/28*(1-abs(sign(kdpoly-28)))) as UNSIGNED ) as poli28_rj3,
cast(sum(poly_rj3*kdpoly/29*(1-abs(sign(kdpoly-29)))) as UNSIGNED ) as poli29_rj3,
cast(sum(poly_rj3*kdpoly/30*(1-abs(sign(kdpoly-30)))) as UNSIGNED ) as poli30_rj3,
cast(sum(poly_rj4*kdpoly*(1-abs(sign(kdpoly-1)))) as UNSIGNED ) as poli1_rj4,
cast(sum(poly_rj4*kdpoly/2*(1-abs(sign(kdpoly-2)))) as UNSIGNED ) as poli2_rj4,
cast(sum(poly_rj4*kdpoly/3*(1-abs(sign(kdpoly-3)))) as UNSIGNED ) as poli3_rj4,
cast(sum(poly_rj4*kdpoly/4*(1-abs(sign(kdpoly-4)))) as UNSIGNED ) as poli4_rj4,
cast(sum(poly_rj4*kdpoly/5*(1-abs(sign(kdpoly-5)))) as UNSIGNED ) as poli5_rj4,
cast(sum(poly_rj4*kdpoly/6*(1-abs(sign(kdpoly-6)))) as UNSIGNED ) as poli6_rj4,
cast(sum(poly_rj4*kdpoly/7*(1-abs(sign(kdpoly-7)))) as UNSIGNED ) as poli7_rj4,
cast(sum(poly_rj4*kdpoly/8*(1-abs(sign(kdpoly-8)))) as UNSIGNED ) as poli8_rj4,
cast(sum(poly_rj4*kdpoly/9*(1-abs(sign(kdpoly-9)))) as UNSIGNED ) as poli9_rj4,
cast(sum(poly_rj4*kdpoly/10*(1-abs(sign(kdpoly-10)))) as UNSIGNED ) as poli10_rj4,
cast(sum(poly_rj4*kdpoly/28*(1-abs(sign(kdpoly-28)))) as UNSIGNED ) as poli28_rj4,
cast(sum(poly_rj4*kdpoly/29*(1-abs(sign(kdpoly-29)))) as UNSIGNED ) as poli29_rj4,
cast(sum(poly_rj4*kdpoly/30*(1-abs(sign(kdpoly-30)))) as UNSIGNED ) as poli30_rj4,
cast(sum(poly_rj1*kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) +
cast(sum(poly_rj2*kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) +
cast(sum(poly_rj3*kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) +
cast(sum(poly_rj4*kdpoly/11*(1-abs(sign(kdpoly-11)))) as UNSIGNED ) as rujukan,";
set tampung = '';
select group_concat(concat('sum(carabayar_kd',kode,') as crbyr',kode) separator ', ') into tampung from m_carabayar order by kode;
set @insert2 = concat(@insert2,tampung,', ');
set tampung = '';
select group_concat(concat('sum(carabayar_kd',kode,')') separator '+') into tampung from m_carabayar order by kode;
set @insert2 = concat(@insert2,tampung," as total
FROM xxx_sensusdaftar_rajal a GROUP BY tglreg, a.pasienbaru
ORDER BY tglreg DESC;");
PREPARE stmt4 FROM @insert2;
EXECUTE stmt4;
DEALLOCATE PREPARE stmt4;
set @select = "select shift1, pasienbaru,
sum(P) as P,
sum(L) as L,
sum(poli1_rj1) as poli1_rj1,
sum(poli2_rj1) as poli2_rj1,
sum(poli3_rj1) as poli3_rj1,
sum(poli4_rj1) as poli4_rj1,
sum(poli5_rj1) as poli5_rj1,
sum(poli6_rj1) as poli6_rj1,
sum(poli7_rj1) as poli7_rj1,
sum(poli8_rj1) as poli8_rj1,
sum(poli9_rj1) as poli9_rj1,
sum(poli10_rj1) as poli10_rj1,
sum(poli28_rj1) as poli28_rj1,
sum(poli29_rj1) as poli29_rj1,
sum(poli30_rj1) as poli30_rj1,
sum(poli1_rj2) as poli1_rj2,
sum(poli2_rj2) as poli2_rj2,
sum(poli3_rj2) as poli3_rj2,
sum(poli4_rj2) as poli4_rj2,
sum(poli5_rj2) as poli5_rj2,
sum(poli6_rj2) as poli6_rj2,
sum(poli7_rj2) as poli7_rj2,
sum(poli8_rj2) as poli8_rj2,
sum(poli9_rj2) as poli9_rj2,
sum(poli10_rj2) as poli10_rj2,
sum(poli28_rj2) as poli28_rj2,
sum(poli29_rj2) as poli29_rj2,
sum(poli30_rj2) as poli30_rj2,
sum(poli1_rj3) as poli1_rj3,
sum(poli2_rj3) as poli2_rj3,
sum(poli3_rj3) as poli3_rj3,
sum(poli4_rj3) as poli4_rj3,
sum(poli5_rj3) as poli5_rj3,
sum(poli6_rj3) as poli6_rj3,
sum(poli7_rj3) as poli7_rj3,
sum(poli8_rj3) as poli8_rj3,
sum(poli9_rj3) as poli9_rj3,
sum(poli10_rj3) as poli10_rj3,
sum(poli28_rj3) as poli28_rj3,
sum(poli29_rj3) as poli29_rj3,
sum(poli30_rj3) as poli30_rj3,
sum(poli1_rj4) as poli1_rj4,
sum(poli2_rj4) as poli2_rj4,
sum(poli3_rj4) as poli3_rj4,
sum(poli4_rj4) as poli4_rj4,
sum(poli5_rj4) as poli5_rj4,
sum(poli6_rj4) as poli6_rj4,
sum(poli7_rj4) as poli7_rj4,
sum(poli8_rj4) as poli8_rj4,
sum(poli9_rj4) as poli9_rj4,
sum(poli10_rj4) as poli10_rj4,
sum(poli28_rj4) as poli28_rj4,
sum(poli29_rj4) as poli29_rj4,
sum(poli30_rj4) as poli30_rj4,
sum(rujukan) as rujukan,";
set tampung = '';
select group_concat(concat('sum(crbyr',kode,') as crbyr',kode) separator ', ') into tampung from m_carabayar order by kode;
set @select = concat(@select,tampung,', sum(total) as total from xxx_sensusdaftar_rajal2 group by pasienbaru;');
PREPARE stmt5 FROM @select;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;
END
Клянусь, я только что проверил это на моей системе. В отличие от вырезания и вставки всего этого, чтобы показать вам, просто оберните все, что у вас есть внутри этого
DELIMITER $$
create procedure blahblah789()
(ALL OF YOUR STUFF)
$$
DELIMITER ;
Работает, значит нет синтаксической ошибки 1064. Если бы я запускал его, ну, конечно, у меня не было бы таблиц.
Вам нужно определять границы Это. Дать ему шанс
Страница справочника Использование разделителей с MySqlScript
Других решений пока нет …