Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing multiple values to a parameter 1

Status
Not open for further replies.
oradba101

oradba101

MIS
Feb 28, 2003
318
US
Greetings,

I need to be able to pass multiple values to a parameter. Is there any way to do this?

Regards,


William Chadbourne
Programmer/Analyst
 
Sort by date Sort by votes
William,

There are methods, but before we explore those ways, could you tell us a bit more about the usage of the parameter? Is its destination a procedure or function over which you have code control?

If you do not have coding control, then how do you propose to "teach" the code how to deal with the multiple values?

If you do have coding control, then why not simplify the issue by defining the multiple parameters you are wishing to pass to the destination?

Let us know.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Greetins, Dave

I have a Crystal Report thst relies on a REF CURSOR.

The REF CURSOE does a series of calculations on a 300K+ table which takes a long time to complete if it does at all.

I can't filter the records through Crystal so I thought I would try it through imput parameters in the Package I created.

Regards,


William Chadbourne
Programmer/Analyst
 
Upvote 0 Downvote
So, can you call the Crystal component from within the Package? If so, why not pass the multiple parameters to your packaged function/procedure, then call the Crystal part with the parameter it expects?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Hi, Dave

Perhaps a further explanation is in order.

I have a Crystal Report that uses a histogram chart ans despite my efforts I was not able to create a chart due to the limitations of the charting tools in Crystal.

So, in order ro calculate the data I needed for this chart I decided to create a Package returning a REF CURSOR from Oracle (BO specifically states that this is the only way to get Oracle SPs to work with Crystal).

I created the package that does the following:

The user wants to be able to select the number of bars that appear on the chart baesd upon the minimum and maximum values of a score for a location and a column called analyte. So for example if the minimum value is 0 and the maximum value is 1 then the maximum value subtracts the minimum value and then deivies the remainder by the number of bars inputted by the uses let's say this is 4

Then the following variables are calculated to give the various ranges

x1 = divisor (.25)
x2 - divisor * 2 (.50)
x3 = divisor * 3 (.75)
x4 = divisor * 4 (1)

This is then matched to the score for the individual records and one of the values of the 'x' variables is inserted into a column called range.

This part works great.

But there are over 300000 records in one table and Crystal seams to execute the package before any of the filters defines are lokked at and this usually does not complete. Because the user can select multiple locations and analyes I would like to be able to insert multiple values into both parameters.

Also, how does the CODE thing work?

Regards,


William Chadbourne
Programmer/Analyst
 
Upvote 0 Downvote
Greetings, Dave

Have I provided enough information or is this not possible?

Regards,


William Chadbourne
Programmer/Analyst
 
Upvote 0 Downvote
William,

My apologies, but I am completely ignorant of Crystal Reports behaviours, so I'm deferring to those more knowledgeable on the topic...Tharg, Dagon, others ???

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Hi, Dave

The Crystal part I have worked out.

When I choose more than one value crystal formats it like this:

'CKP,KPS'

Since the parameter is part of the SELECT statements in the package I need a function that will change it to

'CKP','KPS'

Is this possible?

Thanks,









ck



William Chadbourne
Programmer/Analyst
 
Upvote 0 Downvote
So, William, am I correct in presuming that you want:[ul][li]Zero, one, or many values (e.g., 'CKP','KPS') "gathered up" and returned as a single expression,[/li][li]Individual values surrounded by single quotes,[/li][li]Multiple values separated with a single comma[/li][/ul]If those are the correct specifications, then we can build such a function, if you explain how you want those values identified.


If, instead, you want zero, one, or many rows of data to pass to Crystal, then a refcursor would be appropriate.

Regardless of which option you are seeking, to help us help you create a proof of concept, could you please post a few SQL statements:[ul][li]CREATE TABLE <table name>...[/li][li]INSERT INTO <table_name>[/li][/ul]Thanks.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Hi, Dave

[ul]
[li]Zero, one, or many values (e.g., 'CKP','KPS') "gathered up" and returned as a single expression,[/li]
[li]ndividual values surrounded by single quotes, [/li]
[li]Multiple values separated with a single comma [/li]
[/ul]

This is exactly what I need

Please see attached code

Code: 
CREATE OR REPLACE PROCEDURE LABWORKS.histogram
(
InputBin      IN NUMBER,
InputLoc      IN VARCHAR2,
InputAnl      IN VARCHAR2,
InputBegDate  IN DATE,
InputEndDate  IN DATE
)
IS
      CURSOR c_minmax IS
      WITH temp AS
      (
      SELECT loccode, 
             analyte,
             sample_date,
             result
        FROM view_histogram
       WHERE loccode = InputLoc
         AND analyte = InputAnl
         AND TO_DATE(sample_date) BETWEEN InputBegDate AND InputEndDate
         AND acode IN('$SCAN_AMBIENT','$SCAN_AMBIENT_ME','$SCAN_AMBIENT_UC','$SIM_AMBIENT',
                      '$SIM_AMBIENT_RI','$TO-15_AMBIENT_EPA')
      )
      SELECT loccode, 
             analyte,
             MIN(display_as_num(RESULT)) AS min_result,
             MAX(display_as_num(RESULT)) AS max_result
        FROM temp
      GROUP BY loccode, analyte
;

      -- Retrive the columns needed for the report
      CURSOR c_temp IS
      SELECT acode,
             DISPLAY_AS_NUM(result) AS result,
             loccode,
             analyte,
             anlname,
             sample_date,
             sampno,
             casnumb
        FROM view_histogram
       WHERE loccode = InputLoc
         AND analyte = InputAnl
         AND TO_DATE(sample_date) BETWEEN InputBegDate AND InputEndDate
         AND acode IN('$SCAN_AMBIENT','$SCAN_AMBIENT_ME','$SCAN_AMBIENT_UC','$SIM_AMBIENT',
                      '$SIM_AMBIENT_RI','$TO-15_AMBIENT_EPA') ;

The values are inputted and all the inputted values are in both CURSOR statements in the WHERE clauses.

BTW I hope that by seeing my name in bold I have not upset you.

Regards,



William Chadbourne
Programmer/Analyst
 
Upvote 0 Downvote
William said:
I hope that by seeing my name in bold I have not upset you.
Click to expand...
Actually, quite the opposite. I highlight person's screen names as a sign of respect to that person...Kinda like putting someone's name in lights.


So, which of the above expressions in your cursor(s) do you want to have "batched up" into a single result?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Hi, Dave

Code: 
WHERE loccode = InputLoc
  AND analyte = InputAnl

Regards,



William Chadbourne
Programmer/Analyst
 
Upvote 0 Downvote
Sorry, William, I meant which of these expressions...
Code: 
      SELECT acode,
             DISPLAY_AS_NUM(result) AS result,
             loccode,
             analyte,
             anlname,
             sample_date,
             sampno,
             casnumb
...do you want to be strung together for the rows that appear in the CURSOR?


Also, I see no LOOP occurring in your LABWORKS.histogram; I see only the two CURSORS. Are you adverse to my creating code that loops through your CURSOR so that I can "gather up" the results that you want strung together?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Hi, Dave

Not at all

Here is the entire package body

Code: 
CREATE OR REPLACE PACKAGE BODY LABWORKS.analyte_histogram AS
/******************************************************************************
   NAME:       ANALYTE_HISTOGRAM
   PURPOSE:    If a stored procedure is used within a Crystal Report is must be
               in a package and must use a REF CURSOR.

   REVISIONS:
   Ver        Date        Author              Description
   ---------  ----------  ---------------     ---------------------------------
   1.0        04/19/2008  William Chadbourne  1. Created this package body.
*******************************************************************************/
   PROCEDURE histogram_cnt
      (InputBin     IN NUMBER,
       InputLoc     IN VARCHAR2,
       InputAnl     IN VARCHAR2,
       InputBegDate IN DATE,
       InputEndDate IN DATE,       
       hist_cursor  IN OUT hist_type)
/******************************************************************************
   NAME:       histogram_cnt
   PURPOSE:    To define the number of data points (bind) a user can define on
               a Crystal Reports chart in the Analyte Histogram report.

   REVISIONS:
   Ver        Date        Author              Description
   ---------  ----------  ---------------     --------------------------------
   1.0        04/16/2008  William Chadbourne  1. Created this procedure.

   NOTES:

******************************************************************************/
IS
BEGIN
   DECLARE
      v_bins    NUMBER ;
      v_divisor NUMBER ;
      v_x1      NUMBER ;
      v_x2      NUMBER ;
      v_x3      NUMBER ;
      v_x4      NUMBER ;
      v_x5      NUMBER ;
      v_x6      NUMBER ;
      v_x7      NUMBER ;
      v_x8      NUMBER ;
      v_x_ins   NUMBER ;

      -- Get the minimum and maximume values of the result column
      -- by loccode and Analyte.
      CURSOR c_minmax IS
      WITH temp AS
      (
      SELECT loccode, 
             analyte,
             sample_date,
             result
        FROM view_histogram
       WHERE loccode = InputLoc
         AND analyte = InputAnl
         AND TO_DATE(sample_date) BETWEEN InputBegDate AND InputEndDate
         AND acode IN('$SCAN_AMBIENT','$SCAN_AMBIENT_ME','$SCAN_AMBIENT_UC','$SIM_AMBIENT',
                      '$SIM_AMBIENT_RI','$TO-15_AMBIENT_EPA')
      )
      SELECT loccode, 
             analyte,
             MIN(DISPLAY_AS_NUM(RESULT)) AS min_result,
             MAX(DISPLAY_AS_NUM(RESULT)) AS max_result
        FROM temp
      GROUP BY loccode, analyte
