alexa-tracking

[ASK] tahun dalam procedure MySQL

Main Content

1024
1024
KASKUS
51
244
https://www.kaskus.co.id/thread/53198648138b469a628b46c9/ask-tahun-dalam-procedure-mysql
[ASK] tahun dalam procedure MySQL
agan2 yang baek hati, ane mau tanya nih ttg procedure di MySql ....... begini ceritanya ::::
misal terdapat table 2011_sekolah, 2011_siswa, 2011_nilai. dan tiap tahun, awalan nama table-nya akan menyesuaikan dgn tahun pada sistem, jadi misal skrang tahun 2014, maka table2 diatas akan brubah jadi 2014_sekolah, 2014_siswa, 2014_nilai, jdi akan berganti seiring bertambahx tahun.

pertanyaan saya:
bagaimana jika itu dibuat dalam Store Procedure, jadi user hanya menginputkan tahun saja untuk menampilkan data di salah 1 table diatas...
misal user memanngil procedure tsb dgn ::
Call Proc_tahun('2011') --> maka akan tampil data di table 2011_nilai,

udah beberapa hari saya nyoba cuman masih blom ada solusi untuk menampilkan data-x dengan format diatas...
mohon bantuan agan2 sekalian
ada cara tulis procedure gan??

ya harusnya kan tinggal
Code:
select * from parameter||'_nilai'
Quote:


================================================
iya sam, ane dah nyoba cuman masih gagal: brikut potongan pada From-nya

FROM " '",tahun,"'"_nilai WHERE Kode_training=kode
) bebaslah), " FROM '",tahun,"'_nilai WHERE Kode_training='",kode,"'"

*catatan:
saya menggunakan 2 parameter, yaitu tahun dan kode.. tinggal tahun aja yg blom bisa...
Quote:


coba ga usah diapit tanda ' (petik satu) gan variabel tahunnya
Quote:

FROM " ' ",tahun," ' "_nilai WHERE Kode_training=kode
) bebaslah), " FROM ' ",tahun," '_nilai WHERE Kode_training=' ",kode," ' "

jadi begini
FROM " ",tahun," "_nilai WHERE Kode_training=kode
) bebaslah), " FROM ' ",tahun," '_nilai WHERE Kode_training=' ",kode," ' "

masih tetep sama, eror-x di Line FROM
Quote:


lah itu yg bawah blm gan..masih ada petik 1 nya
Quote:


itu yang baris atas masih salah mas,,, walaupun q hapus tanda petik 1 (') masih tetep tidak bisa.
coba kayak gini..

Code:
FROM ",tahun,"_nilai WHERE Kode_training=kode
) bebaslah), FROM ",tahun,"_nilai WHERE Kode_training='",kode,"' "
Quote:


udah aku coba sperti tu tadi, tpi tetep g bisa....
Code:

