Read lookup file into memory using basic routine

[dstest]

Hi,

I have a situation where i need read a file using sequential file stage and then do lookup using sequentil file only (lookup includes range,ANY...)

Is there any way i can read the entire file in memory using basic routine and use that for each incoming record to do a lookup.

Currently i am using the following routine for this but my routine is opening and closing file for each incoming record and is taking more time for the process.

Input Arg : InputFile,Arg_SERVICE_GROUP_CODE,:Arg_SOURCE_SYSTEM_ID,Arg_PLACE_OF_SERVICE_CODE

OPENSEQ InputFile TO File Then
Loop
Until Done Do
READSEQ LINE FROM File Then
SERVICE_GROUP_CODE = FIELD(LINE,",",1)
SOURCE_SYSTEM_ID = FIELD(LINE,",",2)
PLACE_OF_SERVICE_CODE = FIELD(LINE,",",3)
COMMON_CLAIM_TYPE_IND = FIELD(LINE,",",4)
If SERVICE_GROUP_CODE<>"" Then
INPUTSTR=Arg_SERVICE_GROUP_CODE
LKPSTR=SERVICE_GROUP_CODE
END
If SOURCE_SYSTEM_ID<>"" Then
INPUTSTR=INPUTSTR:Arg_SOURCE_SYSTEM_ID
LKPSTR=LKPSTR:SOURCE_SYSTEM_ID
END
If PLACE_OF_SERVICE_CODE<>"" Then
INPUTSTR=INPUTSTR:Arg_PLACE_OF_SERVICE_CODE
LKPSTR=LKPSTR:PLACE_OF_SERVICE_CODE
END
IF INPUTSTR = LKPSTR THEN
Done = @TRUE
Ans = COMMON_CLAIM_TYPE_IND
End Else
Ans = "DDD"
End
End Else
Done = @TRUE
End
Repeat
End Else
Call DSLogFatal("Unable to open file ":InputFile, RoutineName)
END

I am calling this routine in transformer stage and so for each record it is openign the file doing sequential search and closing the file.

Please help is there any way just read the whole file one time and store it stage varaibale and then use the same for each incoming record for comparison.

Thanks

[ArndW]

dstest, please enclose your routine in "code" tags to make it legible. You will need to use a COMMON block to make your routine's data memory resident for the duration of the job, bit it is too much work to try to decode your post to give you concrete suggestions.

[dstest]

OPENSEQ InputFile TO File Then

[ArndW]

Try editing the post and doing the

Code: Select all

....

tags correctly.

[dstest]

Code: Select all

OPENSEQ InputFile TO File Then
         Loop
         Until Done Do
         READSEQ LINE FROM File Then
		    SERVICE_GROUP_CODE		      = FIELD(LINE,",",1)
                SOURCE_SYSTEM_ID		      = FIELD(LINE,",",2)
                PLACE_OF_SERVICE_CODE		= FIELD(LINE,",",3)
               CLAIM_TYPE_IND		= FIELD(LINE,",",17)
                
		   If SERVICE_GROUP_CODE<>"" Then
		      INPUTSTR=Arg_SERVICE_GROUP_CODE
		      LKPSTR=SERVICE_GROUP_CODE
		   END
		  
		   If SOURCE_SYSTEM_ID<>"" Then
		      INPUTSTR=INPUTSTR:Arg_SOURCE_SYSTEM_ID
		      LKPSTR=LKPSTR:SOURCE_SYSTEM_ID
		   END
		  
		   If PLACE_OF_SERVICE_CODE<>"" Then
		      INPUTSTR=INPUTSTR:Arg_PLACE_OF_SERVICE_CODE
		      LKPSTR=LKPSTR:PLACE_OF_SERVICE_CODE
		   END

		   IF INPUTSTR = LKPSTR THEN
		       Done = @TRUE
                   Ans = CLAIM_TYPE_IND
		   End Else
                   Ans = "NONE"
               End
	    End Else
             Done = @TRUE
         End
         Repeat 
   End Else
      Call DSLogFatal("Unable to open file ":InputFile, RoutineName)
   END

[/code]

[ArndW]

You need to use a COMMON block. What are your input arguments and what value do you return to the calling program?

[dstest]

Code: Select all

Input Arg : InputFile,
Arg_SERVICE_GROUP_CODE,
Arg_SOURCE_SYSTEM_ID,
Arg_PLACE_OF_SERVICE_CODE

Return Value :CLAIM_TYPE_IND  

I never used COMMON block,Please give me some sample so that i can develop the remaining code.

[dstest]

Code: Select all

Input Arg : InputFile,
Arg_SERVICE_GROUP_CODE,
Arg_SOURCE_SYSTEM_ID,
Arg_PLACE_OF_SERVICE_CODE

Return Value :CLAIM_TYPE_IND  

I never used COMMON block,Please give me some sample so that i can develop the remaining code.

[ArndW]

Ok, here goes. I just wrote the code, but there are probably some syntactical errors as I don't have access to DataStage at the moment, but the general structure is there:

Code: Select all

   EQUATE RoutineName TO "MyRoutine"
   COMMON/MyProgramCommon/Initialized,KeyArray,ValueArray

   IF NOT(Initialized)
   THEN
      OPENSEQ InputFile TO FilePtr
      THEN
         ***********************************************
         ** Read the whole File into the common Block **
         ***********************************************
         Finished = 0
         READSEQ InLine FROM FilePtr
         THEN
            KeyArray<-1>   = Field(InLine,',',1):'*':Field(InLine,',',2):'*':Field(InLine,',',3)
            ValueArray<-1> = Field(InLine,',',17)
         END
         ELSE Finished = 1
      END
      ELSE
         CALL DSLogFatal("Unable to open file ":InputFile, RoutineName) 
      END ;** of if-then-else the OPEN was successful
      CLOSESEQ FilePtr
      Initialized = 1
   END ;** of if-then-else the COMMON block has been initialized

   **********************************************
   ** Locate the search values in the KeyArray **
   **********************************************
   LOCATE Arg_SERVICE_GROUP_CODE:'*':Arg_SOURCE_SYSTEM_ID:'*':Arg_PLACE_OF_SERVICE_CODE IN KeyArray SETTING Pos ELSE Pos = 0
   IF Pos
   THEN
      CLAIM_TYPE_IND = ValueArray<Pos>
   END
   ELSE
      CLAIM_TYPE_IND = "NONE"
   END ;** of if-then-else the values could be found

[chulett]

Several of the included routines use COMMON, and many are in the 'RowProc' group if I recall correctly. Look at their code as well for example usage.

[dstest]

Thanks for your help.Instead of location the incoming values is there any way i can read the COMMON block line by line and do comparison.

Incoming : 100,200,300

Lookup file : 100,ANY,300,I

Output : 100,200,300,I

Here ANY means match with any incoming value.

Thanks

[ArndW]

One cannot read a COMMON block, and doing it line-by-line doesn't make sense. Did you try to run the routine I posted? What doesn't work, it explains both COMMON block usage and also solves your problem.

[dstest]

It is working if it is direct match,but i have situation to check if the lookup file columns has ANY that means what ever the incoming value it is matched(becuase of ANY) and also i have some other conditions to match.

That is the reason i am asking is there any way i can just read each line from the common block.

[ArndW]

You have two options, either declare 3 keys and use 3 LOCATE statements or loop through the COMMON each time. I'd go with the first option as it is much more efficient.

[chulett]

How you access something isn't changed by the fact that it is in COMMON. So if what you've got in there is a dynamic array, you can certainly iterate thru the elements there just like you would any time / place else. Etc.