Hi all,
sorry, that I didn't give all.

Am Montag, 2. August 2004 08:56 schrieb Helen Borrie:

> At 08:42 AM 2/08/2004 +0200, you wrote:
> > > No, not at all. But SQL error -104 is a very broad "parent" for dozens
> > > of language validity errors. Can you catch the gdscode? - that's the
> > > 9-digit error code.
> >
> >335544569
>
> That's a DSQL error, and there should have been a text message with it.

Token unknown - line 1, char 45 1.
STATEMENT: TIBOInternalDataset:
<TIB_Session>.<TIBQuery>.<TIBOInternalDataset>.

> Could you provide your SP definition and *also* the statement that you are
> using in your client app to submit the DDL request?

The definition is at the end of this message. At this time I am still testing
it from DBWorkbench with the debugger. The debugger tells, that the error is
at line 130 (IF (ABMELDD IS NULL) THEN).

>
> > > Where did the database come from?
> >
> >Firebird 1.0
> >
> > > Could it be an ODS 9 database?
> >
> >10.1
>
> If it's an ODS 10.1 database, that might be the source of problems if you
> are trying to do this on a Firebird 1.0 server, or you are using a Fb 1.0
> client with a Fb 1.5 server. Firebird 1.0 databases are ODS 10, not
> 10.1. In this case, the thing that prompts the DSQL error is anyone's
> guess. It might be thrown, for example, when a Fb 1.0 server finds indexes
> on a metadata table that it doesn't think ought to be there. (Indexing on
> metadata tables was what prompted the ODS version stepping up at Fb 1.5).

We use only 1.5 server and clients. And I think, that DBWorkbench shoudn't
have troubles with this.

>
> No answers yet, only questions...
>
> /heLen

Thanks for all,

Walter.

