Code to get Collateral Limit

Hi,

I am new to coding sphere, and have trouble obtaining the value I need. It has to do with executing a select statement, and using this to input into another select statement to eventually get the value and summing them up.

Task
- Getting the Collateral Nominal Value from the Customer ID

Logic
- Do a select statement to query Collateral Rights(CR) Table to get Collateral ID and store it into a variable (a user might have multiple CRs)
- Input this into another select statement to query Collateral Table instead (a collateral right can have multiple Collaterals attached too, and they are suffixed by CR.1.1, CR.1.2, etc, with the last number increasing, and CR.1 being the Collateral Right ID)
- From the result, do a F.READ and get R.COLL<COLL.NOMINAL.VALUE>
- Sum up this value

The current code (which is failing)


**********
GET.COLL.LIMIT:
**********

SEL.CMD = 'SELECT ':FN.COLL.RIGHT
SEL.CMD := ' WITH CUSTOMER.ID EQ ':Y.CUS.ID

CALL EB.READLIST(SEL.CMD,SEL.LIST,'',NO.OF.REC,RET.CODE)

FOR J = 1 TO NO.OF.REC
COLL.RIGHT.ID = SEL.LIST<J>

SEL.CMD = 'SELECT ':FN.COLL
SEL.CMD := ' WITH COLLATERAL.RIGHT EQ ':COLL.RIGHT.ID

CALL EB.READLIST(SEL.CMD,RET.LIST,'',RET.OF.REC,RET.RET.CODE)

FOR K = 1 TO RET.OF.REC
COLL.EXTRA = '1'
COLL.ID = COLL.RIGHT.ID:'.':COLL.EXTRA
COLL.EXTRA++

CALL F.READ(FN.COLL,COLL.ID,R.COLL,F.COLL,COLL.ERR1)
TOT.COLL += R.COLL<COLL.NOMINAL.VALUE>
NEXT K
NEXT J

RETURN

Would appreciate it if there is any help to correct the mistake made, or to have an alternative method instead.

Thank you

Hi
what exactly fails?

Looks like in the first SELECT field name should be CUSTOMER. not CUSTOMER.ID...

Hi VK,

Thank you for your input. When I tried the code, it failed at the second 'CALL EB.READLIST(SEL.CMD,RET.LIST,'',RET.OF.REC,RET.RET.CODE)'

The value returned was null. I wanted to use the output from the first select statement (which is successful) and place it into the next select statement. I am wondering if it is not possible to use select statement in the same GOSUB, since their parameters are named the same way.

I have solved this by querying COLLETRAL table directly using the same
SEL.CMD = 'SELECT ':FN.COLL
SEL.CMD := ' WITH CUSTOMER.ID EQ ':Y.CUS.ID

It can list the collaterals directly instead of searching through Collateral Rights.

All is good now, just that if there is any code that can be shared to achieve the task of selecting a value from a table and inputting it into the next table in the same set of codes would be great. I am also wondering if there is someway to store my queried variables from the first select statement, something which I am weak at

Hi
no code to share but:

just name your variables differently (actually you did it having SELL.LIST and RET.LIST). Your question about GOSUB can be answered as: local variables are visible throughout all subroutine, no matter in which section they are.

See also:

jbc.temenos.com/pages/variables_visibility_and_lifetime.html