DSXchange

Hi Everyone

Is it possible to have a dynamic variable name? I ask as I have a particular job that must use several fields in order to determine a customer SAP grouping code. My problem is as such:

The source system is worldwide, I receive a flat file which will be same for every country. Whilst the system is worldwide there is no real control over data entry so each country has implemented its own rule for internal numbering ie the rules that would govern the SAP code for France will not be the same as the rules that would govern the code for the US. Eventually there could be upwards of 60 countries.

I have one universal job for processing the incoming files and producing an output, and I would, if possible, like to keep it that way. I have a routine being used in the transform stage that calls several fields to determine the appropriate group code but as more countries come on line I think that it would become unmanageable, so my thought was to try and have several routines (instead of several jobs) each with a country prefix so it would be called on the basis of the country being processed at the time.

Is this possible - or is there an easier way of implementing such a raft of rules that I have totally missed?

Any help that you could provide would be greatly appreciated.

Sorry one thing i forgot to mention was that I have tried to implement a hash file lookup but it didn't work as the primary field used is the account number, or to be precise the account number format. In the routine at present I use the Match function on the account number as part of the determination eg Match Account "2A3N" = SAP code

You can store the source in a db table and pass the 'where' clause of the extraction SQL as a job parameter.

Alternatively write your master job to contain a list of code and hold your validation rules in a separate table which will dictate the actions and its seriousness / importance with relation to that particular recordset.

Both the above solution have been implemented by me in different customer sites and am sure that both will work.

Alternatively write your master job to contain a list of code and hold your validation rules in a separate table which will dictate the actions and its seriousness / importance with relation to that particular recordset.

Thanks for that Sainath.

The first option you mention won't really work as I don't have access to a db table. Every input is a text file and there is very little storage of information other than hash files used as lookups.

However could you expand a little on this second option? If I have to store validation rules in a seperate table and call them continuously, I can't see any difference between that and just writing a single routine which contains an extensive CASE statement?

Thanks again

Having an external validation rule will give you the comfort of changing it during runtime and including new additions as you go along. In a CASE statement, you will have to hard-code it.