SQR procedure with run-time error on BEGIN-SELECT statement

Question

Hello Stack community,

I have been stuck for the past couple of days with what I initially thought was a relatively simple SQR update.

In a nutshell, procedure Process-Main selects all the employees where COMPANY = 'ABC' and then I need to insert a record into TableA & TableB associated with each employeeID

TableB is empty, so I am simply running an INSERT statement for every empID and the records are being added without any problems.

However, some empIDs might have existing records in TableA and I need to check for that before dong the INSERT into TableA.

Oddly, when i execute this SQR, i'm getting the following error

(SQR 3721) Bad param found on 'BEGIN-SELECT' line;

(SQR 3704) Missing procedure name.
    A.EMPLID

I'm fairly certain this is a syntax issue or i'm just having a brain fart... Does anyone have any advice/suggestions?

Here's my pseudo code

!***************************
begin-procedure Process-Main
!***************************

begin-select distinct

J.DEPTID
J.EMPLID
J.EFFDT

do Insert-TableA-Record
do Insert-TableB-Record

FROM PS_JOB J
WHERE J.COMPANY = 'ABC'

end-select
end-procedure

!****************************
begin-procedure Insert-TableA-Record
!****************************

let $found = 'N'

begin-select
    A.EMPLID

    let $found = 'Y'

    FROM TableA A

    WHERE A.EMPLID=&J.EMPLID
    AND A.PLAN_TYPE='P1234'
end-select

if $found = 'N'
    begin-sql ON-ERROR = Abort-Update
        INSERT INTO TableA (
            EMPLID,
            PLAN_TYPE
        )
        VALUES (
            &J.EMPLID,
            'P1234'
        );
    end-sql
end-if

end-procedure

!****************************
begin-procedure Insert-TableB-Record
!****************************

begin-sql ON-ERROR = Abort-Update

!INSERT INTO TableB STMT HERE... 
!everything runs fine in this procedure

end-sql

end-procedure

You say this is pseudo code - if it were real code, SQR would have a problem with the do Insert-TableA-Record and do Insert-TableB-Record being in column 1. Is this close to real code? A.EMPLID in the begin-select statement in procedure Insert-TableA-Record should be in column 1. Is there any way to see portions of the real code? — cardmagik, Dec 11 '15 at 17:46

score 4 · Answer 1 · answered Dec 11 '15 at 17:57

I found the answer... i knew it was a silly mistake for not being familiar with SQR syntax

I did not indent the "LET" statements inside the SELECT area. Now I know only the SQL statement can appear in the first column.

I have learned another coding lesson and this is the proper syntax:

begin-select
A.EMPLID
    let $found = 'Y'
FROM TableA A
WHERE A.EMPLID=&J.EMPLID
AND A.PLAN_TYPE='P1234'
end-select

score 0 · Answer 2 · answered Dec 11 '15 at 17:53

0

In procedure Insert-TableA-Record, A.Emplid, needs to be in column 1

I got the same error when testing a similar procedure

answered Dec 11 '15 at 17:53

cardmagik

1,698
19
17

SQR procedure with run-time error on BEGIN-SELECT statement

2 Answers2