change view into stored proc w/ parameter - HOW?

[LMCRYER]

I am using Oracle81. I have the following view which pulls in all of the data that we need:

CREATE OR REPLACE FORCE VIEW MRPOWNER.CYCLE_COUNT_3
(TRANSACTION_CODE, SUBCODE, PART_NBR, PART_DESC, PART_CLS4,
ABC_CODE, LAST_COUNT_DATE, STATUS, CINCOM_QTY_ON_HAND)
AS
SELECT
'INVADJ' AS transaction_code,
'B' AS subcode,
vw_part.part_nbr,
vw_part.part_desc,
vw_part.part_cls4,
vw_part.abc_code,
vw_invd.last_count_date,
vw_invd.status,
vw_invd.CINCOM_QTY_ON_HAND
FROM
CSIOWNER.part_elk vw_part,
mrpowner.vw_invd_cycle_061004 vw_invd
WHERE
vw_invd.part_nbr = vw_part.part_nbr;

Now what I need to do is make this into a procedure so that it can have a parameter. I would like to be able to query it by using Crystal Reports and pass the value for the parameter back to the procedure to further define the WHERE statement like this:

WHERE (LAST_COUNT_DATE < ?)

I know how to do this on the SQL side but dont know how to make this work in Oracle. Can someone please guide me?




LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[SantaMufasa]

Lisa,

Since I don't have your actual tables, I've simulated your code with the following:

select 'Yada'
from dual
where &PLGR_COST_ELEM not in ('2FEE','2FEN','2FEP','PCOM');
Enter value for plgr_cost_elem: 'XXXX'

'YAD
----
Yada

How does my code differ from your original, failing code?

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:03 (17Jun04) UTC (aka "GMT" and "Zulu"), 15:03 (17Jun04) Mountain Time)

 

[carp]

15:06:52 SQL> select * from users;

USERNAME
--------------------------------------------------
Lisa
SantaMufasa
Carp

15:06:56 SQL> select * from users where username NOT IN ('SantaMufasa','Carp');

USERNAME
--------------------------------------------------
Lisa

You know - Oracle might not be the problem here!!

But to your original effort. Again, if you have a view and you add a WHERE clause in your query at the Crystal level, I don't see why Crystal won't just pass the SQL into Oracle as-is. Referring back to my previous example, if you enter the query

SELECT * FROM travel_expenses_FY04
WHERE traveler = 'LMCRYER';

what will Crystal do? Based on intuition, experience with other front end report writers, and what you have given us so far, I would expect it to just pass the SQL through to Oracle and wait for the data to come back - no?