alexa-tracking

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:Original Posted By SchyZeR
ada cara tulis procedure gan??

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


================================================
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:Original Posted By yanuar1087


================================================
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...


coba ga usah diapit tanda ' (petik satu) gan variabel tahunnya
Quote:Original Posted By SchyZeR


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

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:Original Posted By yanuar1087

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


lah itu yg bawah blm gan..masih ada petik 1 nya
Quote:Original Posted By SchyZeR


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


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:Original Posted By SchyZeR
coba kayak gini..

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


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:Original Posted By SchyZeR
stres bacanya gan emoticon-Malu (S)

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


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:Original Posted By yanuar1087


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



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:Original Posted By SchyZeR


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


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:Original Posted By SchyZeR
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)



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:Original Posted By yanuar1087



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


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