Toad World® Forums

Syntactically ambigious code causes script extract to fail...


#1

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)


#2

Marc,
Thanks for the information. I have opened CR 81734 to track this reported issue.