COMMON Usage

[DS_SUPPORT]

We have a dimension for capturing all the dates and the corresponding Surrogate ID values , and previously we were using this lookup and got the SIDs from the Hashed File. And later on, we have written a custom routine to convert the given date to the SID value, as it is more like removing the hyphens between the dates, So we did these things in the BASIC routine, it will convert all valid dates to its SID.

Now we got a requirement, that the date value should be within the range which is there in the dimension, Like if in dimesion we have dates only from 2001 - 2020, then only the dates between them should be converted to SID values else it should throw error.

And , I dont want to Change the number of arguments of the routine as it is used widely across the project. So I want to do the things within the routine without changing the arguments. I can do a select from the Dimension Hashed File for getting the Maximum Date and Minimum Date, But it will be very perfomant as it will open the Hashed File for every row. Is there any way we can store the Maximum Value and Minimum Value from the Dimension through some other routines or some way, and validate that value in the Current Routine.

Kindly , Provide some suggestion on how to achieve this?

[ray.wurlod]

Do you understand how COMMON storage works? In particular are you aware that it persists for the duration of one process?

Examine the Key Management routines for opening a hashed file into a variable in COMMON, so that it need be opened only once, rather than once per row.

If follows, therefore, that you can access variables declared to be in a COMMON area of memory from all calls to a routine from the same process, for example all calls from the same Transformer stage. Even other routines called from the same Transformer stage can have access to the same COMMON area(s) by using compatible declarations.

One way to implement what you discuss, therefore, is to use a sorted dynamic array of dates, and use LOCATE (with a sequencing specification) to search it. The minimum and maximum might be stored in separate variables in the same COMMON area, or are accessible as the first and final elements of the dynamic array.

Calls from a different Transformer stage will not have access to the same COMMON area.