I'd appreciate some help on this topic since I am making my first steps into PL/SQL:
When trying to create a function, I get an error: "Table or view does not exist" for all 5 Select statements (and for the statement in the cursor declaration as well). I am sure all tables exist.
Many thanks in advance.
CREATE OR REPLACE FUNCTION ZINSKALK (vtid IN NUMBER, intart IN INTEGER)
RETURN NUMBER
IS
j INTEGER := 0;
p NUMBER := 1;
q NUMBER := 0;
t NUMBER := 1;
r NUMBER := 0;
lz INTEGER := 0;
modusvn INTEGER := 0;
w1 NUMBER := 0;
w2 NUMBER := 0;
i INTEGER := 0;
TYPE ARRAY_TYP IS VARRAY (200) OF NUMBER (10, 2);
RARRAY ARRAY_TYP;
CURSOR C1
IS
SELECT vtobslpos.anzahl, vtobslpos.betrag
FROM vtobsl, vtobslpos
WHERE vtobsl.sysid = vtobslpos.sysvtobsl
AND vtobsl.sysid IN
(SELECT MAX (vtobsl.sysid)
FROM vtobsl
WHERE vtobsl.sysvt = vtid AND vtobsl.rang = 100)
ORDER BY vtobslpos.rang ASC;
BEGIN
SELECT SUM (vtobslpos.anzahl)
INTO lz
FROM vtobslpos, vtobsl
WHERE vtobslpos.sysvtobsl = vtobsl.sysid
AND vtobsl.sysvt = vtid
AND vtobsl.sysid IN (SELECT MAX (sysid)
FROM vtobsl
WHERE sysvt = vtid AND rang = 100);
SELECT vtobsl.modus
INTO modusvn
FROM vtobsl
WHERE vtobsl.sysid IN (SELECT MAX (sysid)
FROM vtobsl
WHERE sysvt = vtid AND rang = 100);
SELECT bgextern
- sz
+ (CASE intart
WHEN 1 THEN 0
WHEN 2 THEN - (sz2 + disagio)
WHEN 3 THEN (sz2 + disagio - provision)
ELSE 0
END)
INTO W1
FROM vt
WHERE sysid = vtid;
SELECT rw + rsv
INTO w2
FROM vt
WHERE sysid = vtid;
i := (CASE WHEN modusvn = 0 THEN 1 ELSE 0 END);
RARRAY (1) := -w1;
RARRAY (lz + 1) := w2;
FOR x IN C1
LOOP
FOR y IN 1 .. x.anzahl
LOOP
i := i + 1;
RARRAY (i) := RARRAY (i) + x.betrag;
END LOOP;
END LOOP;
WHILE ABS (p) >= 0.001
LOOP
p := 0;
q := 0;
j := 0;
FOR z IN 1 .. lz + 1
LOOP
j := j + 1;
p := p + RARRAY (j) * POWER (t, j - 1);
q := q + (j - 1) * RARRAY (j) * POWER (t, j - 2);
END LOOP;
t := t - p / q;
END LOOP;
r := 1200 / t - 1200;
RETURN r;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;