pipe function to be implemented

[srini.dw]

nv_year_month TIME_DT_D.MONTH_INTLGNT_KEY%TYPE

SELECT
A.PERSON_KEY,
A.EFF_DT
FROM
DBA.EVENT_F A,
DBA.TIME_DT_D F,
DBA.EVENT_D D
WHERE
A.TIME_DT_INTLGNT_KEY = F.TIME_DT_INTLGNT_KEY AND
F.CAL_DAY_DT <= SYSDATE AND
F.MONTH_INTLGNT_KEY <= ' || nv_year_month || '


I have above SQL query in package in Oracle.
Iam using Oracle for the above query in DataStage PX 7.5,
Iam supposed to execute the above query and nv_year_month is a parameter,
How will i define this in the query or somewhere else.

Thanks
Srini

[chulett]

'In a package'? So, this is in a Stored Procedure that you need to call and one of the (or the only) parameters you need to pass it is this nv_year_month value?

Is there something more to it than using the Stored Procedure stage to call it and pass in whatever value(s) it needs?

[DSguru2B]

Run the same query, only the sql query in your database stage and make the nv_year_month as a parameter.

Code: Select all

SELECT 
A.PERSON_KEY, 
A.EFF_DT 
FROM 
DBA.EVENT_F A, 
DBA.TIME_DT_D F, 
DBA.EVENT_D D 
WHERE 
A.TIME_DT_INTLGNT_KEY = F.TIME_DT_INTLGNT_KEY AND 
F.CAL_DAY_DT <= SYSDATE AND 
F.MONTH_INTLGNT_KEY <= ' #nv_year_month# '

This will get all your data. What you want to do with this, is the missing part from the stored proc. which is why Craig requested for more details.