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!

Condition based grouping and JSON Generation

Orcl ApexApr 19 2022

Hi All,
I have the below data set and I want to generate JSON OBJECT based on the below rules -
The first group criteria are customer
The second group criteria are PO_number
The third group criteria are - if customer 'A' then a unique combination of PO_LINE and RELEASE_NUM must come under one group e.g. first two rows in insert should come under one group of customer 'A' and 3rd should be under 2nd group of customer 'A'
For other customers, grouping is based on PO_NUMBER only.

CREATE TABLE mexl (cust VARCHAR2(10)
 , po_number NUMBER
 , line_number NUMBER
 , release_number NUMBER
 );
 
INSERT INTO mexl (cust, po_number, line_number, release_number) VALUES ('A', 1, 1, 1) ;
INSERT INTO mexl (cust, po_number, line_number, release_number) VALUES ('A', 1, 1, 1) ;
INSERT INTO mexl (cust, po_number, line_number)         VALUES ('A', 3, 1  ) ;
INSERT INTO mexl (cust, po_number, line_number, release_number) VALUES ('B', 2, 1, 1) ;
INSERT INTO mexl (cust, po_number, line_number, release_number) VALUES ('C', 1, 1, 1) ;
This post has been answered by odie_63 on Apr 20 2022
Jump to Answer
Comments
Post Details
Added on Apr 19 2022
6 comments
476 views