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
Using the following script in isql against the employee database demonstrates the error
/*
Creating a stored procedure with one or more "update or insert" statement
fails with:
Statement failed, SQLSTATE = 42S22
invalid request BLR at offset 193
-column EMP_NO is not defined in table PROJECT
-Error while parsing procedure EMPIU's BLR
*/
SET TERM ^ ;
create or alter procedure EMPIU (
EMP_NO smallint,
FIRST_NAME varchar(15),
LAST_NAME varchar(20),
PHONE_EXT varchar(4),
HIRE_DATE timestamp,
DEPT_NO char(3),
JOB_CODE varchar(5),
JOB_GRADE smallint,
JOB_COUNTRY varchar(15),
SALARY numeric(10,2),
PROJ_ID type of column PROJECT.PROJ_ID)
as
begin
select first 1 p.proj_id from project p where p.proj_id=:proj_id into :proj_id;
update or insert into EMPLOYEE_PROJECT (EMP_NO, PROJ_ID)
values (:EMP_NO, :PROJ_ID)
matching (EMP_NO, PROJ_ID);
Submitted by: Frank Schlottmann-Goedde (fsg)
Duplicates CORE3966
Using the following script in isql against the employee database demonstrates the error
/*
Creating a stored procedure with one or more "update or insert" statement
fails with:
Statement failed, SQLSTATE = 42S22
invalid request BLR at offset 193
-column EMP_NO is not defined in table PROJECT
-Error while parsing procedure EMPIU's BLR
*/
SET TERM ^ ;
create or alter procedure EMPIU (
EMP_NO smallint,
FIRST_NAME varchar(15),
LAST_NAME varchar(20),
PHONE_EXT varchar(4),
HIRE_DATE timestamp,
DEPT_NO char(3),
JOB_CODE varchar(5),
JOB_GRADE smallint,
JOB_COUNTRY varchar(15),
SALARY numeric(10,2),
PROJ_ID type of column PROJECT.PROJ_ID)
as
begin
select first 1 p.proj_id from project p where p.proj_id=:proj_id into :proj_id;
update or insert into EMPLOYEE_PROJECT (EMP_NO, PROJ_ID)
values (:EMP_NO, :PROJ_ID)
matching (EMP_NO, PROJ_ID);
update or insert into EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE, DEPT_NO, JOB_CODE, JOB_GRADE,
JOB_COUNTRY, SALARY)
values (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE, :DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY,
:SALARY)
matching (EMP_NO);
end^
SET TERM ; ^
The text was updated successfully, but these errors were encountered: