Sequential file problem

sourabhverma · Post by **sourabhverma** » Sat Apr 21, 2007 5:00 am

Hi Everybody,

I have one seq file of 200-250 records, and one of the columns is time column having different values like '14:58:59'. I also have one oracle table with three records like
Col1 Col2
15:00:00 PM, A
17:00:00 PM, B
19:00:00 PM, C

Now i want to compare every time field of file with the time field of oracle table such that :

if File.Time > 15:00:00 And File.Time < 17:00:00 then A
if File.Time > 17:00:00 And File.Time < 19:00:00 then B
if File.Time > 19:00:00 then C

For this i have wrote one transformation routine where i am taking all three records of table in one seq file and in routine reading that file and putting Col1 values in one array & Col2 values in second array.
Then for every incoming time field i am comparing it with Array 1 values & returning corresponding Array2 values.
Is this right way or some other ways are also possible.

Any solution will be appreciable.

chulett · Post by **chulett** » Sat Apr 21, 2007 6:39 am

Don't see a 'sequential file problem' here.

There's no 'right' way and there's always other ways possible. To me, the questions are: does this technique work for you? Is it efficient or at least efficient enough to not jeopardize your SLA? If so, then it's as right as any other solution.

sourabhverma · Post by **sourabhverma** » Sat Apr 21, 2007 7:35 am

Sorry for mentioning the subject as "sequential file problem". actually i was not getting the exact sub for the same.
As far as your ques are concerned, i am getting the desired results through my approach but i was looking for some other ways which can be more efficient & simple.

chulett · Post by **chulett** » Sat Apr 21, 2007 8:20 am

Here is a techinque from Ken Bland that I've used to great effect. Nutshell: a single cached hashed file record with 'arrays' like you are using to do your range check via the Locate function.

The explanation is a little long, but the basics are pretty easy to grasp. You could short-cut some of the setup tasks since you've got such a small volume of data to 'rollup' - the end result would be very efficient.

ray.wurlod · Post by **ray.wurlod** » Sat Apr 21, 2007 3:34 pm

For only three distinct values I would not bother with the Oracle table at all, unless the time ranges were likely to change - I'd just use a simple If..Then..Else construct.