469,603 Members | 2,049 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,603 developers. It's quick & easy.

Pass variable into View?

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_FPS_AT_DATE ( GLOBAL_FP_ID,
GLOBAL_FP_PRICE, GLOBAL_FP_COMMENT, GLOBAL_FP_TBU, GLOBAL_FP_DISC_CAT,
GLOBAL_FP_VOL_DISC, GLOBAL_FP_PARTNER_PRICE, GLOBAL_FP_LIST_PRICE,
GLOBAL_FP_MSCAT, GLOBAL_FP_WPCAT, GLOBAL_FP_EFF_DATE,
GLOBAL_FP_MOD_WHO, GLOBAL_FP_MOD_DATE )
AS
Select tblA.* From (
Select GLOBAL_FP_ID, GLOBAL_FP_PRICE, GLOBAL_FP_COMMENT,
GLOBAL_FP_TBU, GLOBAL_FP_DISC_CAT, GLOBAL_FP_VOL_DISC,
GLOBAL_FP_PARTNER_PRICE, GLOBAL_FP_LIST_PRICE, GLOBAL_FP_MSCAT,
GLOBAL_FP_WPCAT, GLOBAL_FP_EFF_DATE, GLOBAL_FP_MOD_WHO,
GLOBAL_FP_MOD_DATE
From cgppr_global_fps
UNION ALL
Select GLOBAL_FP_ID, GLOBAL_FP_PRICE, GLOBAL_FP_COMMENT,
GLOBAL_FP_TBU, GLOBAL_FP_DISC_CAT, GLOBAL_FP_VOL_DISC,
GLOBAL_FP_PARTNER_PRICE, GLOBAL_FP_LIST_PRICE, GLOBAL_FP_MSCAT,
GLOBAL_FP_WPCAT, GLOBAL_FP_EFF_DATE, GLOBAL_FP_MOD_WHO,
GLOBAL_FP_MOD_DATE
From cgppr_global_fps
) tblA,
(
Select GLOBAL_FP_ID, Max(GLOBAL_FP_MOD_DATE) GLOBAL_FP_MOD_DATE
From
(
Select GLOBAL_FP_ID, GLOBAL_FP_MOD_DATE
From cgppr_global_fps
UNION
Select GLOBAL_FP_ID, GLOBAL_FP_MOD_DATE
From cgppr_h_global_fps
)
Where GLOBAL_FP_MOD_DATE < 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;
Jul 19 '05 #1
1 6396
Hello, Mark,

near 09:11 19-Jul from zz******@hotmail.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.
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by HH | last post: by
7 posts views Thread by Zlatko Matić | last post: by
8 posts views Thread by Blue Ocean | last post: by
reply views Thread by weiwei | last post: by
1 post views Thread by Mark | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.