SET TERM ^^ ;
CREATE PROCEDURE P_GETAENDLIST (
DIENST SmallInt,
VEREIN SmallInt)
AS
/*
Procedure: GETAENDLIST

Author : Walter Neumann
Date : 18. 5. 2004
Purpose : Daten für Änderungslisten generieren
Params
------
<param> : <purpose>
*/
DECLARE VARIABLE PID INTEGER;
DECLARE VARIABLE TDAT DATE;
DECLARE VARIABLE TWO VARCHAR(100);
DECLARE VARIABLE TMELDDAT TIMESTAMP;
DECLARE VARIABLE TAENDDAT TIMESTAMP;
DECLARE VARIABLE BID INTEGER;
DECLARE VARIABLE LEISTID SMALLINT;
DECLARE VARIABLE ABBETR DATE;
DECLARE VARIABLE ABGRU SMALLINT;
DECLARE VARIABLE ABBEM VARCHAR(100);
DECLARE VARIABLE ABMELDD TIMESTAMP;
DECLARE VARIABLE ABMELD CHAR(1);
DECLARE VARIABLE ABMELDK CHAR(1);
DECLARE VARIABLE BISBETR DATE;
DECLARE VARIABLE BISGRU SMALLINT;
DECLARE VARIABLE BISBEM VARCHAR(100);
DECLARE VARIABLE BISMELDD TIMESTAMP;
DECLARE VARIABLE BISMELD CHAR(1);
DECLARE VARIABLE BISMELDK CHAR(1);
DECLARE VARIABLE PAENDD TIMESTAMP;
DECLARE VARIABLE STUND NUMERIC(9,2);
DECLARE VARIABLE ABBETRALT DATE;
DECLARE VARIABLE BISBETRALT DATE;
DECLARE VARIABLE BISMELDDALT TIMESTAMP;
DECLARE VARIABLE BISMELDALT CHAR(1);
DECLARE VARIABLE STUNDALT NUMERIC(9,2);
DECLARE VARIABLE LNR INTEGER;
DECLARE VARIABLE STPKTOLD SMALLINT;
DECLARE VARIABLE GEDRUCKT SMALLINT;
DECLARE VARIABLE DIENST1 SMALLINT;
DECLARE VARIABLE DIENST2 SMALLINT;
DECLARE VARIABLE DIENST3 SMALLINT;
DECLARE VARIABLE DIENST4 SMALLINT;
DECLARE VARIABLE ZSID INTEGER;
DECLARE VARIABLE ZSLEISTID SMALLINT;
DECLARE VARIABLE ZSGR SMALLINT;
DECLARE VARIABLE ZSMELDD TIMESTAMP;
DECLARE VARIABLE SVN CHAR(10);
DECLARE VARIABLE TIT VARCHAR(30);
DECLARE VARIABLE VN VARCHAR(50);
DECLARE VARIABLE NN VARCHAR(50);
declare variable ADRID INTEGER;
DECLARE VARIABLE STRA VARCHAR(50);
DECLARE VARIABLE HNR VARCHAR(20);
DECLARE VARIABLE PLZ VARCHAR(7);
DECLARE VARIABLE ORT VARCHAR(50);
DECLARE VARIABLE STPKT SMALLINT;
DECLARE VARIABLE STPKTBEZ VARCHAR(50);
DECLARE VARIABLE LISTNR INTEGER;
DECLARE VARIABLE AENDDAT DATE;
DECLARE VARIABLE AENDDIENST VARCHAR(50);
DECLARE VARIABLE AENDART VARCHAR(50);
DECLARE VARIABLE AENDGRU VARCHAR(50);
DECLARE VARIABLE AENDBEMERK VARCHAR(100);
DECLARE VARIABLE AENDSTDALT NUMERIC(9,2);
DECLARE VARIABLE AENDSTDNEU NUMERIC(9,2);
DECLARE VARIABLE UPD SMALLINT;
DECLARE VARIABLE NONU TIMESTAMP;
DECLARE VARIABLE NONULL TIMESTAMP;
BEGIN
/* code */
NONULL = CAST('1900-01-01 00:00:01' AS TIMESTAMP);
IF (DIENST > 0) THEN
DIENST1 = DIENST;
ELSE
SELECT F.AENDLISTDIENST1, F.AENDLISTDIENST2, F.AENDLISTDIENST3,
F.AENDLISTDIENST4 FROM FIRMA F
INTO :DIENST1, :DIENST2, :DIENST3, DIENST4;
FOR SELECT P.PERSONENIDINT, P.SVNR, P.TITEL, P.VN, P.NN, P.TODDAT, K.TODWO,
K.MELDDAT, K.AENDDAT,
A.ADRID, A.STRASSE, A.HAUSNR, A.PLZ, A.ORT, KA.KLASTPKT, S.BEZEICHNUNG,
S.LISTNR
FROM PERSONEN P
RIGHT JOIN KLIENT K ON K.KLIENTID = P.PERSONENIDINT
LEFT JOIN ADRESSE A ON A.PERSONENID = P.PERSONENIDINT
LEFT JOIN KLIENTENADRESSE KA ON KA.KLAADRID = A.ADRID
LEFT JOIN STUETZPUNKT S ON S.CODE = KA.KLASTPKT
WHERE ((A.GUELTIGVON IS NULL) OR (A.GUELTIGVON <= 'TODAY')) AND
((A.GUELTIGBIS IS NULL) OR (A.GUELTIGBIS >= 'TODAY')) AND A.ADRESSENTYP = 1
ORDER BY KA.KLASTPKT, A.PLZ, P.NN, P.VN
INTO :PID, :SVN, :TIT, :VN, :NN, :TDAT, :TWO, :TMELDDAT, :TAENDDAT, :ADRID, :STRA, :HNR, :PLZ, :ORT, :STPKT, :STPKTBEZ, :LNR
DO
BEGIN
IF (STPKTOLD IS NULL) THEN
BEGIN
STPKTOLD = STPKT;
LISTNR = LNR + 1;
GEDRUCKT = 0;
END
ELSE IF (STPKT > STPKTOLD) THEN
BEGIN
IF (GEDRUCKT = 1) THEN
UPDATE STUETZPUNKT
SET LISTNR = :LISTNR
WHERE CODE = :STPKTOLD;
STPKTOLD = STPKT;
LISTNR = LNR + 1;
GEDRUCKT = 0;
END
DIENST = DIENST1;
WHILE (NOT(DIENST IS NULL OR DIENST = 0)) DO
BEGIN
FOR SELECT BETREUPLANID, LEISTUNGID, BETREUTAB, ABGRUND,
ABBEMERK, ABMELDDAT, ABMELD, ABMELDKORR,
BETREUTBIS, BISGRUND, BISBEMERK, BISMELDDAT, BISMELD,
BISMELDKORR, AENDDATBP,
SOSTDF + MOSTDF + DISTDF + MISTDF + DOSTDF + FRSTDF + SASTDF
+
SOSTDV + MOSTDV + DISTDV + MISTDV + DOSTDV + FRSTDV + SASTDV
+
SOSTDM + MOSTDM + DISTDM + MISTDM + DOSTDM + FRSTDM + SASTDM
+
SOSTDN + MOSTDN + DISTDN + MISTDN + DOSTDN + FRSTDN + SASTDN
+
SOSTDA + MOSTDA + DISTDA + MISTDA + DOSTDA + FRSTDA + SASTDA
+
SOSTDNT + MOSTDNT + DISTDNT + MISTDNT + DOSTDNT + FRSTDNT +
SASTDNT
FROM BETREUUNGSPLANHP
WHERE KLIENTID = :PID AND VEREINID = :VEREIN AND LEISTUNGID
= :DIENST
AND VERRECHNUNGSKZ = 'S' AND
(((BETREUTAB IS NOT NULL) AND (((ABMELDDAT IS NULL) AND
(ABMELD = 'J')) OR ((AENDDATBP > ABMELDDAT) AND (BETREUTBIS IS NULL) AND
(ABMELDKORR = 'J')))) OR
((BETREUTBIS IS NOT NULL) AND (((BISMELDDAT IS NULL) AND
(BISMELD = 'J')) OR ((AENDDATBP > BISMELDDAT) AND (BISMELDKORR = 'J')))))
ORDER BY LEISTUNGID, BETREUTAB
INTO :BID, :LEISTID, :ABBETR, :ABGRU, :ABBEM, :ABMELDD, :ABMELD, :ABMELDK, :BISBETR, :BISGRU, :BISBEM, :BISMELDD, :BISMELD, :BISMELDK, :PAENDD, :STUND
DO
BEGIN
GEDRUCKT = 1;
UPD = 0;
SELECT BEZEICHNUNGD FROM DIENSTE WHERE CODE = :LEISTID
INTO :AENDDIENST;
IF (ABMELDD IS NULL) THEN /*This is the line, where the
error occured */
NONU = NONULL;
ELSE
NONU = ABMELDD;
IF ((ABBETR > '01.01.1950') AND (ABMELD = 'J') AND
(ABMELDD IS NULL)) THEN
BEGIN
IF (ABMELDD IS NULL) THEN
AENDBEMERK = '';
ELSE
AENDBEMERK = 'Korrektur! ';
BISBETRALT = '01.01.1900';
BISMELDDALT = '01.01.1900';
STUNDALT = 0;
BISMELDALT = '';
SELECT FIRST 1 BETREUTAB, BETREUTBIS, BISMELDDAT,
BISMELD,
SOSTDF + MOSTDF + DISTDF + MISTDF + DOSTDF +
FRSTDF + SASTDF +
SOSTDV + MOSTDV + DISTDV + MISTDV + DOSTDV +
FRSTDV + SASTDV +
SOSTDM + MOSTDM + DISTDM + MISTDM + DOSTDM +
FRSTDM + SASTDM +
SOSTDN + MOSTDN + DISTDN + MISTDN + DOSTDN +
FRSTDN + SASTDN +
SOSTDA + MOSTDA + DISTDA + MISTDA + DOSTDA +
FRSTDA + SASTDA +
SOSTDNT + MOSTDNT + DISTDNT + MISTDNT + DOSTDNT +
FRSTDNT + SASTDNT
FROM BETREUUNGSPLANHP
WHERE KLIENTID = :PID AND VEREINID = :VEREIN AND
LEISTUNGID = :LEISTID AND /*((UNTERGRUPPE = :UNTG) OR (:LEISTID <> 4)) AND*/
BETREUTBIS < :ABBETR
ORDER BY BETREUTAB DESC
INTO :ABBETRALT, :BISBETRALT, :BISMELDDALT, :BISMELDALT, :STUNDALT;
IF ((BISBETRALT = (ABBETR - 1)) AND (BISMELDDALT IS
NULL) AND (BISMELDALT = 'N') AND (STUNDALT <> STUND)) THEN
AENDART = 'Stundenänderung';
ELSE IF (BISBETRALT = '01.01.1900') THEN
AENDART = 'Erster Dienst';
ELSE
AENDART = 'Wiedereinsatz';
AENDDAT = ABBETR;
IF (ABGRU IS NULL) THEN
AENDGRU = '';
ELSE
SELECT BEZEICHNUNG FROM AENDGRUND WHERE CODE
= :ABGRU INTO :AENDGRU;
IF (ABBEM IS NOT NULL) THEN
IF (AENDBEMERK IS NULL) THEN
AENDBEMERK = ABBEM;
ELSE
AENDBEMERK = AENDBEMERK || ABBEM;
AENDSTDALT = STUNDALT;
AENDSTDNEU = STUND;
INSERT INTO AENDLIST (BETRID, SVN, TIT, VN, NN,
STRA, HNR, PLZ, ORT, STPKT, STPKTBEZ, LISTNR, AENDDAT, AENDDIENST, AENDART,
AENDGRU, AENDBEMERK, AENDSTDALT, AENDSTDNEU)
VALUES(:BID, :SVN, :TIT, :VN, :NN, :STRA, :HNR, :PLZ, :ORT, :STPKT, :STPKTBEZ, :LISTNR, :AENDDAT, :AENDDIENST,
:AENDART, :AENDGRU, :AENDBEMERK, :AENDSTDALT, :AENDSTDNEU);
UPD = 1;
END
IF (BISMELDD IS NULL) THEN
NONU = NONULL;
ELSE
NONU = BISMELDD;
IF ((BISBETR > '01.01.1950') AND (BISMELD = 'J') AND
(BISMELD IS NULL)) THEN
BEGIN
AENDDAT = BISBETR;
AENDART = 'Letzter Dienst';
IF (BISGRU IS NULL) THEN
AENDGRU = '';
ELSE
SELECT BEZEICHNUNG FROM AENDGRUND WHERE CODE
= :BISGRU INTO :AENDGRU;
IF (BISMELDD IS NULL) THEN
AENDBEMERK = BISBEM;
ELSE IF (BISBEM IS NULL) THEN
AENDBEMERK = 'Korrektur!';
ELSE
AENDBEMERK = 'Korrektur! ' || BISBEM;
AENDSTDALT = STUND;
AENDSTDNEU = 0;
INSERT INTO AENDLIST (BETRID, SVN, TIT, VN, NN,
STRA, HNR, PLZ, ORT, STPKT, STPKTBEZ, LISTNR, AENDDAT, AENDDIENST, AENDART,
AENDGRU, AENDBEMERK, AENDSTDALT, AENDSTDNEU)
VALUES(:BID, :SVN, :TIT, :VN, :NN, :STRA, :HNR, :PLZ, :ORT, :STPKT, :STPKTBEZ, :LISTNR, :AENDDAT, :AENDDIENST,
:AENDART, :AENDGRU, :AENDBEMERK, :AENDSTDALT, :AENDSTDNEU);
UPD = UPD + 2;
END
IF (UPD = 1) THEN
BEGIN
UPDATE BETREUUNGSPLANHP
SET ABMELDDAT = 'NOW'
WHERE BETREUPLANID = :BID;
END
ELSE IF (UPD = 2) THEN
BEGIN
UPDATE BETREUUNGSPLANHP
SET BISMELDDAT = 'NOW'
WHERE BETREUPLANID = :BID;
END
ELSE IF (UPD = 3) THEN
BEGIN
UPDATE BETREUUNGSPLANHP
SET ABMELDDAT = 'NOW',
BISMELDDAT = 'NOW'
WHERE BETREUPLANID = :BID;
END
END
FOR SELECT Z.ZSID, Z.ZSLEISTUNGID, Z.ZSDATUM, Z.ZSGRUND,
Z.ZSBEMERKUNG, Z.ZSANZAHL, Z.ZSMELDDAT, ZG.BEZEICHNUNG, D.BEZEICHNUNGD
FROM ZUSATZSTUNDEN Z
LEFT JOIN DIENSTE D ON D.CODE = Z.ZSLEISTUNGID
LEFT JOIN ZUSATZSTDGRUND ZG ON ZG.CODE = Z.ZSGRUND
WHERE Z.ZSKLIENTID = :PID AND Z.ZSLEISTUNGID = :DIENST AND
(((Z.ZSMELDDAT IS NULL) AND (Z.ZSMELD = 'J')) OR
((Z.ZSAENDDAT > Z.ZSMELDDAT) AND (Z.ZSMELDKORR = 'J')))
INTO :ZSID, :ZSLEISTID, :AENDDAT, :ZSGR, :AENDBEMERK, :AENDSTDNEU, :ZSMELDD, :AENDGRU, :AENDDIENST
DO
BEGIN
GEDRUCKT = 1;
AENDART = 'Akuter Mehrbedarf';
IF (ZSMELDD IS NOT NULL) THEN
AENDBEMERK = 'Korrektur! ' || AENDBEMERK;
AENDSTDALT = 0;
INSERT INTO AENDLIST (BETRID, SVN, TIT, VN, NN, STRA, HNR,
PLZ, ORT, STPKT, STPKTBEZ, LISTNR, AENDDAT, AENDDIENST, AENDART, AENDGRU,
AENDBEMERK, AENDSTDALT, AENDSTDNEU)
VALUES(:ZSID, :SVN, :TIT, :VN, :NN, :STRA, :HNR, :PLZ, :ORT, :STPKT, :STPKTBEZ, :LISTNR, :AENDDAT, :AENDDIENST,
:AENDART, :AENDGRU, :AENDBEMERK, :AENDSTDALT, :AENDSTDNEU);
UPDATE ZUSATZSTUNDEN
SET ZSMELDDAT = 'NOW'
WHERE ZSID = :ZSID;
END
IF (DIENST = DIENST1) THEN
DIENST = DIENST2;
ELSE IF (DIENST = DIENST2) THEN
DIENST = DIENST3;
ELSE IF (DIENST = DIENST3) THEN
DIENST = DIENST4;
ELSE
DIENST = 0;
END
IF (TMELDDAT IS NULL) THEN
NONU = NONULL;
ELSE
NONU = TMELDDAT;
IF (TDAT > '1.1.1950') THEN
BEGIN
IF (TMELDDAT IS NULL) THEN
BEGIN
GEDRUCKT = 1;
AENDDAT = TDAT;
AENDDIENST = '';
AENDART = 'Tod';
AENDGRU = '';
IF (TMELDDAT IS NULL) THEN
AENDBEMERK = TWO;
ELSE
AENDBEMERK = 'Korrektur! ' || TWO;
AENDSTDALT = 0;
AENDSTDNEU = 0;
INSERT INTO AENDLIST (BETRID, SVN, TIT, VN, NN, STRA, HNR, PLZ,
ORT, STPKT, STPKTBEZ, LISTNR, AENDDAT, AENDDIENST, AENDART, AENDGRU,
AENDBEMERK, AENDSTDALT, AENDSTDNEU)
VALUES(:PID, :SVN, :TIT, :VN, :NN, :STRA, :HNR, :PLZ, :ORT, :STPKT, :STPKTBEZ, :LISTNR, :AENDDAT, :AENDDIENST,
:AENDART, :AENDGRU, :AENDBEMERK, :AENDSTDALT, :AENDSTDNEU);
UPDATE KLIENT
SET MELDDAT = 'NOW'
WHERE KLIENTID = :PID;
END
END
END
end
^^
SET TERM ; ^^