Pass variable into View?

[Mark]

Hi,

I'm trying to write a view that returns a historical view of records
as of a certain date. The records come from a live and historical
table and I need to return the records as they were in the live table
on various dates. The SQL to do this is no problem but it requires a
date value in a where clause in an inner select (the sql is below for
reference - the date value that needs to be variable is hardcoded as
'01/01/2001'). Can I somehow pass this into a view or is a view the
correct approach?

Thanks,
Mark

CREATE OR REPLACE VIEW CGPPR_GLOBAL_FP S_AT_DATE ( GLOBAL_FP_ID,
GLOBAL_FP_PRICE , GLOBAL_FP_COMME NT, GLOBAL_FP_TBU, GLOBAL_FP_DISC_ CAT,
GLOBAL_FP_VOL_D ISC, GLOBAL_FP_PARTN ER_PRICE, GLOBAL_FP_LIST_ PRICE,
GLOBAL_FP_MSCAT , GLOBAL_FP_WPCAT , GLOBAL_FP_EFF_D ATE,
GLOBAL_FP_MOD_W HO, GLOBAL_FP_MOD_D ATE )
AS
Select tblA.* From (
Select GLOBAL_FP_ID, GLOBAL_FP_PRICE , GLOBAL_FP_COMME NT,
GLOBAL_FP_TBU, GLOBAL_FP_DISC_ CAT, GLOBAL_FP_VOL_D ISC,
GLOBAL_FP_PARTN ER_PRICE, GLOBAL_FP_LIST_ PRICE, GLOBAL_FP_MSCAT ,
GLOBAL_FP_WPCAT , GLOBAL_FP_EFF_D ATE, GLOBAL_FP_MOD_W HO,
GLOBAL_FP_MOD_D ATE
From cgppr_global_fp s
UNION ALL
Select GLOBAL_FP_ID, GLOBAL_FP_PRICE , GLOBAL_FP_COMME NT,
GLOBAL_FP_TBU, GLOBAL_FP_DISC_ CAT, GLOBAL_FP_VOL_D ISC,
GLOBAL_FP_PARTN ER_PRICE, GLOBAL_FP_LIST_ PRICE, GLOBAL_FP_MSCAT ,
GLOBAL_FP_WPCAT , GLOBAL_FP_EFF_D ATE, GLOBAL_FP_MOD_W HO,
GLOBAL_FP_MOD_D ATE
From cgppr_global_fp s
) tblA,
(
Select GLOBAL_FP_ID, Max(GLOBAL_FP_M OD_DATE) GLOBAL_FP_MOD_D ATE
From
(
Select GLOBAL_FP_ID, GLOBAL_FP_MOD_D ATE
From cgppr_global_fp s
UNION
Select GLOBAL_FP_ID, GLOBAL_FP_MOD_D ATE
From cgppr_h_global_ fps
)
Where GLOBAL_FP_MOD_D ATE < to_date('01/01/2001', 'dd/mm/YY')
Group By GLOBAL_FP_ID
) tblB
Where tblA.GLOBAL_FP_ ID = tblB.GLOBAL_FP_ ID
And tblA.GLOBAL_FP_ MOD_DATE = tblB.GLOBAL_FP_ MOD_DATE
WITH READ ONLY;

[Wit Serdakovskij]

Re: Pass variable into View?

Hello, Mark,

near 09:11 19-Jul from zzzzzz45@hotmai l.com:

Hi,
>
I'm trying to write a view that returns a historical view of records
as of a certain date. The records come from a live and historical
table and I need to return the records as they were in the live table
on various dates. The SQL to do this is no problem but it requires a
date value in a where clause in an inner select (the sql is below for
reference - the date value that needs to be variable is hardcoded as
'01/01/2001'). Can I somehow pass this into a view or is a view the
correct approach?

Try to use package variable (or user-defined function) in view. Set the
value before using view.

Thanks,
Mark

[...]

--
wbr,
Wit.