Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query to get record details beased on recent update

SK K3 days ago — edited 3 days ago

Oracle version:
Oracle 19c Enterprise edition Realease 19.0.0.0.0

Create table script :
   create table item_dtls(
   item_id number,
   detailed_desc varchar2(100),
   legal_org char(16),
   related_order varchar2(100),
   update_date timestamp(6),
   start_date timestamp(6),
   end_date timestamp(6)
   );

Insert statements :

Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (101,'TEST123','200898          ','2309',to_timestamp('04-02-2020 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-02-2022 10:50:30.735000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('22-02-2026 10:50:35.931000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (101,'TEST Item','200897          ',null,to_timestamp('01-01-2023 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('10-01-2023 10:50:30.735000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('22-04-2024 10:50:35.931000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (101,null,null,'120',to_timestamp('02-02-2024 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-02-2024 10:50:30.735000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('22-04-2025 10:50:35.931000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (101,'Fleet sale',null,null,to_timestamp('21-02-2025 11:07:49.996000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-02-2026 10:50:30.735000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('31-12-2100 11:08:26.292000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (101,'IT projects','200820          ','2132',to_timestamp('21-02-2019 11:07:49.996000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-02-2024 10:50:30.735000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('31-12-2100 11:08:26.292000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (102,'compliance','3000            ','123',to_timestamp('04-02-2020 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-02-2020 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('31-12-2100 11:08:26.292000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (102,'Debt colln','200             ','111',to_timestamp('04-02-2018 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-01-2018 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('31-12-2100 11:08:26.292000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (103,'TEST Item1','100             ','12',to_timestamp('04-02-2018 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-01-2018 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('31-12-2100 11:08:26.292000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));

expected output :

In this table, based on item id we can see details of that item.
This table has different start date and end date for ech item. sometimes you may see that different rows have overlapping periods (start and end date)
Below is the expected result for item_id=101:

  1. Get min(start_date) and max(end_date) to determine the full life of the item (each item has more than one row with different or overlapping start and end dates). In every step below, recent record is identified by update_date column.
  2. Based on update_date, determine what is the most recent record.
  3. 1st record in the screenshot is for begining period (start date): 01-02-2020. For the same record, you will see end date as 22-02-2026. But in my expected result end date will change to 09-01-2023. This is because, 10-01-2023 onwards there is another version of record with latest update date. hence that record and its values to be considered.
  4. For the 2nd record, end date will be 31-01-2024 because there is another most recent record starts from 01-02-2024 based on update date.. Also note that for the 2nd record, column value - RELATED_ORDER value has come from previous row because current row has null values. Whenever there is null value, we will retrive previous version value.
  5. Aagin 4th record is same as 1st record for some portion of peiord untill we find next most recent version.
  6. record with update_date 21-02-2019 is ignored because it is older record and we found most recent than that for the start and end dates

Same steps followed for item_id=102 & 103.

can you kindly assist me on query to get desired results? Thanks a lot in advance.

This post has been answered by Frank Kulash on Feb 22 2025
Jump to Answer

Comments

James Su Feb 14 2025

Do you have a MOS account? You can see the solution here:

https://support.oracle.com/knowledge/Oracle%20Cloud/2767882_1.html

BTW the toad behavior is normal and I usually choose rollback.

ronald_2017 Feb 15 2025

No, I don't have. Can you please copy paste the solution? Thanks.

James Su Feb 15 2025

Please leave an email address.

ronald_2017 Feb 15 2025

I log in. However, it asks Support Identifier. What should I write?

Thanks

James Su Feb 16 2025

You need to purchase the service to see the content.

I may be able to help you in private but if you don't want to post your email I have no way to contact you.

ronald_2017 Feb 16 2025

First of all thanks for your help. My words are not for you. The illogical thing is why should I purchase the service in order to solve the problem which caused by Oracle. What is Oracle support is used for? Besides, it used to be send dm in Oracle forum. Apparently, it is not active now. These kinds of restrictions are really ridiculous in today's world.

BluShadow Feb 17 2025

Oracle support is for paying customers. Like most businesses, not everything is free.

A part of the contract with having support is that customers are not permitted to share the content that is given within support anywhere in public, hence why James cannot post the solution for you here.

For that error you're getting though, it's a bit of a deceptive message. You may want to look at your Firewall (or get your network guys to) as that's a likely cause.

ronald_2017 Feb 17 2025

For that error you're getting though, it's a bit of a deceptive message. You may want to look at your Firewall (or get your network guys to) as that's a likely cause.

After I login, I am redirected to registration page. I don't think it is related to firewall. It is written “Connect your User Account” and there is an item called Support Identifier. What should I type there?

Thanks.

Cookiemonster76 Feb 17 2025

I believe that BluShadow is talking about the error you're getting in toad there, not your issues with oracle support.

BluShadow Feb 17 2025

Indeed, I'm referring to the ORA-24757: duplicate transaction identifier
I'm not trying to tell you how to get into Oracle Support. For that you would need to have a customer support identifier CSID which you only get when you pay for support. If you're working for a company then maybe your company has support with Oracle? In which case your company would have someone who is the support administrator and can assign your login to have support with Oracle (they will tie up your login with the company's support identifier for you). In many companies this is a DBA who has this access.

ronald_2017 Feb 17 2025

First of all thanks for your support. The thing is, even I just select via db link I get ORA-24757 duplicate transaction identifier error. I didn't do any DML. I use it in my laptop, not for the company. It is for the educational purposes. Where should I check in firewall?

Cookiemonster76 Feb 17 2025

So where are these two DB's that you're linking together?

Is one of them on your laptop?

ronald_2017 Feb 17 2025

One is in my laptop. It is a test db. The other one is in my friend's virtual windows server. Maybe somethings need to be changed in the virtual server.

ronald_2017 6 days ago

What do you recommend? Thanks.

Cookiemonster76 5 days ago

Any and all firewalls between your laptop and the virtual windows server need to have the port(s) you're trying to use completely open.

Alternatively stop trying to link those 2 DBs.

Is there a specific reason you need to link to that DB?

If you're just trying out how DB links work you could just create a loopback link (one that connects to the same DB).

ronald_2017 3 days ago

Yes, I want to use dblink between each other. I just recreated the db link as shared this time. However, it gives another error. It works at first but then gives an error.

ronald_2017 3 days ago

I am just wondering that should I replace 'localhost' in the server's listener file with the server's IP address?

1 - 17

Post Details

Added 3 days ago
7 comments
147 views