pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedwhy oh why procedure
2+ messages / 2 participants
[nested] [flat]
* why oh why procedure
@ 2005-04-29 00:48 dedy <slackman@unika.ac.id>
2005-04-29 02:40 ` Re: why oh why procedure Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 2+ messages in thread
From: dedy @ 2005-04-29 00:48 UTC (permalink / raw)
To: 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 | `. `
|/`. `-. `._)
/ .-.\
\ ( `\
`.\
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: why oh why procedure
2005-04-29 00:48 why oh why procedure dedy <slackman@unika.ac.id>
@ 2005-04-29 02:40 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-04-29 02:40 UTC (permalink / raw)
To: dedy <slackman@unika.ac.id>; +Cc: pgsql-admin
On Fri, Apr 29, 2005 at 07:48:07AM +0700, dedy wrote:
>
> I want to create store procedure and the sql is
>
> CREATE PROCEDURE CARI_DATA
In PostgreSQL you'll need to use CREATE FUNCTION. See also the
PL/pgSQL documentation to learn its syntax and capabilities. Here
are links to documentation for the latest version:
http://www.postgresql.org/docs/8.0/interactive/sql-createfunction.html
http://www.postgresql.org/docs/8.0/interactive/plpgsql.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-04-29 02:40 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-29 00:48 why oh why procedure dedy <slackman@unika.ac.id>
2005-04-29 02:40 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox