Heyas,
i have the following UDF uploaded using
TOAD (4.7):
CREATE
PROCEDURE
“PCLEARING”.“TEST” (
IN
“VMARKER” VARCHAR(6))
SPECIFIC “TEST”
LANGUAGE SQL
NOT
DETERMINISTIC
NO
EXTERNAL ACTION
MODIFIES SQL DATA
OLD
SAVEPOINT LEVEL
P1: BEGIN
MERGE INTO
PCLEARING.SALDEN S
USING (SELECT
MONAT,DEPOTID,PACKKEY,SUM(ANZAHL)
ANZAHL FROM
(
SELECT
MONAT,DEPOTID,PACKKEY,-ANZAHL
AS
ANZAHL FROM
PCLEARING.AKONTEN WHERE
MONAT=vMARKER
UNION
SELECT
MONAT,DEPOTID,PACKKEY,ANZAHL FROM
PCLEARING.EKONTEN WHERE
MONAT=vMARKER
UNION
SELECT
MONAT,DEPOTID,PACKKEY,ANZAHL FROM
PCLEARING.VORTRAG WHERE
MONAT=vMARKER)
GROUP
BY
MONAT, DEPOTID, PACKKEY) AS
T
ON
(S.DEPOTID=T.DEPOTID
AND
S.PACKKEY=T.PACKKEY
AND
S.MONAT=vMARKER
)
WHEN
MATCHED THEN
UPDATE
SET
S.ANZAHL=COALESCE(S.ANZAHL,**0**)+T.ANZAHL
WHEN
NOT
MATCHED THEN
INSERT
(MONAT,DEPOTID,PACKKEY,ANZAHL)
VALUES
(vMARKER,T.DEPOTID,T.PACKKEY,T.ANZAHL);–
COMMIT;
END;
The script extract (Object Details)
fails in parsing the slightly ambigious code at the inner select/group:
(Note everything is missing after “GROUP
BY;”.
I have to upload this
CREATE
PROCEDURE
“PCLEARING”.“TEST” (
IN
“VMARKER” VARCHAR(6))
SPECIFIC “TEST”
LANGUAGE SQL
NOT
DETERMINISTIC
NO
EXTERNAL ACTION
MODIFIES SQL DATA
OLD
SAVEPOINT LEVEL
P1: BEGIN
MERGE INTO
PCLEARING.SALDEN S
USING (SELECT
MONAT,DEPOTID,PACKKEY,SUM(ANZAHL)
ANZAHL FROM
(
SELECT
MONAT,DEPOTID,PACKKEY,-ANZAHL
AS
ANZAHL FROM
PCLEARING.AKONTEN WHERE
MONAT=vMARKER
UNION
SELECT
MONAT,DEPOTID,PACKKEY,ANZAHL FROM
PCLEARING.EKONTEN WHERE
MONAT=vMARKER
UNION
SELECT
MONAT,DEPOTID,PACKKEY,ANZAHL FROM
PCLEARING.VORTRAG WHERE
MONAT=vMARKER)
F GROUP
BY
MONAT, DEPOTID, PACKKEY) AS
T
ON
(S.DEPOTID=T.DEPOTID
AND
S.PACKKEY=T.PACKKEY
AND
S.MONAT=vMARKER
)
WHEN
MATCHED THEN
UPDATE
SET
S.ANZAHL=COALESCE(S.ANZAHL,**0**)+T.ANZAHL
WHEN
NOT
MATCHED THEN
INSERT
(MONAT,DEPOTID,PACKKEY,ANZAHL)
VALUES
(vMARKER,T.DEPOTID,T.PACKKEY,T.ANZAHL);–
COMMIT;
END;
to get it to work properly (ALIAS after
the inner select).
regards
Marc
IDS Logistik GmbH
Marc Rink
- Software Development Manager -
Saaläckerstrasse 8
63801 Kleinostheim
Tel: +49(6027)40903-13
Fax: +49(6027)40903-9013
MOB: +49(170)9672528
Internet: http://www.ids-logistik.de
E-Mail: marc_rink@ids-logistik.de
Sitz der Gesellschaft: Kleinostheim
Amtsgericht Aschaffenburg - HRB 10253
Geschäftsführer: Dr. Michael Bargl
Vorsitzender des Beirates: Mathias Krage
“Computer games don’t affect kids. I mean if Pac-Man affected us as
kids, we’d all be running around in darkened rooms, munching magic pills
and listening to repetitive electronic music.”
–Kristian Wilson, Nintendo 1989.
att1.dat (38.3 KB)
att1.dat (28.9 KB)