X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 52280533D9 for ; Thu, 28 Apr 2005 22:31:43 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83768-07 for ; Fri, 29 Apr 2005 01:31:35 +0000 (GMT) Received: from mail.unika.ac.id (66.subnet205.astinet.telkom.net.id [203.130.205.66]) by svr1.postgresql.org (Postfix) with SMTP id 52CC252A73 for ; Thu, 28 Apr 2005 22:31:14 -0300 (ADT) Received: (qmail 25403 invoked by uid 507); 29 Apr 2005 07:48:07 +0700 Message-ID: <20050429004807.25402.qmail@mail.unika.ac.id> From: "dedy" To: pgsql-admin@postgresql.org Subject: why oh why procedure Date: Fri, 29 Apr 2005 07:48:07 +0700 Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="utf-8" Content-Transfer-Encoding: 7bit X-Sender: slackman@unika.ac.id X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.375 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, UPPERCASE_75_100 X-Spam-Level: X-Archive-Number: 200504/397 X-Sequence-Number: 17410 Hi all.... I want to create store procedure and the sql is CREATE PROCEDURE CARI_DATA (NIM VARCHAR(10), TAHUNAJARAN VARCHAR(4), KD_MSUJI VARCHAR(2), BEBAN SMALLINT, JENJANG VARCHAR(3)) RETURNS ( KDMK_JUR VARCHAR(12), NM_MKULIAH VARCHAR(25), SKS SMALLINT, BAYAR DOUBLE PRECISION, KELAS VARCHAR(2), JENJANG VARCHAR(3)) AS BEGIN SELECT T_MATAKULIAH.KDMK_JUR, T_MATAKULIAH.NM_MKULIAH, T_MATAKULIAH.SKS, (CAST(T_MATAKULIAH.TARIP AS DOUBLE PRECISION)) AS bayar, KRS_.KELAS, UANG_SKS.JENJANG FROM T_MATAKULIAH INNER JOIN (T_MAHASISWA INNER JOIN KRS_ ON T_MAHASISWA.NIM = KRS_.NIM) ON T_MATAKULIAH.KDMK_PUS = KRS_.KDMK_PUS INNER JOIN UANG_SKS ON T_MAHASISWA.ANGKATAN = UANG_SKS.ANGKATAN WHERE (((KRS_.NIM)= :nim) AND ((KRS_.TAHUNAJARAN)= :tahunajaran)AND ((KRS_.KD_MSUJI)= :kd_msuji) AND ((T_MATAKULIAH.BEBAN)= :beban)) and ((uang_sks.jenjang)= :jenjang) into :KDMK_JUR, :NM_MKULIAH, :SKS, :BAYAR, :KELAS, :JENJANG union SELECT T_MATAKULIAH.KDMK_JUR, T_MATAKULIAH.NM_MKULIAH, T_MATAKULIAH.SKS, (CAST(SKS*UANGSKS*BEBAN/100 AS DOUBLE PRECISION)) AS bayar, KRS_.KELAS, uang_sks.jenjang FROM T_MATAKULIAH INNER JOIN ((UANG_SKS INNER JOIN T_MAHASISWA ON UANG_SKS.ANGKATAN = T_MAHASISWA.ANGKATAN) INNER JOIN KRS_ ON T_MAHASISWA.NIM = KRS_.NIM) ON T_MATAKULIAH.KDMK_PUS = KRS_.KDMK_PUS WHERE (((KRS_.NIM)= :nim) AND ((KRS_.TAHUNAJARAN)= :tahunajaran) AND ((KRS_.KD_MSUJI)= :kd_msuji) AND (T_MATAKULIAH.BEBAN) <> :beban) and ((uang_sks.jenjang)= :jenjang) into :KDMK_JUR, :NM_MKULIAH, :SKS, :BAYAR, :KELAS, :JENJANG union SELECT T_MKKHUSUS.KDMK_JUR, T_MKKHUSUS.NM_MKULIAH,T_MKKHUSUS.SKS, (CAST(BEBANBAYAR*UANGSKS AS DOUBLE PRECISION)) AS bayar, KRS_.KELAS, UANG_SKS.JENJANG FROM (T_MATAKULIAH INNER JOIN ((UANG_SKS INNER JOIN T_MAHASISWA ON UANG_SKS.ANGKATAN = T_MAHASISWA.ANGKATAN) INNER JOIN KRS_ ON T_MAHASISWA.NIM = KRS_.NIM) ON T_MATAKULIAH.KDMK_PUS = KRS_.KDMK_PUS) INNER JOIN T_MKKHUSUS ON T_MATAKULIAH.KDMK_PUS = T_MKKHUSUS.KDMK_PUS WHERE (((KRS_.NIM)= :nim) AND ((KRS_.TAHUNAJARAN)= :tahunajaran) AND ((KRS_.KD_MSUJI)= :kd_msuji)) and ((uang_sks.jenjang)= :jenjang) into :KDMK_JUR, :NM_MKULIAH, :SKS, :BAYAR, :KELAS, :JENJANG; EXIT; END; but every time i run it the error message is that union cannot be execute. is store procedure do not support union?? Thank you Dedy Styawan | . . |L /| . _ . |\ _| \--+._/| . / ||\| Y J ) / |/| ./ J |)'( | ` F`.'/ -<| F __ .-< | / .-'. `. /-. L___ J \ < \ | | O\|.-' _J \ .- \/ O | | \ |F '-F -<_. \ .-' `-' L__ __J _ _. >-' )._. |-' `-|.' /_. \_| F /.- . _.< /' /.' .' `\ /L /' |/ _.-'-\ /'J ___.---'\| |\ .--' V | `. ` |/`. `-. `._) / .-.\ \ ( `\ `.\