DROP PROCEDURE IF EXISTS `proc_thn_zero`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_thn_zero`(IN `kode` VARCHAR(50),in `tahun` VARCHAR(50))
BEGIN
SET @sqlmode = (SELECT @@sql_mode);
SET @@sql_mode='';
SET @sql =
CONCAT(
"SELECT NoPeserta,Nama,Kode_training,",
(SELECT CONCAT_WS(',',
IF(c0>0,'n1',NULL), IF(c1>0,'n2',NULL), IF(c2>0,'n3',NULL), IF
(c4>0,'n4',NULL),
IF(c5>0,'n5',NULL), IF(c6>0,'n6',NULL), IF(c7>0,'n7',NULL), IF
(c8>0,'n8',NULL),
IF(c9>0,'n9',NULL), IF(c10>0,'n10',NULL), IF(c11>0,'n11',NULL), IF
(c12>0,'n12',NULL),
IF(c13>0,'n13',NULL), IF(c14>0,'n14',NULL), IF(c15>0,'n15',NULL), IF
(c16>0,'n16',NULL),
IF(c17>0,'n17',NULL), IF(c18>0,'n18',NULL), IF(c19>0,'n19',NULL), IF
(c19>0,'n20',NULL),
IF(c21>0,'n21',NULL), IF(c22>0,'n22',NULL), IF(c23>0,'n23',NULL))
#terusin sampai n[n]
FROM (SELECT
SUM(IF(IFNULL(n1,0)=0,0,1)) AS c0, SUM(IF(IFNULL(n2,0)=0,0,1)) AS c1,
SUM(IF(IFNULL(n3,0)=0,0,1)) AS c2, SUM(IF(IFNULL(n4,0)=0,0,1)) AS c3,
SUM(IF(IFNULL(n4,0)=0,0,1)) AS c4, SUM(IF(IFNULL(n5,0)=0,0,1)) AS c5,
SUM(IF(IFNULL(n6,0)=0,0,1)) AS c6, SUM(IF(IFNULL(n7,0)=0,0,1)) AS c7,
SUM(IF(IFNULL(n8,0)=0,0,1)) AS c8, SUM(IF(IFNULL(n9,0)=0,0,1)) AS c9,
SUM(IF(IFNULL(n10,0)=0,0,1)) AS c10, SUM(IF(IFNULL(n11,0)=0,0,1)) AS c11,
SUM(IF(IFNULL(n12,0)=0,0,1)) AS c12, SUM(IF(IFNULL(n13,0)=0,0,1)) AS c13,
SUM(IF(IFNULL(n14,0)=0,0,1)) AS c14, SUM(IF(IFNULL(n15,0)=0,0,1)) AS c15,
SUM(IF(IFNULL(n16,0)=0,0,1)) AS c16, SUM(IF(IFNULL(n17,0)=0,0,1)) AS c17,
SUM(IF(IFNULL(n18,0)=0,0,1)) AS c18, SUM(IF(IFNULL(n19,0)=0,0,1)) AS c19,
SUM(IF(IFNULL(n20,0)=0,0,1)) AS c20, SUM(IF(IFNULL(n21,0)=0,0,1)) AS c21,
SUM(IF(IFNULL(n22,0)=0,0,1)) AS c22, SUM(IF(IFNULL(n23,0)=0,0,1)) AS c23
FROM ",tahun,"_nilai WHERE Kode_training=kode
) bebaslah), FROM ",tahun,"_nilai WHERE Kode_training='",kode,"' "
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SET @@sql_mode=@sqlmode;
END//
DELIMITER ;
stres bacanya gan emoticon-Malu (S)

coba dari yg simpel dulu aja
Code:
SET @sql = CONCAT("SELECT * FROM ",tahun,"_nilai")
Quote:


sesua saran dari mas-x saya coba cek dlu dgn query diatas,, dan hasilnya lancar, tpi blom saya gabung dengan procedure saya yg diatas

Quote:


berarti kesalahan bukan di select tahunnya.. kelihatannya salah concat2 itu, emang ga berwarna ya tulisannya? harusnya kelihatan kalo petiknya kebanyakan ato gimana

untuk mudahnya, coba @sql itu dicetak dulu di layar.. terus hasil jadinya gimana
Quote:


saya blom ngeh dengan maksudx mas sperti apa....

saya coba dengan hanya 1 parameter saja bisa jalan, yaitu parameter KODE
Code:

DROP PROCEDURE IF EXISTS `proc_no_zero`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_no_zero`(IN `kode` VARCHAR(50))
BEGIN
SET @sqlmode = (SELECT @@sql_mode);
SET @@sql_mode='';
SET @sql =
CONCAT(
"SELECT NoPeserta,Nama,Kode_training,",
(SELECT CONCAT_WS(',',
IF(c0>0,'n1',NULL), IF(c1>0,'n2',NULL), IF(c2>0,'n3',NULL), IF

(c4>0,'n4',NULL),
IF(c5>0,'n5',NULL), IF(c6>0,'n6',NULL), IF(c7>0,'n7',NULL), IF

(c8>0,'n8',NULL),
IF(c9>0,'n9',NULL), IF(c10>0,'n10',NULL), IF(c11>0,'n11',NULL), IF

(c12>0,'n12',NULL),
IF(c13>0,'n13',NULL), IF(c14>0,'n14',NULL), IF(c15>0,'n15',NULL), IF

(c16>0,'n16',NULL),
IF(c17>0,'n17',NULL), IF(c18>0,'n18',NULL), IF(c19>0,'n19',NULL), IF

(c19>0,'n20',NULL),
IF(c21>0,'n21',NULL), IF(c22>0,'n22',NULL), IF(c23>0,'n23',NULL))
#terusin sampai n[n]
FROM ( SELECT
SUM(IF(IFNULL(n1,0)=0,0,1)) AS c0, SUM(IF(IFNULL(n2,0)=0,0,1)) AS c1,
SUM(IF(IFNULL(n3,0)=0,0,1)) AS c2, SUM(IF(IFNULL(n4,0)=0,0,1)) AS c3,
SUM(IF(IFNULL(n4,0)=0,0,1)) AS c4, SUM(IF(IFNULL(n5,0)=0,0,1)) AS c5,
SUM(IF(IFNULL(n6,0)=0,0,1)) AS c6, SUM(IF(IFNULL(n7,0)=0,0,1)) AS c7,
SUM(IF(IFNULL(n8,0)=0,0,1)) AS c8, SUM(IF(IFNULL(n9,0)=0,0,1)) AS c9,
SUM(IF(IFNULL(n10,0)=0,0,1)) AS c10, SUM(IF(IFNULL(n11,0)=0,0,1)) AS

c11,
SUM(IF(IFNULL(n12,0)=0,0,1)) AS c12, SUM(IF(IFNULL(n13,0)=0,0,1)) AS

c13,
SUM(IF(IFNULL(n14,0)=0,0,1)) AS c14, SUM(IF(IFNULL(n15,0)=0,0,1)) AS

c15,
SUM(IF(IFNULL(n16,0)=0,0,1)) AS c16, SUM(IF(IFNULL(n17,0)=0,0,1)) AS

c17,
SUM(IF(IFNULL(n18,0)=0,0,1)) AS c18, SUM(IF(IFNULL(n19,0)=0,0,1)) AS

c19,
SUM(IF(IFNULL(n20,0)=0,0,1)) AS c20, SUM(IF(IFNULL(n21,0)=0,0,1)) AS

c21,
SUM(IF(IFNULL(n22,0)=0,0,1)) AS c22, SUM(IF(IFNULL(n23,0)=0,0,1)) AS c23
#terusin sampai n[n]
FROM 2011_nilai WHERE Kode_training=kode
) bebaslah), " FROM 2011_nilai WHERE Kode_training='",kode,"'"
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SET @@sql_mode=@sqlmode;
END//
DELIMITER ;


nah jika 2 parameter, yaitu parameter KODE n TAHUN :
Code:

DROP PROCEDURE IF EXISTS `proc_thn_zero`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_thn_zero`(in `tahun` VARCHAR(50),IN `kode` VARCHAR(50))
BEGIN
SET @sqlmode = (SELECT @@sql_mode);
SET @@sql_mode='';
#set @coba=tahun+_nilai;
SET @sql =
CONCAT(
"SELECT NoPeserta,Nama,Kode_training,",
(SELECT CONCAT_WS(',',
IF(c0>0,'n1',NULL), IF(c1>0,'n2',NULL), IF(c2>0,'n3',NULL), IF
(c4>0,'n4',NULL),
IF(c5>0,'n5',NULL), IF(c6>0,'n6',NULL), IF(c7>0,'n7',NULL), IF
(c8>0,'n8',NULL),
IF(c9>0,'n9',NULL), IF(c10>0,'n10',NULL), IF(c11>0,'n11',NULL), IF
(c12>0,'n12',NULL),
IF(c13>0,'n13',NULL), IF(c14>0,'n14',NULL), IF(c15>0,'n15',NULL), IF
(c16>0,'n16',NULL),
IF(c17>0,'n17',NULL), IF(c18>0,'n18',NULL), IF(c19>0,'n19',NULL), IF
(c19>0,'n20',NULL),
IF(c21>0,'n21',NULL), IF(c22>0,'n22',NULL), IF(c23>0,'n23',NULL))
#terusin sampai n[n]
FROM (SELECT
SUM(IF(IFNULL(n1,0)=0,0,1)) AS c0, SUM(IF(IFNULL(n2,0)=0,0,1)) AS c1,
SUM(IF(IFNULL(n3,0)=0,0,1)) AS c2, SUM(IF(IFNULL(n4,0)=0,0,1)) AS c3,
SUM(IF(IFNULL(n4,0)=0,0,1)) AS c4, SUM(IF(IFNULL(n5,0)=0,0,1)) AS c5,
SUM(IF(IFNULL(n6,0)=0,0,1)) AS c6, SUM(IF(IFNULL(n7,0)=0,0,1)) AS c7,
SUM(IF(IFNULL(n8,0)=0,0,1)) AS c8, SUM(IF(IFNULL(n9,0)=0,0,1)) AS c9,
SUM(IF(IFNULL(n10,0)=0,0,1)) AS c10, SUM(IF(IFNULL(n11,0)=0,0,1)) AS c11,
SUM(IF(IFNULL(n12,0)=0,0,1)) AS c12, SUM(IF(IFNULL(n13,0)=0,0,1)) AS c13,
SUM(IF(IFNULL(n14,0)=0,0,1)) AS c14, SUM(IF(IFNULL(n15,0)=0,0,1)) AS c15,
SUM(IF(IFNULL(n16,0)=0,0,1)) AS c16, SUM(IF(IFNULL(n17,0)=0,0,1)) AS c17,
SUM(IF(IFNULL(n18,0)=0,0,1)) AS c18, SUM(IF(IFNULL(n19,0)=0,0,1)) AS c19,
SUM(IF(IFNULL(n20,0)=0,0,1)) AS c20, SUM(IF(IFNULL(n21,0)=0,0,1)) AS c21, ------------->> eror-x menunjuk di LINE ini
SUM(IF(IFNULL(n22,0)=0,0,1)) AS c22, SUM(IF(IFNULL(n23,0)=0,0,1)) AS c23
FROM ",tahun,"_nilai WHERE Kode_training=kode
) bebaslah), "FROM ' ",tahun," '_nilai WHERE Kode_training=' ",kode," ' "
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SET @@sql_mode=@sqlmode;
END//
DELIMITER ;

Code:
  SUM(IF(IFNULL(n20,0)=0,0,1)) AS c20, SUM(IF(IFNULL(n21,0)=0,0,1)) AS c21,         ------------->> eror-x menunjuk di LINE ini

SUM(IF(IFNULL(n22,0)=0,0,1)) AS c22, SUM(IF(IFNULL(n23,0)=0,0,1)) AS c23

FROM ",tahun,"_nilai WHERE Kode_training=kode

) bebaslah),[B] "FROM ' ",tahun," '_nilai [/B]WHERE Kode_training=' ",kode," ' "


itu kok masih ada petik satunya? coba dihapus

maksud saya apakah variable @sql itu tidak bisa dicetak di layar hasil jadinya? kalo di oracle pake dbms output bla bla.. kalo punya agan ane kurang ngerti emoticon-Malu (S)
Quote:



query diatas cuman contoh aja, tpi realitas-x udah saya rubah n ttp tidak bisa.... waduh saya ngertinya cuman itu doank bang... mohon saranx..
Quote:


coba gan query yang bagian ini diganti dulu deh
Code:

FROM ",tahun,"_nilai WHERE Kode_training=kode

) bebaslah), "FROM ' ",tahun," '_nilai WHERE Kode_training=' ",kode," ' "

);


jadi

Code:

FROM 2011_nilai WHERE Kode_training=kode

) bebaslah), "FROM ' ",tahun," '_nilai WHERE Kode_training=' ",kode," ' "

);


soalnya ane agak penasaran sama line FROM yang itu emoticon-Ngakak