Issue Details (XML | Word | Printable)

Key: CORE-3966
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Adriano dos Santos Fernandes
Reporter: Frank Schlottmann-Goedde
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Creating a stored procedure with an "update or insert" statement with MATCHING fails

Created: 29/Oct/12 10:18 AM   Updated: 29/May/15 12:45 PM
Component/s: None
Affects Version/s: 3.0 Initial
Fix Version/s: 3.0 Alpha 1

Environment: probably all (tested with Windows 7 and Ubuntu 12.04)
Issue Links:
Duplicate
 

QA Status: Done successfully


 Description  « Hide
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 ; ^


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.