-
Logic in store procedure
Hi group,
I have a requirement for a customer as follow.
I have a table with 9 columns and one more with the combination id where we can have 3 columns with double values (1X, 12, X1, X2, 21, 2X) and two triples (two columns of a row with triple value) are allowed by row (1X2).
Si I have 72 records with different values (72 rows is an example here) just to explain you that I can have more rows on this table.
So for example to reduce to one record, next combination is allowed 1,1,1,1,1X,1X,1X,1X2,1X2 (three doubles and two triples), it means 72 rows (1*1*1*1*2*2*2*3*3) = 72 different rows reduced to this single row.
My table's rows are as follow:
1 1,1,1,1,1,1,1,1,1
2 1,1,1,1,1,1,X,1,1
3 1,1,1,1,1,X,1,1,1
4 1,1,1,1,1,X,X,1,1
5 1,1,1,1,X,1,1,1,1
6 1,1,1,1,X,1,X,1,1
7 1,1,1,1,X,X,1,1,1
8 1,1,1,1,X,X,X,1,1
9 1,1,1,1,1,1,1,X,1
10 1,1,1,1,1,1,X,X,1
11 1,1,1,1,1,X,1,X,1
12 1,1,1,1,1,X,X,X,1
13 1,1,1,1,X,1,1,X,1
14 1,1,1,1,X,1,X,X,1
15 1,1,1,1,X,X,1,X,1
16 1,1,1,1,X,X,X,X,1
17 1,1,1,1,1,1,1,2,1
18 1,1,1,1,1,1,X,2,1
19 1,1,1,1,1,X,1,2,1
20 1,1,1,1,1,X,X,2,1
21 1,1,1,1,X,1,1,2,1
22 1,1,1,1,X,1,X,2,1
23 1,1,1,1,X,X,1,2,1
24 1,1,1,1,X,X,X,2,1
25 1,1,1,1,1,1,1,1,X
26 1,1,1,1,1,1,X,1,X
27 1,1,1,1,1,X,1,1,X
28 1,1,1,1,1,X,X,1,X
29 1,1,1,1,X,1,1,1,X
30 1,1,1,1,X,1,X,1,X
31 1,1,1,1,X,X,1,1,X
32 1,1,1,1,X,X,X,1,X
33 1,1,1,1,1,1,1,X,X
34 1,1,1,1,1,1,X,X,X
35 1,1,1,1,1,X,1,X,X
36 1,1,1,1,1,X,X,X,X
37 1,1,1,1,X,1,1,X,X
38 1,1,1,1,X,1,X,X,X
39 1,1,1,1,X,X,1,X,X
40 1,1,1,1,X,X,X,X,X
41 1,1,1,1,1,1,1,2,X
42 1,1,1,1,1,1,X,2,X
43 1,1,1,1,1,X,1,2,X
44 1,1,1,1,1,X,X,2,X
45 1,1,1,1,X,1,1,2,X
46 1,1,1,1,X,1,X,2,X
47 1,1,1,1,X,X,1,2,X
48 1,1,1,1,X,X,X,2,X
49 1,1,1,1,1,1,1,1,2
50 1,1,1,1,1,1,X,1,2
51 1,1,1,1,1,X,1,1,2
52 1,1,1,1,1,X,X,1,2
53 1,1,1,1,X,1,1,1,2
54 1,1,1,1,X,1,X,1,2
55 1,1,1,1,X,X,1,1,2
56 1,1,1,1,X,X,X,1,2
57 1,1,1,1,1,1,1,X,2
58 1,1,1,1,1,1,X,X,2
59 1,1,1,1,1,X,1,X,2
60 1,1,1,1,1,X,X,X,2
61 1,1,1,1,X,1,1,X,2
62 1,1,1,1,X,1,X,X,2
63 1,1,1,1,X,X,1,X,2
64 1,1,1,1,X,X,X,X,2
65 1,1,1,1,1,1,1,2,2
66 1,1,1,1,1,1,X,2,2
67 1,1,1,1,1,X,1,2,2
68 1,1,1,1,1,X,X,2,2
69 1,1,1,1,X,1,1,2,2
70 1,1,1,1,X,1,X,2,2
71 1,1,1,1,X,X,1,2,2
72 1,1,1,1,X,X,X,2,2
Table structure is as follow:
CREATE TABLE PRDSUP.COMBINACIONES
(
ID_COMBINACION NUMBER,
C1 VARCHAR2(3 BYTE),
C2 VARCHAR2(3 BYTE),
C3 VARCHAR2(3 BYTE),
C4 VARCHAR2(3 BYTE),
C5 VARCHAR2(3 BYTE),
C6 VARCHAR2(3 BYTE),
C7 VARCHAR2(3 BYTE),
C8 VARCHAR2(3 BYTE),
C9 VARCHAR2(3 BYTE)
) ...
I would like to know if you have an idea to create one procedure to scan all the rows and create this single row (1,1,1,1,1X,1X,1X,1X2,1X2) according to the logic that I explained you.
If we want to create one more double (4 in total we are not allowed), also if we want to create other triple (more than 2) for a single row, we aren't allowed.
Let me know if I was clear with this explanation also let me know if you need any additional information.
Thanks in advance.
Kind regards,
Francisco Mtz.
