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 Go to next message
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.

Report message to a moderator

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 Go to previous messageGo to next message
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:
2015001234	3	6 2


Your requirements are not clear.

Report message to a moderator

Re: Need to calculate a Set No. from a Data set [message #633835 is a reply to message #633833] Wed, 25 February 2015 14:19 Go to previous messageGo to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member
Dear Michael....I've given the example data for only one Employee.
And for "First 3 records, I am referring to Record_No column because data of Record_No:1 to Record_No:3 and Record_No:8 is same. This is not about the database records/rows.
And finally the values are same because Record No.2,3 & 8 have same column_no and Column_Val values as that of Record_No.1.

Report message to a moderator

Re: Need to calculate a Set No. from a Data set [message #633836 is a reply to message #633835] Wed, 25 February 2015 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You did not answer my last question.
And post test case for multiple employees otherwise you may get solutions which work for a single employee in the table and won't for multiple.

Report message to a moderator

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 Go to previous messageGo to next message
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.

Report message to a moderator

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 Go to previous messageGo to next message
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.

Report message to a moderator

Re: Need to calculate a Set No. from a Data set [message #633864 is a reply to message #633836] Thu, 26 February 2015 08:13 Go to previous messageGo to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member
Dear Michael...basically this data is coming into table from a Matrix style form (developed in Forms-10g) where Record_No is entry records (returned by :System.Cursor_Record variable) in forms, Column_No is text columns (text field names) in forms and Column_Val is coming from cell text-fields values.
For each cell value I generate a separate row in database while insertion. But as there are more than one cells in a row in form, so that row number is being stored as Record_No. That's why you are seeing more than one Column_Nos and Column_Vals against one Record_No.

So, all of this data is entered by user through entry form and he doesn't enter all the records for an employee at a time, sometimes he enters data with different intervals even for the same Employee_Code.
If you wish to have more than one Employee_Codes, then you can just duplicate the provided data and only change Employee_Code arbitrarily.

I hope now scenario is clear to you.

Report message to a moderator

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 Go to previous messageGo to next message
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.
/forum/fa/12504/0/

[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 #633883 is a reply to message #633839] Thu, 26 February 2015 13:08 Go to previous messageGo to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member
Dear Solomon

Can you please give any alternative solution for WITHIN GROUP() because this is not supported in DB 10g Rel.2.
And I've tried Connect_By but I'm unable to get the required result.

Report message to a moderator

Re: Need to calculate a Set No. from a Data set [message #633884 is a reply to message #633838] Thu, 26 February 2015 13:12 Go to previous messageGo to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member
Dear Barbara

Thank you very much for so detailed solution but sadfully I can't use it because I've not sufficient privileges to create an object.
Do you have any further solution please?

Regards
Kamran IT

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
mazam wrote on Thu, 26 February 2015 14:08
And 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.

Report message to a moderator

Re: Need to calculate a Set No. from a Data set [message #633941 is a reply to message #633885] Sat, 28 February 2015 13:11 Go to previous messageGo to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member
Thanks a lot SY...Yours solution is very correct and helpful because it is the base of the solution of a bigger problem in my application.
Thanks again for your co-operation.

Report message to a moderator

Re: Need to calculate a Set No. from a Data set [message #633945 is a reply to message #633885] Sat, 28 February 2015 14:15 Go to previous messageGo to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member
Dear SY n All...Now that we've solved the generation of SET_NO problem, next I need to update SET_NO column in my table with above query for the current Employee. I'll update all the null SET_NO records with the SET_NO value generated from above query.

But now suggest me that should I use a trigger to update SET_NO column, or I should use a procedure to update table? if Procedure is better then at which event I should call that procedure?

And finally after this Updation, I'll have to generate (Unload) a text file containing all the rows of the current Employee whose records are being updated.

Same confusion, about file generation, is here that should I write the code of file generation at a DB-Trigger, or in a Procedure or at forms application level, although I always prefer to handle things at database level.

Hopefully you people will help me in solving n finalizing this problem as well.

Report message to a moderator

Re: Need to calculate a Set No. from a Data set [message #633946 is a reply to message #633945] Sat, 28 February 2015 16:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/UTL_FILE

also consider to just Read The Fine Manual

https://docs.oracle.com/apps/search/search.jsp?category=database&product=e50529-01&q=utl_file

Report message to a moderator

Re: Need to calculate a Set No. from a Data set [message #633950 is a reply to message #633946] Sat, 28 February 2015 22:32 Go to previous message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member
Dear black
Thanx for the information.
I've already read some docs about utl_file, spooling, sql*loader, webutil.client_text_io n of course from tom kyte. Thats why i m confused that which solution woiuld be best in this scenario.

Report message to a moderator

Previous Topic: need the out put based on max date
Next Topic: SQL or PL/SQL method required to fill up the values based on other table values
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 21:22:25 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.