Read lookup file into memory using basic routine
Moderators: chulett, rschirm, roy
Read lookup file into memory using basic routine
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
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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Try editing the post and doing the tags correctly.
Code: Select all
....
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
You need to use a COMMON block. What are your input arguments and what value do you return to the calling program?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Code: Select all
Input Arg : InputFile,
Arg_SERVICE_GROUP_CODE,
Arg_SOURCE_SYSTEM_ID,
Arg_PLACE_OF_SERVICE_CODE
Return Value :CLAIM_TYPE_IND
Code: Select all
Input Arg : InputFile,
Arg_SERVICE_GROUP_CODE,
Arg_SOURCE_SYSTEM_ID,
Arg_PLACE_OF_SERVICE_CODE
Return Value :CLAIM_TYPE_IND
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
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
That is the reason i am asking is there any way i can just read each line from the common block.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>