You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
summary: Creating a stored procedure with an "update or insert" statement with returning value fails => Creating a stored procedure with an "update or insert" statement with MATCHING fails
Submitted by: Frank Schlottmann-Goedde (fsg)
Is duplicated by CORE3968
Using the following script in isql against the employee database demonstrates the error
/*
Creating a stored procedure with an "update or insert" statement with returning value
fails with:
Invalid token.
invalid request BLR at offset 364.
context not defined (BLR error).
Error while parsing procedure P_BETEILIGUNG_IU's BLR.
*/
SET TERM ^ ;
create or alter procedure P_BETEILIGUNG_IU (
GID char(36) character set ISO8859_1 collate ISO8859_1,
GID_BETEILIGUNGS_VERHAELTNIS char(36) character set ISO8859_1 collate ISO8859_1,
GID_VRINFO char(36) character set ISO8859_1 collate ISO8859_1,
GID_AGNR char(36) character set ISO8859_1 collate ISO8859_1,
ANTEIL numeric(18,3),
ORDERNR integer,
GUELTIG_AB date,
GUELTIG_BIS date,
FUEHRENDE_PRAEMIE char(1) character set ISO8859_1 collate ISO8859_1,
FUEHRENDE_COURTAGE char(1) character set ISO8859_1 collate ISO8859_1,
GID_ZAHLART char(36) character set ISO8859_1 collate ISO8859_1,
GID_POLICEN_BETEILIGTE char(36) character set ISO8859_1 collate ISO8859_1,
VSNRVR varchar(36) character set ISO8859_1 collate ISO8859_1,
VSNR varchar(36) character set ISO8859_1 collate ISO8859_1)
as
declare variable VON date;
declare variable BIS date;
declare variable GID_POLICEN char(36);
declare variable GID_POLICEN_OR_DETAIL char(36);
declare variable ALLE_SOLLST_NEUMACHEN char(1);
declare variable GID_BETEILIGUNG char(36);
declare variable D char(1);
declare variable po_number char(8);
declare variable cust_no integer;
declare variable sales_rep smallint;
declare variable order_status varchar(7);
declare variable order_date timestamp;
declare variable ship_date timestamp;
declare variable date_needed timestamp;
declare variable paid char(1);
declare variable qty_ordered integer;
declare variable total_value decimal(9,2);
declare variable discount float;
declare variable item_type varchar(12);
declare variable aged numeric(18,9);
begin
if (ORDERNR is null) then
begin
select max(B.QTY_ORDERED) + 1
from SALES B
where B.item_type = :GID_BETEILIGUNGS_VERHAELTNIS
into :ORDERNR;
end
update or insert into SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID,
QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE)
values (:PO_NUMBER, :CUST_NO, :SALES_REP, :ORDER_STATUS, :ORDER_DATE, :SHIP_DATE, :DATE_NEEDED, :PAID, :QTY_ORDERED,
:TOTAL_VALUE, :DISCOUNT, :ITEM_TYPE)
matching (PO_NUMBER)
returning (PO_NUMBER)
into :PO_NUMBER;
end^
SET TERM ; ^
Commits: 62ac6e3
The text was updated successfully, but these errors were encountered: