Skip to Main Content
Forums

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!

Active records by max insert date

Akbar JalaluddinOct 26 2021

Gurus,
I am trying to write a query to identify the max (inserted date) records for each ID. Once those records are identified, I want to set the active indicator to 0 for the remaining dates. Below is an example.
image.png
In the above pic, M_ID 123456 has ACT_IND 1 for 2 ID_NO 1 & 2 with different inserted dates. I want to identify the max inserted date record and update the other record to ACT_IND=0. Which means ID_No 2 should only have ACT_IND=1 as it has max inserted date of 23 oct 21. ID_No 1 should have ACT_IND=0.

Similarly for M_ID 234567 as three records having ACT_IND=1. Of the three, only the latest record which is ID_NO 5 inserted on 12-JUL-21 should have ACT_IND=1 and the other should be 0.
There could be only one active record with ACT_IND=1 but can be ignored. The issue is that somehow need to identify the M_ID's that have multiple inserted dates, retain the max inserted date active indicator to 1 and set the other to 0.
Expected output as below.
image.pngScripts as below:
create table test_data
(
id_no int,
m_id varchar2(20 byte) ,
act_ind int,
inserted_dt date);

INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('1', '123456', '1', TO_DATE('2021-10-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('2', '123456', '1', TO_DATE('2021-10-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('3', '234567', '1', TO_DATE('2021-07-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('4', '234567', '1', TO_DATE('2021-07-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('5', '234567', '1', TO_DATE('2021-07-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('6', '345678', '1', TO_DATE('2021-04-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('7', '334455', '1', TO_DATE('2021-01-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('8', '334455', '1', TO_DATE('2021-05-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;
Any suggestions please?

Comments

Nigel Deakin-Oracle
Are the two GlassFish instances configured to be part of the same GlassFish cluster? If so then you don't need to do anything. MyQueue will work as a single queue for both instances.

If the two GlassFish instances are NOT clustered then you have two separate queues, both with the JNDI name MyQueue. In this case you need to either

1. Configure the sending application to send messages to the other instance
2. Configure the MDB to receive messages from the other instance

(obviously you must do one or the other, not both!).

To define which instance a MDB receives messages from, set the activation config property connectionURL to point to the other instance. This will have the form mq://hostname:jmsport

To define which instance an application sends messages to, set the connection factory property addressList to point to the other instance. Again, this will have the form mq://hostname:jmsport

Don't forget that when you create the Queue object with JNDI name jms/MyQueue you need to define a property "Name" and set it to the actual name of the queue.

Nigel
883902
Hi,

The first thing I would like to suggest is, we never/can't send message directly to MDB. We always send/receive message(s) to/from the destination. In case of MDB, the application server register the MDB as message listener against particular destination, and invoked the onMessage() method when message arrives on particular destination.

If I am not wrong, what you want to achieve is asynchronous communication between two applications deployed in two different machine. In your case, you want your one application works as message producer and other act as an message consumer, and both are deployed in different machine.

In this situations, you only need to configure the JMS administration object, connection factories and destinations, on one Application server and configure other application server to use those configuration using JMS resource adapter. you can well achieve this by configuring the JMS Resource Adapter. you need to check the Glassfish documentation.

If you don't want to break the JMS specification, always communicate with Messaging Provider through Resource Adapter.

I hope this helps.
1 - 2

Post Details

Added on Oct 26 2021
#plsql, #sql
14 comments
366 views
3 mentions