SQL Error Column 'Inposting' not found
Hint Ref: 020911160002
Hint Date: 16/11/2009
Hint Details:
ISSUE:
When running an XML job which makes use of the IQac Document generation stored procedures in the database, a Customer reported that the system displayed the error 'Column Inposting Not Found'. Running the same job on an exact copy of this database in Demo, did not generate the error.
PROBLEM:
Prior to Sybase 9 there was no way to determine if a Memory Viable existed or not, before assigning it a new value. This meant that the only way to make this check was to try assigning the variable a value and if this generated an error, because the variable did not exist, trapping this error and using this trap to create it anew before following on and giving it the value.
There appears to be a number of issues with this approach in that if the variable SHOULD exist, but for some reason it is not possible to assign it a value, then the true error is never displayed because the exception clause is automatically executed which suppresses the error. Then it simply generates a new error when the assignment is tried again.
From Sybase 9 onwards, this issue has been resolved with the introduction of the 'VAREXISTS' function.
SOLUTION:
Update the existing Stored Procedure 'spaceman.IQACSetUpVariables' with the one below.
Interestingly, to date, this has solved the 'Inposting' error.
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)
(Space Manager Support Team: this update can be found in "L:\SM4WIN\SQL_Functions\IQACSetUpVariables for Sybase 10.txt")
ALTER PROCEDURE spaceman.IQACSetUpVariables
()
/* variables are
checkdate,lastdocid,docclass,lastlineno,journaltotal,allocationtotal,documenttotal,vattotal,vatnominal,
docclass,controlledger,controlnominal,controlaccount,banknominal,bankledger,bankaccount,accountingdate,checkdate,nsign,
uniquekey,inposting
*/
begin
IF VAREXISTS( 'checkdate' ) = 0 THEN
CREATE VARIABLE checkdate date;
END IF;
IF VAREXISTS( 'lastdocid' ) = 0 THEN
CREATE VARIABLE lastdocid char(12);
END IF;
IF VAREXISTS( 'docclass' ) = 0 THEN
CREATE VARIABLE docclass char(20);
END IF;
IF VAREXISTS( 'lastlineno' ) = 0 THEN
CREATE VARIABLE lastlineno tinyint;
END IF;
IF VAREXISTS( 'journaltotal' ) = 0 THEN
CREATE VARIABLE journaltotal double;
END IF;
IF VAREXISTS( 'allocationtotal' ) = 0 THEN
CREATE VARIABLE allocationtotal double;
END IF;
IF VAREXISTS( 'documenttotal' ) = 0 THEN
CREATE VARIABLE documenttotal double;
END IF;
IF VAREXISTS( 'vattotal' ) = 0 THEN
CREATE VARIABLE vattotal double;
END IF;
IF VAREXISTS( 'vatnominal' ) = 0 THEN
CREATE VARIABLE vatnominal char(20);
END IF;
IF VAREXISTS( 'docclass' ) = 0 THEN
CREATE VARIABLE docclass char(20);
END IF;
IF VAREXISTS( 'controlledger' ) = 0 THEN
CREATE VARIABLE controlledger char(20);
END IF;
IF VAREXISTS( 'controlnominal' ) = 0 THEN
CREATE VARIABLE controlnominal char(20);
END IF;
IF VAREXISTS( 'controlaccount' ) = 0 THEN
CREATE VARIABLE controlaccount char(20);
END IF;
IF VAREXISTS( 'banknominal' ) = 0 THEN
CREATE VARIABLE banknominal char(20);
END IF;
IF VAREXISTS( 'bankledger' ) = 0 THEN
CREATE VARIABLE bankledger char(20);
END IF;
IF VAREXISTS( 'bankaccount' ) = 0 THEN
CREATE VARIABLE bankaccount char(20);
END IF;
IF VAREXISTS( 'accountingdate' ) = 0 THEN
CREATE VARIABLE accountingdate date;
END IF;
IF VAREXISTS( 'checkdate' ) = 0 THEN
CREATE VARIABLE checkdate date;
END IF;
IF VAREXISTS( 'nsign' ) = 0 THEN
CREATE VARIABLE nsign double;
END IF;
IF VAREXISTS( 'uniquekey' ) = 0 THEN
CREATE VARIABLE uniquekey smallint;
END IF;
IF VAREXISTS( 'inposting' ) = 0 THEN
CREATE VARIABLE inposting tinyint;
END IF;
end