Creating JSON and editing a complex query (oracle 11g)

Question

I have 4 different tables:

table_price_product (contains information related to the products and their price)

table_price_list (contains the information related to the price list)

prices_per_client (contains the information related to the prices for the different clients given a specific product)

clients (contains the information related to the clients)

This is my SQL FIDDLE: LINK

I got this:

CUSTOMER_NUMBER  |  CUSTOMER_CLASS_CODE|    PRICE
(null)           |           A         |    29223
(null)           |           B         |    33223
112121           |           E         |    40340
119435           |           E         |    40340

Now, i want to obtain in a single query all the data related to the products and the clients, replace A,B,C etc with CLASS A, CLASS B, CLASS C respectively but if the customer_class_code is = 'E' i want to obtain the name from table clients AND finally, convert everything and get JSONs for every product. Take a look at this, somehow i would need to add column "PRICES_FOR_CLIENTS" and "groups" because i need those columns to generate the JSON.

SKU     |PRICE|PRICES_FOR_CLIENTS|groups|CLASS A|CLASS B|WALMART|SUPERMARKET
99342435|9999 |                  |      |29223  |33223  |40340  |40340

I would like to generate a JSON with that information:

{"sku":"99342435","PRICE":"9999",PRICES_FOR_CLIENTS:[{"group":"CLASS A", "PRICE":"29223"},{"group":"CLASS B", "PRICE":"33223"},{"group":"WALMART","PRICE":"40340"},{"group":"SUPERMARKET", "PRICE":"40340"}]};

Can you help me?

EDIT:

<Item SKU="99342435" Price="9999">
    <PRICES_FOR_CLIENTS>
        <CLIENT_PRICE>
            <Client>WALMART</Client>
            <Price>40340</Price>
            <Site>USSITE</Site>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>SUPERMARKET</Client>
            <Price>48343</Price>
            <Site>USSITE</Site>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>B</Client>
            <Price>33223</Price>
            <Site>USSITE</Site>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>A</Client>
            <Price>29223</Price>
            <Site>USSITE</Site>
        </CLIENT_PRICE>
    </PRICES_FOR_CLIENTS>
</Item>

Del · Accepted Answer · 2020-04-01 05:42:45Z

3

I think you can replace most of your code with the following query. You may need to adjust the IN clause, which is a pain if you are changing the customer list a lot. But this replicates your results:

SELECT *
FROM (SELECT DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, tpp.price AS ITEM_PRICE, ppc.price
      FROM table_price_list tpl
      INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
      INNER JOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
      LEFT JOIN clients c ON ppc.customer_number = c.account_number
      WHERE SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1))
PIVOT (AVG(PRICE) FOR IDENTIFIER IN ('A' AS CLASS_A , 'B' AS CLASS_B, 'SUPERMARKET' AS SUPERMARKET, 'WALMART' AS WALMART));

Here is an update fiddle.

As for the JSON output, it would be much easier if you were on a later version as it is now part of the core functionality.

EDIT: Adding XML Functionality per Comments

You could check out this query:

SELECT XMLSERIALIZE(CONTENT
                    XMLELEMENT("Item",
                               XMLATTRIBUTES(sub.item_code AS "SKU", sub.item_price AS "Price"),
                               XMLELEMENT("PRICES_FOR_CLIENTS",
                                          XMLAGG(XMLELEMENT("CLIENT_PRICE",
                                                            XMLFOREST(sub.identifier AS "Client", sub.price AS "Price"))))) AS CLOB INDENT)                                              
FROM (SELECT DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, tpp.price AS ITEM_PRICE, avg(ppc.price) AS PRICE
      FROM table_price_list tpl
      INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
      INNER JOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
      LEFT JOIN clients c ON ppc.customer_number = c.account_number
      WHERE SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1)
      GROUP BY DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code), tpp.item_code, tpp.price) sub
WHERE sub.identifier IS NOT NULL
GROUP BY sub.item_code, sub.item_price;

Here is an updated fiddle with that query (Link).

Which produces the following output:

<Item SKU="99342435" Price="9999">
    <PRICES_FOR_CLIENTS>
        <CLIENT_PRICE>
            <Client>WALMART</Client>
            <Price>40340</Price>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>SUPERMARKET</Client>
            <Price>48343</Price>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>B</Client>
            <Price>33223</Price>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>A</Client>
            <Price>29223</Price>
        </CLIENT_PRICE>
    </PRICES_FOR_CLIENTS>
</Item>

Edit 2: Adding JSON via String Concatination

The following would output JSON via direct string concatination:

SELECT '{"sku":"'||sub.item_code||'","PRICE":"'||sub.item_price||'",PRICES_FOR_CLIENTS:['||listagg('{"group":"'||sub.identifier||'","PRICE":"'||sub.price||'"}',',') WITHIN GROUP (ORDER BY sub.identifier)||']};' AS JSON                                              
FROM (SELECT DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, replace(tpp.price, ',', '.') AS ITEM_PRICE, REPLACE(avg(ppc.price), ',', '.') AS PRICE, 
      tpl.request_id, max(tpl.request_id) over (partition by tpp.item_code) as max_request
      FROM table_price_list tpl
      INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
      INNER JOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
      LEFT JOIN clients c ON ppc.customer_number = c.account_number
      WHERE SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1)
      GROUP BY DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code), tpp.item_code, tpp.price, tpl.request_id) sub 
WHERE sub.identifier IS NOT NULL
and sub.request_id = sub.max_request
GROUP BY sub.item_code, sub.item_price;

And an updated fiddle with this query (Link)

Edit 3: Added Replace **Edit 4: Added analytical function **

edited Apr 1, 2020 at 5:42

answered Mar 31, 2020 at 17:49

Del

1,6311 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

JustToKnow Over a year ago

Hey dude, thank you for replying!. Gonna check it out :)

Del Over a year ago

@Student_new To be honest, I'm not sure. Since you are on 11g, I might try XML instead of JSON. You could look into the XMLELEMENT, XMLAGG and XMLFOREST functions to replicate your JSON output. If you just need the empty columns, you can just modify the "SELECT *" to call the actual column names and just add two empty columns.

Del Over a year ago

@Student_new Is the site value hardcoded or pulled from a table? I don't see it in your schema. If it is hard coded, then just add 'USSITE' AS "Site" to the XMLFOREST. If you really need JSON, you could just force it via string concatination. I'll update my answer.

Del Over a year ago

@Student_new Dude, you're lucky you caught me on a super slow day. I added the replace into the last JSON query. I added it to both price fields.

Del Over a year ago

@Student_new Check out the analytical function that I added, it should solve your problem

|

Collectives™ on Stack Overflow

Creating JSON and editing a complex query (oracle 11g)

1 Answer 1

18 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

18 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related