Hi,

I have table like below

MODEL_NAME ITEM_NUMBER DA_VALUE

A A-1 2.1

A A-2 3.1

A A-3 1.1

A A-4 4.5

B B-1 3.2

B B-2 5.3

B B-3 7.2

C C-1 3.9

C C-2 4.0

C C-3 9.9

I need to write a stored procedure with 2 input values. One is design_code and another one is model_name. I will pass the model name as comma

separated values to the procedure. Model name is nothing but A,B,C in the above table. Procedure has to split those model names and display

all the item_numbers and da_numbers for the model names.

Here the logic is i have to take all the model's item da_value and multiply.

For example for the above table, I have to multiply all A-1 da_values and B-1 da_value and C-1 da_value then stored in to one array that result.

Again A-1 da_value and B-2 da_values and C-2 da_values then stored into the same array.

Again A-1 da_value and B-3 da_values and C-3 da_values then stored into the same array.

After that come to A-2 da_value and start B-1 da_value and C-1 da_value then store into the same array.

Again A-2 da_value and B-2 da_values and C-2 da_values then stored into the same array.

Again A-2 da_value and B-3 da_values and C-3 da_values then stored into the same array.

So like this only the flow should go for all the models. Below is the sample example.

v_variable := 2.1 * 3.2 * 3.9;

A-1 B-1 C-1

v_variable := 2.1 * 5.3 * 4.0;

A-1 B-2 C-2

v_variable := 2.1 * 7.2 * 9.9;

A-1 B-3 C-3

v_variable := 3.1 * 3.2 * 3.9;

A-2 B-1 C-1

v_variable := 3.1 * 5.3 * 4.0;

A-2 B-2 C-2

The calculation should happen like above example. Can anyone share with your thoughts?

Thanks

I have table like below

MODEL_NAME ITEM_NUMBER DA_VALUE

A A-1 2.1

A A-2 3.1

A A-3 1.1

A A-4 4.5

B B-1 3.2

B B-2 5.3

B B-3 7.2

C C-1 3.9

C C-2 4.0

C C-3 9.9

I need to write a stored procedure with 2 input values. One is design_code and another one is model_name. I will pass the model name as comma

separated values to the procedure. Model name is nothing but A,B,C in the above table. Procedure has to split those model names and display

all the item_numbers and da_numbers for the model names.

Here the logic is i have to take all the model's item da_value and multiply.

For example for the above table, I have to multiply all A-1 da_values and B-1 da_value and C-1 da_value then stored in to one array that result.

Again A-1 da_value and B-2 da_values and C-2 da_values then stored into the same array.

Again A-1 da_value and B-3 da_values and C-3 da_values then stored into the same array.

After that come to A-2 da_value and start B-1 da_value and C-1 da_value then store into the same array.

Again A-2 da_value and B-2 da_values and C-2 da_values then stored into the same array.

Again A-2 da_value and B-3 da_values and C-3 da_values then stored into the same array.

So like this only the flow should go for all the models. Below is the sample example.

v_variable := 2.1 * 3.2 * 3.9;

A-1 B-1 C-1

v_variable := 2.1 * 5.3 * 4.0;

A-1 B-2 C-2

v_variable := 2.1 * 7.2 * 9.9;

A-1 B-3 C-3

v_variable := 3.1 * 3.2 * 3.9;

A-2 B-1 C-1

v_variable := 3.1 * 5.3 * 4.0;

A-2 B-2 C-2

The calculation should happen like above example. Can anyone share with your thoughts?

Thanks

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

If you always will pass in 3 in the list then you can use SQL's builtin join mechanism and allow a cartesian join to do the work for you.

You would only need to make the model_name the parameter to the below query, instead of hardcoding the values.

Notice the cartesian join does not join on any key, so you end up with all combinations where A is the first colum, B is the second, and C is the third.

This is not a PL/SQL solution and is likely not what you want, but I think coding it to support dynamic / variable number of model_names is going to be troublesome with PL/SQL.

I would suggest using dynamic SQL and returning a ref-cursor, so you can arbitrarily add the number of self-joins you need for the query, while letting Oracle do the actual combination work for you.

```
select a.item_number, b.item_number, c.item_number,
a.da_value * b.da_value * c.da_value
from test a, test b, test c
where a.model_name = 'A' and b.model_name = 'B' and c.model_name = 'C'
order by a.item_number, b.item_number, c.item_number
;
ITEM_ ITEM_ ITEM_ A.DA_VALUE*B.DA_VALUE*C.DA_VALUE
----- ----- ----- --------------------------------
A-1 B-1 C-1 26.208
A-1 B-1 C-2 26.88
A-1 B-1 C-3 66.528
A-1 B-2 C-1 43.407
A-1 B-2 C-2 44.52
A-1 B-2 C-3 110.187
A-1 B-3 C-1 58.968
A-1 B-3 C-2 60.48
A-1 B-3 C-3 149.688
A-2 B-1 C-1 38.688
A-2 B-1 C-2 39.68
ITEM_ ITEM_ ITEM_ A.DA_VALUE*B.DA_VALUE*C.DA_VALUE
----- ----- ----- --------------------------------
A-2 B-1 C-3 98.208
A-2 B-2 C-1 64.077
A-2 B-2 C-2 65.72
A-2 B-2 C-3 162.657
A-2 B-3 C-1 87.048
A-2 B-3 C-2 89.28
A-2 B-3 C-3 220.968
A-3 B-1 C-1 13.728
A-3 B-1 C-2 14.08
A-3 B-1 C-3 34.848
A-3 B-2 C-1 22.737
ITEM_ ITEM_ ITEM_ A.DA_VALUE*B.DA_VALUE*C.DA_VALUE
----- ----- ----- --------------------------------
A-3 B-2 C-2 23.32
A-3 B-2 C-3 57.717
A-3 B-3 C-1 30.888
A-3 B-3 C-2 31.68
A-3 B-3 C-3 78.408
A-4 B-1 C-1 51.168
A-4 B-1 C-2 52.48
A-4 B-1 C-3 129.888
A-4 B-2 C-1 84.747
A-4 B-2 C-2 86.92
A-4 B-2 C-3 215.127
ITEM_ ITEM_ ITEM_ A.DA_VALUE*B.DA_VALUE*C.DA_VALUE
----- ----- ----- --------------------------------
A-4 B-3 C-1 115.128
A-4 B-3 C-2 118.08
A-4 B-3 C-3 292.248
36 rows selected.
```

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial