Skip site navigation (1) Skip section navigation (2)

why oh why procedure

From: "dedy" <slackman(at)unika(dot)ac(dot)id>
To: pgsql-admin(at)postgresql(dot)org
Subject: why oh why procedure
Date: 2005-04-29 00:48:07
Message-ID: 20050429004807.25402.qmail@mail.unika.ac.id (view raw or flat)
Thread:
Lists: pgsql-admin
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  | `. `
  |/`. `-.     `._)
     / .-.\		
     \ (  `\
      `.\ 


Responses

pgsql-admin by date

Next:From: Enrico WeigeltDate: 2005-04-29 02:18:34
Subject: Re: Database Encryption (now required by law in Italy)
Previous:From: akanksha kulkarniDate: 2005-04-28 17:38:11
Subject: unsubscribe

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group