Parameter with Range values

[smadala]

Hi All,

I created a stored procedure with parameters in package, i used that stored procedure in crystal Reports XI..till now its working fine.
Now i need to retrieve a report based on Parameter range values..
My question is:
Is it possible to assign Ranges to Parameters? Bydefault Ranges should be NULL. I have to enter Range values from webpage.


This is my stroed Procedure which i used without Range values:
I need Range values for Begin_Date Parameter.


procedure Select_Budget(p _PK_Budget_Head er_Id IN BUDGET_HEADER.P K_BUDGET_HEADER _ID%TYPE,
P_BEGIN_DATE IN BUDGET_HEADER.B EGIN_DATE%TYPE DEFAULT NULL,
RETURN_CURSOR OUT type_pkg.ref_cu rsor_typ) is

begin

OPEN RETURN_CURSOR FOR
SELECT

BH.PK_BUDGET_HE ADER_ID,
BH.FK_BUDGET_MA STER_ID,
BH.BEGIN_DATE,
BH.END_DATE,
BH.COMMENTS

FROM BUDGET_HEADER BH

WHERE BH.PK_BUDGET_HE ADER_ID = nvl(p_PK_Budget _Header_Id, BH.PK_BUDGET_HE ADER_ID)
AND BH.BEGIN_DATE = nvl(p_BEGIN_DAT E, BH.BEGIN_DATE);


end Select_Budget;

I think this is clear for you, if not i am ready to give you clear idea..

Thanks in Advance

[Saii]

If your requirement implies that begin date should be between some start date and end date then you need to have two IN date parameters

[smadala]

Thanks for your quick reply saii..appreciat ed.

you said i need to have 2 IN date parameters..
If suppose my user needs Ranges for some other parameter (like datatype as Number -- ex EmpNo), Then also i have to use 2 IN parameters?

Previously, I generated Reports using simple tables, there i didn't get this problem.Why because, in Crystal Reports 1 option is there"Allow Range values -- True/False".
By setting 'True ', 2 different boxes are displayed (to enter start value and end value).
While coming to stored procedures, by default that option is set to 'False'.
I thought we have to set Range values to true/false while passing parameters only..
Its possible or not i don't know..
i am searching in this way...

Is there any other way for this problem?


Thanks in advance

[amitpatel66]

Thanks for your quick reply saii..appreciat ed.

you said i need to have 2 IN date parameters..
If suppose my user needs Ranges for some other parameter (like datatype as Number -- ex EmpNo), Then also i have to use 2 IN parameters?

Previously, I generated Reports using simple tables, there i didn't get this problem.Why because, in Crystal Reports 1 option is there"Allow Range values -- True/False".
By setting 'True ', 2 different boxes are displayed (to enter start value and end value).
While coming to stored procedures, by default that option is set to 'False'.
I thought we have to set Range values to true/false while passing parameters only..
Its possible or not i don't know..
i am searching in this way...

Is there any other way for this problem?


Thanks in advance

Well in case of empno, if you dont want to have two INPUT parameters, then you can have one VARCHAR2 parameter which will accept the range value with some delimiter.

For Eg: I pass the value to the input parameter '10-20' which means I want all the employees whose empno is between 10 and 20.
Then you can extract the FROM and TO values using a delimiter.somet hing like:
[code=oracle]
select SUBSTR('10-20',1,INSTR('10-20','-')-1) FROM dual -- This query will give you value 10
[/code]

[smadala]

Thanks for you reply,

I think i didn't understood your reply clearly..

I have to enter Range values from my webpage. I don't want to pass those values directly, i think you understood this.

any suggestions please?


Thanks

[amitpatel66]

Thanks for you reply,

I think i didn't understood your reply clearly..

I have to enter Range values from my webpage. I don't want to pass those values directly, i think you understood this.

any suggestions please?


Thanks

If you are going to pass the RANGE values (HIGH - LOW) seperately then you need to go for two IN PARAMETERS