| Use date field to determine matching field [message #629184] |
Thu, 04 December 2014 04:08  |
 |
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Hi All,
I am trying to rebuild a self reference field that was not used correctly.
Instead of using the primary key for self reference, another field (reference_no) was used. This field is not unique (its a user counter that resets every year).
I need to add a new field (REGISTRATION_REPLY_NO) and fill it with the correct reference but I need to write an update statement that updates REGISTRATION_REPLY_NO for each record by checking for all possible reference_no records (as the reference number can repeat) and get the registration_no for the record with a CREATE_DATE that is the first larger CREATE_DATE than the CREATE_DATE of the current record ).
case example:
CREATE TABLE TEST_DOCS
(
REGISTRATION_NO VARCHAR(10) PRIMARY KEY,
TYPE NUMBER(1), --1 = INCOMING, 2 = REPLY
REFERENCE_NO NUMBER(10),
CREATE_DATE DATE,
REPLY_NO NUMBER(10)
);
INSERT ALL
INTO TEST_DOCS VALUES ('2014-31201',1 ,820,TO_DATE('02-06-2013','DD-MM-YYYY'), 850 )
INTO TEST_DOCS VALUES ('2014-31206',2 ,850,TO_DATE('05-06-2013','DD-MM-YYYY'), NULL )
INTO TEST_DOCS VALUES ('2013-88201',1 ,320,TO_DATE('28-12-2013','DD-MM-YYYY'), 8 )
INTO TEST_DOCS VALUES ('2013-00006',2 ,8,TO_DATE('05-01-2013','DD-MM-YYYY'), NULL )
INTO TEST_DOCS VALUES ('2013-78201',1 ,1000,TO_DATE('25-12-2013','DD-MM-YYYY'), 8 )
INTO TEST_DOCS VALUES ('2014-00001',2 ,8,TO_DATE('02-01-2014','DD-MM-YYYY'), NULL )
SELECT * FROM DUAL;
ALTER TABLE TEST_DOCS ADD REGISTRATION_REPLY_NO VARCHAR(10);
|
|
|
|
|
|
|
|
|
|
| Re: Use date field to determine matching field [message #629209 is a reply to message #629203] |
Thu, 04 December 2014 07:49  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
With LF's solution, if you now find the query having performance issues, then analyze the execution plan and create required index on create_date if required. You need to post more details if performance is really a concern. You can look at the sticky on top of performance tuning forum.
|
|
|
|
| Re: Use date field to determine matching field [message #629217 is a reply to message #629209] |
Thu, 04 December 2014 08:28 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Looks like you want analytics:
SQL> SELECT registration_no, TYPE, reference_no, create_date,
2 LEAD(create_date) OVER(PARTITION BY reference_no ORDER BY create_date) AS next_date,
3 LEAD(registration_no) OVER(PARTITION BY reference_no ORDER BY create_date) AS reply_registration_no
4 FROM test_docs;
REGISTRATION_NO TYPE REFERENCE_NO CREATE_DATE NEXT_DATE REPLY_REGISTRATION_NO
--------------- ---- ------------ ----------- ----------- ---------------------
2013-00006 2 8 05-Jan-2013 02-Jan-2014 2014-00001
2014-00001 2 8 02-Jan-2014
2013-88201 1 320 28-Dec-2013
2014-31201 1 820 02-Jun-2013
2014-31206 2 850 05-Jun-2013
2013-78201 1 1000 25-Dec-2013
6 rows selected
SQL>
|
|
|
|
| Re: Use date field to determine matching field [message #629317 is a reply to message #629217] |
Sun, 07 December 2014 02:30 |
|
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Thanks Lalit and Cookiemonster,
My query looks like LittleFoot's (nested selects):
SELECT t1.registration_no, t1.TYPE, t1.reference_no, t1.reply_no, t1.create_date,
(
select registration_no
from test_docs t2
where t1.REPLY_NO = t2.REFERENCE_NO
and t2.create_date = (
select min(t3.create_date)
from TEST_DOCS t3
where t3.REFERENCE_NO = t2.REFERENCE_NO
and t3.create_date >= t1.create_date
)
) My_Reply_ref_no
from test_docs t1;
However I did not manage to use Lead, or dense_rank to avoid the nesting levels. In Lead, I still need to add the condition where the reference_no of the partition is equal to the reply_no of the record.
Thanks,
Ferro
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