;

      -- Retrieve the columns needed for the report
      CURSOR c_temp IS
      SELECT acode,
             DISPLAY_AS_NUM(result) AS result,
             loccode,
             analyte,
             anlname,
             sample_date,
             sampno,
             casnumb
        FROM view_histogram
       WHERE loccode = InputLoc
         AND analyte = InputAnl
         AND TO_DATE(sample_date) BETWEEN InputBegDate AND InputEndDate
         AND acode IN('$SCAN_AMBIENT','$SCAN_AMBIENT_ME','$SCAN_AMBIENT_UC','$SIM_AMBIENT',
                      '$SIM_AMBIENT_RI','$TO-15_AMBIENT_EPA') ;

       
   BEGIN
      -- Empty the HISTOGRAM_TEMP table
      EXECUTE IMMEDIATE('TRUNCATE TABLE histogram_temp') ;

      -- Pass the value entered by the user to the v_bins variable
      v_bins := InputBin ;

      -- Execute the first loop which determines the divisor and then
      -- computes the values based upon the user selection in the
      -- InputBins paramter.
      FOR r_minmax IN c_minmax

      -- Populate the DIVISOR variable with the result of subtractibg
      -- the minimum value from the maximum value in the RESULT column
      LOOP
         CASE
            WHEN r_minmax.max_result - r_minmax.min_result = 0 THEN
               v_divisor := 0 ;
            ELSE
               v_divisor := (r_minmax.max_result - r_minmax.min_result) / v_bins ;
         END CASE ;

         -- Based upon the user's inpuutted value, calculate the ranges that each
         -- individual result will fall into.
         CASE
            WHEN v_bins = 3 THEN
               v_x1  := v_divisor ;
               v_x2  := v_divisor * 2 ;
               v_x3  := v_divisor * 3 ;
            WHEN v_bins = 4 THEN
               v_x1  := v_divisor ;
               v_x2  := v_divisor * 2 ;
               v_x3  := v_divisor * 3 ;
               v_x4  := v_divisor * 4 ;
            WHEN v_bins = 5 THEN
               v_x1  := v_divisor ;
               v_x2  := v_divisor * 2 ;
               v_x3  := v_divisor * 3 ;
               v_x4  := v_divisor * 4 ;
               v_x5  := v_divisor * 5 ;
            WHEN v_bins = 6 THEN
               v_x1  := v_divisor ;
               v_x2  := v_divisor * 2 ;
               v_x3  := v_divisor * 3 ;
               v_x4  := v_divisor * 4 ;
               v_x5  := v_divisor * 5 ;
               v_x6  := v_divisor * 6 ;
            WHEN v_bins = 7 THEN
               v_x1  := v_divisor ;
               v_x2  := v_divisor * 2 ;
               v_x3  := v_divisor * 3 ;
               v_x4  := v_divisor * 4 ;
               v_x5  := v_divisor * 5 ;
               v_x6  := v_divisor * 6 ;
               v_x7  := v_divisor * 7 ;
            WHEN v_bins = 8 THEN
               v_x1  := v_divisor ;
               v_x2  := v_divisor * 2 ;
               v_x3  := v_divisor * 3 ;
               v_x4  := v_divisor * 4 ;
               v_x5  := v_divisor * 5 ;
               v_x6  := v_divisor * 6 ;
               v_x7  := v_divisor * 7 ;
               v_x8  := v_divisor * 8 ;
         END CASE ;

         -- Execute the second loop which inserts the value of the
         -- v_x_ins variable into the range column of the
         -- histogram_temp table.
         FOR r_temp IN c_temp

         LOOP
            CASE
               WHEN v_bins >= 3
                AND r_temp.result BETWEEN 0 AND v_x1 THEN
                  v_x_ins := v_x1 ;
               WHEN v_bins >= 3
                AND r_temp.result > v_x1 AND r_temp.result <= v_x2 THEN
                  v_x_ins := v_x2 ;
               WHEN v_bins >= 3
                AND r_temp.result > v_x2 AND r_temp.result <= v_x3 THEN
                  v_x_ins := v_x3 ;
               WHEN v_bins >= 4
                AND r_temp.result > v_x3 AND r_temp.result <= v_x4 THEN
                  v_x_ins := v_x4 ;
               WHEN v_bins >= 5
                AND r_temp.result > v_x4 AND r_temp.result <= v_x5 THEN
                  v_x_ins := v_x5 ;
               WHEN v_bins >= 6
                AND r_temp.result > v_x5 AND r_temp.result <= v_x6 THEN
                  v_x_ins := v_x6 ;
               WHEN v_bins >= 7
                AND r_temp.result > v_x6 AND r_temp.result <= v_x7 THEN
                  v_x_ins := v_x7 ;
               WHEN v_bins >= 8
                AND r_temp.result > v_x7 AND r_temp.result <= v_x8 THEN
                  v_x_ins := v_x8 ;
               ELSE
                  NULL ;
            END CASE ;

            IF r_temp.loccode = r_minmax.loccode AND
               r_temp.analyte = r_minmax.analyte THEN

               -- Insert the records into the table
               INSERT INTO histogram_temp(acode,
                                          result,
                                          loccode,
                                          analyte,
                                          anlname,
                                          sample_date,
                                          sampno,
                                          casnumb,
                                          range)
                                   VALUES(r_temp.acode,
                                          r_temp.result,
                                          r_temp.loccode,
                                          r_temp.analyte,
                                          r_temp.anlname,
                                          r_temp.sample_date,
                                          r_temp.sampno,
                                          r_temp.casnumb,
                                          v_x_ins) ;
            END IF ;
         END LOOP ;
      END LOOP ;
     
      -- This is the result returned to Crystal Reports
      OPEN hist_cursor 
       FOR
      SELECT *
        FROM histogram_temp ; 
   END ;
