DSXchange

Posted: **Sat Feb 03, 2007 6:52 am**

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

Posted: **Sat Feb 03, 2007 8:25 am**

'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?

Posted: **Sat Feb 03, 2007 10:50 am**

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.

DSXchange

pipe function to be implemented

pipe function to be implemented