Replace function with hash file value

[bunu1977]

Hi,
I am extracting data from oracle and while extracting data I am using an user defined sql statement. This user defined sql statement contains one function which will generate a date value.

I am using the user defined sql which looks like

select .....
from .....
where column1 = fungetvalue(parameter)
and column2 =....;

As the same function I am using in multiple jobs, I want to create a job and write the output value of the function to some temp hash file and use the value in all the other jobs. This will improve the performance.

How can I read the value of the hash file in the source Oracle OCI satge?

[I_Server_Whale]

You cannot read a value from a hashed file using an OCI stage if that is what your question is about.

Of course, the first time you can write the value generated by the function into a hashed file and use this hashed file as a look-up.

Your question is framed very poorly. Please frame your properly to get better answers. It helps you and also everybody.

Thanks,
Naveen.

[gateleys]

If you are using a user-defined SQL in the OCI stage and passing the result to a hashed file, you already have the data available. Now, make use of this data....may be via a lookup, as Naveen suggested, in other jobs. Or, may be I did not understand your question!!

gateleys

[bunu1977]

Hi,
I know that I cant read hash file using oracle oci stage.
I think my question is not clear.

Dastastage works row by row. So if I use one function in a sql query, then this function will execute once for each row. To avoid that I need to execute the function and store the value somewhere (I cant create any temp table and store the date) and in my datastage job, i want to use the value instead of the function. But I want to use it in source stage(i am using oracle oci stage).
Can I do this.

[rasi]

Hi

What you can do is have a job which runs the function and writes it to hashed file or sequential file. In your Oracle job create a new parameter which will be used in place of the function. Before running your Oracle job read the hashed or sequential file which has the value and pass it to the Oracle job parameter.

There is no way inside your Oracle job where you can execute the function and use that value inside your SQL query.

[bunu1977]

Hi Siva,
Thanks for this suggestion. If my function return some alphanumeric characters then I can do this.

But in this scnario the function returns a date value and if I need to use the date value in the where clause then I need to use to_date function.
for example
select..............where
to_date(#parmPMDate#,'YYYYMMDD') =col_value

and I dont want to use to_date here because it will slow my process.
I have tried to run this without using to_date and the job aborted with error. But if I use to_date then my job is completing successfully.

[chulett]

and I dont want to use to_date here because it will slow my process.

What makes you think that? It won't.

[bunu1977]

Thanks a lot