Home » SQL & PL/SQL » SQL & PL/SQL » Need to calculate a Set No. from a Data set (Oracle Database, 10g Rel.2, Windows-7)
Need to calculate a Set No. from a Data set [message #633828] |
Wed, 25 February 2015 13:16 |
mazam
Messages: 47 Registered: October 2009 Location: Multan
|
Member |
|
|
Dear gurus
I am using Oracle Database 10g Rel.2.
I need to calculate a Set No. from a Data set.
For this please get the table structure n data as below:
Create Table Emp_Tst(Employee_Code Number(10), Record_No Number(3), Column_No Number(3), Column_Val Number(3), Set_No Number(3));
Insert Into Emp_Tst Values(2015001234 , 1 , 3 , 2 , 1);
Insert Into Emp_Tst Values(2015001234 , 1 , 4 , 2 , 1);
Insert Into Emp_Tst Values(2015001234 , 1 , 5 , 1 , 1);
Insert Into Emp_Tst Values(2015001234 , 2 , 3 , 2 , 1);
Insert Into Emp_Tst Values(2015001234 , 2 , 4 , 2 , 1);
Insert Into Emp_Tst Values(2015001234 , 2 , 5 , 1 , 1);
Insert Into Emp_Tst Values(2015001234 , 3 , 3 , 2 , 1);
Insert Into Emp_Tst Values(2015001234 , 3 , 4 , 2 , 1);
Insert Into Emp_Tst Values(2015001234 , 3 , 5 , 1 , 1);
Insert Into Emp_Tst Values(2015001234 , 4 , 1 , 1 , 2);
Insert Into Emp_Tst Values(2015001234 , 4 , 2 , 1 , 2);
Insert Into Emp_Tst Values(2015001234 , 4 , 4 , 3 , 2);
Insert Into Emp_Tst Values(2015001234 , 5 , 1 , 1 , 2);
Insert Into Emp_Tst Values(2015001234 , 5 , 2 , 1 , 2);
Insert Into Emp_Tst Values(2015001234 , 5 , 4 , 3 , 2);
Insert Into Emp_Tst Values(2015001234 , 6 , 1 , 1 , 3);
Insert Into Emp_Tst Values(2015001234 , 6 , 2 , 1 , 3);
Insert Into Emp_Tst Values(2015001234 , 7 , 1 , 1 , 3);
Insert Into Emp_Tst Values(2015001234 , 7 , 2 , 1 , 3);
Insert Into Emp_Tst Values(2015001234 , 8 , 3 , 2 , 1);
Insert Into Emp_Tst Values(2015001234 , 8 , 4 , 2 , 1);
Insert Into Emp_Tst Values(2015001234 , 8 , 5 , 1 , 1);
Insert Into Emp_Tst Values(2015001234 , 9 , 2 , 1 , 4);
Insert Into Emp_Tst Values(2015001234 , 9 , 3 , 1 , 4);
Insert Into Emp_Tst Values(2015001234 , 10 , 1 , 1 , 3);
Insert Into Emp_Tst Values(2015001234 , 10 , 2 , 1 , 3);
Data generated from this script should look like this:
EMPLOYEE_CODE RECORD_NO COLUMN_NO COLUMN_VAL SET_NO
2015001234 1 3 2 1
2015001234 1 4 2 1
2015001234 1 5 1 1
2015001234 2 3 2 1
2015001234 2 4 2 1
2015001234 2 5 1 1
2015001234 3 3 2 1
2015001234 3 4 2 1
2015001234 3 5 1 1
2015001234 4 1 1 2
2015001234 4 2 1 2
2015001234 4 4 3 2
2015001234 5 1 1 2
2015001234 5 2 1 2
2015001234 5 4 3 2
2015001234 6 1 1 3
2015001234 6 2 1 3
2015001234 7 1 1 3
2015001234 7 2 1 3
2015001234 8 3 2 1
2015001234 8 4 2 1
2015001234 8 5 1 1
2015001234 9 2 1 4
2015001234 9 3 1 4
2015001234 10 1 1 3
2015001234 10 2 1 3
Now lets come to question.
Column SET_NO in my table is blank which I've filled for your understanding and I need its value after user inserts data in first four columns.
So for this case,
Firstly I need your help in calculation of SET NO.
Whenever a new Employee_Code is inserted in table, SET_NO.1 is assigned to its first record. Then, if any next record has the same values of Employee_Code, Record_No, Column_No and Column_Value, those records will have same SET_NO values. For example SET_NO: 1 for first three Record_Nos and Record_No:8. This pattern goes on repeating for all the records.
And then, I need to know which event is better for this calculation and Updation of value in SET NO column? either from a D.B trigger or call a DB procedure at application level after Insertion or Updation?
Regards and Thanks.
|
|
|
Re: Need to calculate a Set No. from a Data set [message #633833 is a reply to message #633828] |
Wed, 25 February 2015 13:54 |
|
Michel Cadot
Messages: 68645 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Whenever a new Employee_Code is inserted in table, SET_NO.1 is assigned to its first record.
All employee numbers are equal in your test case.
Quote:For example SET_NO: 1 for first three Record_Nos and Record_No:8.
What does this mean? Define "first 3 record_nos". Is this record_no 1,2,3?
They are 9 records with these record_no.
Quote:Then, if any next record has the same values of Employee_Code, Record_No, Column_No and Column_Value, those records will have same SET_NO values.
They have different values for "Record_No, Column_No and Column_Value" so why have they the same set_no?
And what happens if you now add record:
Your requirements are not clear.
|
|
|
|
|
Re: Need to calculate a Set No. from a Data set [message #633838 is a reply to message #633828] |
Wed, 25 February 2015 16:16 |
|
Barbara Boehmer
Messages: 9090 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I would not use a trigger, since all rows may be affected by any insert or update or deletion. I used a PL/SQL block below, that you could put in a procedure if you like. It seems like there should be an easier way using just SQL, either using an UPDATE or a VIEW, but I couldn't figure out how to do that in 10g, especially when I only have 11g and 12c to test with.
SCOTT@orcl> SELECT * FROM emp_tst
2 /
EMPLOYEE_CODE RECORD_NO COLUMN_NO COLUMN_VAL SET_NO
------------- ---------- ---------- ---------- ----------
2015001234 1 3 2
2015001234 1 4 2
2015001234 1 5 1
2015001234 2 3 2
2015001234 2 4 2
2015001234 2 5 1
2015001234 3 3 2
2015001234 3 4 2
2015001234 3 5 1
2015001234 4 1 1
2015001234 4 2 1
2015001234 4 4 3
2015001234 5 1 1
2015001234 5 2 1
2015001234 5 4 3
2015001234 6 1 1
2015001234 6 2 1
2015001234 7 1 1
2015001234 7 2 1
2015001234 8 3 2
2015001234 8 4 2
2015001234 8 5 1
2015001234 9 2 1
2015001234 9 3 1
2015001234 10 1 1
2015001234 10 2 1
26 rows selected.
SCOTT@orcl> CREATE OR REPLACE TYPE data_typ AS OBJECT
2 (column_no NUMBER,
3 column_val NUMBER,
4 MAP MEMBER FUNCTION COMPARE RETURN RAW);
5 /
Type created.
SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> CREATE OR REPLACE TYPE BODY data_typ AS
2 MAP MEMBER FUNCTION COMPARE RETURN RAW
3 IS
4 BEGIN
5 RETURN
6 UTL_RAW.CAST_TO_RAW (column_no) ||
7 UTL_RAW.CAST_TO_RAW (column_val);
8 END;
9 END;
10 /
Type body created.
SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> CREATE OR REPLACE TYPE data_tab AS TABLE OF data_typ;
2 /
Type created.
SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> DECLARE
2 v_set_no NUMBER := 0;
3 v_employee_code emp_tst.employee_code%TYPE;
4 v_update NUMBER := 1;
5 BEGIN
6 UPDATE emp_tst SET set_no = NULL;
7 FOR r1 IN
8 (SELECT employee_code, record_no,
9 CAST (COLLECT (data_typ (column_no, column_val)) AS data_tab) data_set
10 FROM emp_tst
11 WHERE set_no IS NULL
12 GROUP BY employee_code, record_no
13 ORDER BY employee_code, record_no)
14 LOOP
15 IF r1.employee_code != v_employee_code THEN
16 v_set_no := 1;
17 v_employee_code := r1.employee_code;
18 ELSE
19 IF v_update > 0 THEN
20 v_set_no := v_set_no + 1;
21 END IF;
22 END IF;
23 v_update := 0;
24 FOR r2 IN
25 (SELECT employee_code, record_no,
26 CAST (COLLECT (data_typ (column_no, column_val)) AS data_tab) data_set
27 FROM emp_tst
28 WHERE set_no IS NULL
29 GROUP BY employee_code, record_no
30 ORDER BY employee_code, record_no)
31 LOOP
32 IF r2.employee_code = r1.employee_code AND
33 r2.data_set = r1.data_set
34 THEN
35 UPDATE emp_tst
36 SET set_no = v_set_no
37 WHERE employee_code = r2.employee_code
38 AND record_no = r2.record_no;
39 IF SQL%ROWCOUNT > 0 THEN
40 v_update := v_update + 1;
41 END IF;
42 END IF;
43 END LOOP;
44 END LOOP;
45 END;
46 /
PL/SQL procedure successfully completed.
SCOTT@orcl> SELECT * FROM emp_tst ORDER BY employee_code, record_no
2 /
EMPLOYEE_CODE RECORD_NO COLUMN_NO COLUMN_VAL SET_NO
------------- ---------- ---------- ---------- ----------
2015001234 1 4 2 1
2015001234 1 3 2 1
2015001234 1 5 1 1
2015001234 2 4 2 1
2015001234 2 3 2 1
2015001234 2 5 1 1
2015001234 3 3 2 1
2015001234 3 4 2 1
2015001234 3 5 1 1
2015001234 4 1 1 2
2015001234 4 2 1 2
2015001234 4 4 3 2
2015001234 5 1 1 2
2015001234 5 4 3 2
2015001234 5 2 1 2
2015001234 6 1 1 3
2015001234 6 2 1 3
2015001234 7 1 1 3
2015001234 7 2 1 3
2015001234 8 3 2 1
2015001234 8 4 2 1
2015001234 8 5 1 1
2015001234 9 2 1 4
2015001234 9 3 1 4
2015001234 10 1 1 3
2015001234 10 2 1 3
26 rows selected.
|
|
|
Re: Need to calculate a Set No. from a Data set [message #633839 is a reply to message #633838] |
Wed, 25 February 2015 17:17 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Or:
with e1 as (
select e.*,
listagg(lpad(column_no,3),'|')
within group(order by column_no)
over(partition by employee_code,record_no) column_set
from emp_tst e
),
e2 as (
select e1.*,
min(record_no) over(partition by employee_code,column_set) min_record_no
from e1
)
select employee_code,
record_no,
column_no,
column_val,
set_no,
dense_rank() over(partition by employee_code order by min_record_no) calculated_set_no
from e2
order by employee_code,
record_no,
column_no
/
EMPLOYEE_CODE RECORD_NO COLUMN_NO COLUMN_VAL SET_NO CALCULATED_SET_NO
------------- ---------- ---------- ---------- ---------- -----------------
2015001234 1 3 2 1 1
2015001234 1 4 2 1 1
2015001234 1 5 1 1 1
2015001234 2 3 2 1 1
2015001234 2 4 2 1 1
2015001234 2 5 1 1 1
2015001234 3 3 2 1 1
2015001234 3 4 2 1 1
2015001234 3 5 1 1 1
2015001234 4 1 1 2 2
2015001234 4 2 1 2 2
EMPLOYEE_CODE RECORD_NO COLUMN_NO COLUMN_VAL SET_NO CALCULATED_SET_NO
------------- ---------- ---------- ---------- ---------- -----------------
2015001234 4 4 3 2 2
2015001234 5 1 1 2 2
2015001234 5 2 1 2 2
2015001234 5 4 3 2 2
2015001234 6 1 1 3 3
2015001234 6 2 1 3 3
2015001234 7 1 1 3 3
2015001234 7 2 1 3 3
2015001234 8 3 2 1 1
2015001234 8 4 2 1 1
2015001234 8 5 1 1 1
EMPLOYEE_CODE RECORD_NO COLUMN_NO COLUMN_VAL SET_NO CALCULATED_SET_NO
------------- ---------- ---------- ---------- ---------- -----------------
2015001234 9 2 1 4 4
2015001234 9 3 1 4 4
2015001234 10 1 1 3 3
2015001234 10 2 1 3 3
26 rows selected.
SQL>
SY.
|
|
|
|
Re: Need to calculate a Set No. from a Data set [message #633876 is a reply to message #633836] |
Thu, 26 February 2015 11:13 |
mazam
Messages: 47 Registered: October 2009 Location: Multan
|
Member |
|
|
Dear Michael...For your understanding, I am attaching the image of my matrix report which is based on data I provided in original post.
For easy understanding, I've shaded each Set of data.
Sr. is Record_No.
From 1 to 10 are the Column_Nos.
Below each Column_No is Column_Val.
So, looking at this image, each of the rows (Record_No) having same values under same Column_No will be assigned same SET_NO. However, the very first row against an employee will have SET_NO as 1.
And, if any row is updated with a different Column_Val from its previous SET, then it will be assigned any other matching SET_NO or a new one.
It means, that any solution provided, should work on both Insert and Update events.
[mod-edit: image inserted into message body by bb]
[Updated on: Fri, 27 February 2015 02:27] by Moderator Report message to a moderator
|
|
|
|
|
Re: Need to calculate a Set No. from a Data set [message #633885 is a reply to message #633883] |
Thu, 26 February 2015 15:12 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
mazam wrote on Thu, 26 February 2015 14:08And I've tried Connect_By but I'm unable to get the required result.
SQL> with e0 as (
2 select employee_code,
3 record_no,
4 column_no,
5 row_number() over(partition by employee_code,record_no order by column_no) rn
6 from emp_tst e
7 ),
8 e1 as (
9 select employee_code,
10 record_no,
11 sys_connect_by_path(lpad(column_no,3),'|') column_set
12 from e0
13 where connect_by_isleaf = 1
14 start with rn = 1
15 connect by employee_code = prior employee_code
16 and record_no = prior record_no
17 and rn = prior rn + 1
18 ),
19 e2 as (
20 select e.*,
21 min(e.record_no) over(partition by e.employee_code,e1.column_set) min_record_no
22 from emp_tst e,
23 e1
24 where e.employee_code = e1.employee_code
25 and e.record_no = e1.record_no
26 )
27 select employee_code,
28 record_no,
29 column_no,
30 column_val,
31 set_no,
32 dense_rank() over(partition by employee_code order by min_record_no) calculated_set_no
33 from e2
34 order by employee_code,
35 record_no,
36 column_no
37 /
EMPLOYEE_CODE RECORD_NO COLUMN_NO COLUMN_VAL SET_NO CALCULATED_SET_NO
------------- ---------- ---------- ---------- ---------- -----------------
2015001234 1 3 2 1 1
2015001234 1 4 2 1 1
2015001234 1 5 1 1 1
2015001234 2 3 2 1 1
2015001234 2 4 2 1 1
2015001234 2 5 1 1 1
2015001234 3 3 2 1 1
2015001234 3 4 2 1 1
2015001234 3 5 1 1 1
2015001234 4 1 1 2 2
2015001234 4 2 1 2 2
EMPLOYEE_CODE RECORD_NO COLUMN_NO COLUMN_VAL SET_NO CALCULATED_SET_NO
------------- ---------- ---------- ---------- ---------- -----------------
2015001234 4 4 3 2 2
2015001234 5 1 1 2 2
2015001234 5 2 1 2 2
2015001234 5 4 3 2 2
2015001234 6 1 1 3 3
2015001234 6 2 1 3 3
2015001234 7 1 1 3 3
2015001234 7 2 1 3 3
2015001234 8 3 2 1 1
2015001234 8 4 2 1 1
2015001234 8 5 1 1 1
EMPLOYEE_CODE RECORD_NO COLUMN_NO COLUMN_VAL SET_NO CALCULATED_SET_NO
------------- ---------- ---------- ---------- ---------- -----------------
2015001234 9 2 1 4 4
2015001234 9 3 1 4 4
2015001234 10 1 1 3 3
2015001234 10 2 1 3 3
26 rows selected.
SQL>
SY.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 05:16:21 CDT 2024
|