Creating Json And Editing A Complex Query (oracle 11g)

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 lis

Solution 1:

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
      INNERJOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
      INNERJOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
      LEFTJOIN 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"))))) ASCLOB 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
      INNERJOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
      INNERJOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
      LEFTJOIN 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)
      GROUPBY DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code), tpp.item_code, tpp.price) sub
WHERE sub.identifier ISNOTNULLGROUPBY sub.item_code, sub.item_price;

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

Which produces the following output:

<ItemSKU="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||'"}',',') WITHINGROUP (ORDERBY 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 (partitionby tpp.item_code) as max_request
      FROM table_price_list tpl
      INNERJOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
      INNERJOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
      LEFTJOIN 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)
      GROUPBY DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code), tpp.item_code, tpp.price, tpl.request_id) sub 
WHERE sub.identifier ISNOTNULLand sub.request_id = sub.max_request
GROUPBY sub.item_code, sub.item_price;

And an updated fiddle with this query (Link)

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