END histogram_cnt ;

END ANALYTE_HISTOGRAM;
/

Perhaps I am misunderstadning but I wast the values entered by a user in the INPUTLOC and INPUTANL parameters to be strung together not any of the columns in the SELECT statement.

Regards,


William Chadbourne
Programmer/Analyst
 
Upvote 0 Downvote
William said:
I want the values entered by a user in the INPUTLOC and INPUTANL parameters to be strung together...
Click to expand...
Then will this work:
Code: 
declare
    str_hold      varchar2(100);
begin
    ...
    str_hold := ''''||INPUTLOC||''','''||INPUTANL||'''';
    ...
end;
/
Let us know if I'm missing something.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Hi, Dave

I just have to separate the INPUTLOC and INPUTANL into separate functions.

Is that the complete code listing?

Regards,


William Chadbourne
Programmer/Analyst
 
Upvote 0 Downvote
Hi, Dave

I'm just having a brain cramp and I just want to get this final piece done.

Thanks for all your help.

Regards,


William Chadbourne
Programmer/Analyst
 
Upvote 0 Downvote
William,

Regarding my code, above...at the time of executing my code, if the content of INPUTLOC is "CKP" and INPUTANL is "KPS", then my code produces this result:

[tt]'CKP','KPS'[/tt]

Is this what you needed/wanted. If not, please correct my misunderstanding.

Again, if I have missed a fundamental specification, please correct me and re-specify in terms that an 8-year-old can understand.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Hi, Dave

CKP snd KPS sre loccodes. From the Crystsl Report, users wi;; more often thsn not choose more thsn one loccode. The function I believe I need is to psrse the user input into two or more vslues snd then use the function in the WHERE clause so:

InputLoc vslue: 'CKP,KPD' becomes 'CKP','KPS'

So the WHERE clause becomes

WHERE loccode IN FUMCTIOM(InputLoc)

InputAnl is for s different column snd would probsbly fall under the user scenario.

Regards,


William Chadbourne
Programmer/Analyst
 
Upvote 0 Downvote
If that is your objective, then why wouldn't this code work in your WHERE statement?:
Code: 
...WHERE INSTR(InputLoc,LocCode) > 0
...
Let us know if this code is useful.


[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheLazyPig
  • Locked
  • Question
Merge Into with multiple execution 1
Replies
1
Views
687
carp
carp
Detonah
  • Locked
  • Question
Micros Res 3700 - Cashier Report Displaying Multiple Days Together
Replies
0
Views
672
Detonah
Detonah
Andrzejek
  • Locked
  • Question
Insert multiple records 1
Replies
9
Views
834
mikrom
mikrom
TheLazyPig
  • Locked
  • Question
Separate 1 row value to multiple rows
Replies
4
Views
723
carp
carp
Chopstik
  • Locked
  • Question
How to identify a package based on a code snippet 1
Replies
1
Views
690
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top