Get a foreign data from a local table (CALL DBR)

Get a foreign data from a local table

When you work with a table, you may need information stored in external tables. To do this, you will use foreign keys in your local table to join an external table. Considering: KEY the foreign key in your local table TABLE_NAME your external table FIELD.ATTRIBUTE the field attribute you want to reach You will use a CALL DBR to make the join:

CALL DBR(“TABLE_NAME”:@FM:FIELD.ATTRIBUTE,KEY,Y.OUTPUT)

With

TABLE_NAME”	Table name
@FM	separator
FIELD.NAME	field name to get
KEY	key identifying the record containing the field to get
Y.OUTPUT	variable containing return field value

Example :

– target : get the deal side BUY/SELL on a derivatives transaction. – context : routine attached to DE.MAPPING, exploiting standard HANDOFF data.

$INSERT I_F.DX.TRANSACTION
Y.VAR = HANDOFF.REC(5)<1,36,1>  ;* clé 
Y.OUTPUT = "" ;* content of return field 
CALL DBR("DX.TRANSACTION":@FM:DX.TX.BUY.SELL,Y.VAR,Y.OUTPUT)
IF (Y.OUTPUT = "BUY")
...

Other example with a local.ref field:

    CUSTOMER.NO = COMI
    Y.DEFAULT.CLEARER = "" 
    DEFAULT.CLEARER = "" 
    CALL DBR("DX.CUSTOMER":FM:DX.CU.LOCAL.REF,CUSTOMER.NO,Y.DEFAULT.CLEARER)
    DEFAULT.CLEARER = Y.DEFAULT.CLEARER< 1, BNK.LRF.DXCU.DEFAULT.CLEARER>
(...)

Remarks:\\  An error message is produced if the CALL DBR is retreiving nothing. It is possible to check this message : “IF (ETEXT) THEN”, “ETEXT = ””,… Don’t forget to include your external table in the loaded libraries ($INSERT I_F.external_table)