CODEWITHSUNDEEP
sqloracle-database
jaychando
jaychando

Reputation: 11

reduce output using SQL query

data in tables is:

Ticket table

Ticket_Nbr            ,  aaa.pnr_ticket_id
2303167095666         ,  5889503
2         ,  2345
3         ,  3456
4         ,  4567
5         ,  5678

Coupon table

CPN_FRM_SER       ,  CPN_NUM,  FRM_CITY,  TO_CITY
2303167095666     ,  1      ,  SCL     ,  PTY
2303167095666     ,  2      ,  PTY     ,  SFO
2303167095666     ,  3      ,  SFO     ,  SEA
2303167095666     ,  4      ,  SEA     ,  SFO

Query is:

    SELECT 
    XMLElement
    (
        "Eticket", 
        XMLElement("Ticket", XMLAttributes(t.ticket_nbr AS "NBR")), 
        (
            SELECT XMLAGG(XMLElement("COUPON", XMLAttributes(c.cpn_num AS "COUPON_NUM", c.frm_city AS "FROM_CITY", c.to_city AS "TO_CITY")))
            FROM coupon c
            WHERE c.cpn_frm_ser = t.ticket_nbr
        ) AS "coupon"
    )
FROM ticket t,
    coupon c
WHERE c.cpn_frm_ser = t.ticket_nbr
    AND t.aaa_pnr_ticket_id = '5889503';

Output generated is:

<Eticket>
  <Ticket NBR="2302167019566"></Ticket>
  <AAA_PNR_TICKET ISSUE_DATE="2017-02-13" HA_CC="CM"></AAA_PNR_TICKET>
  <COUPON COUPON_NUM="1" FROM_CITY="SCL" TO_CITY="PTY"></COUPON>
  <COUPON COUPON_NUM="2" FROM_CITY="PTY" TO_CITY="SFO"></COUPON>
  <COUPON COUPON_NUM="3" FROM_CITY="SFO" TO_CITY="SEA"></COUPON>
  <COUPON COUPON_NUM="4" FROM_CITY="SEA" TO_CITY="SFO"></COUPON>
</Eticket>
<Eticket>
  <Ticket NBR="2302167019566"></Ticket>
  <AAA_PNR_TICKET ISSUE_DATE="2017-02-13" HA_CC="CM"></AAA_PNR_TICKET>
  <COUPON COUPON_NUM="1" FROM_CITY="SCL" TO_CITY="PTY"></COUPON>
  <COUPON COUPON_NUM="2" FROM_CITY="PTY" TO_CITY="SFO"></COUPON>
  <COUPON COUPON_NUM="3" FROM_CITY="SFO" TO_CITY="SEA"></COUPON>
  <COUPON COUPON_NUM="4" FROM_CITY="SEA" TO_CITY="SFO"></COUPON>
</Eticket>
<Eticket>
  <Ticket NBR="2302167019566"></Ticket>
  <AAA_PNR_TICKET ISSUE_DATE="2017-02-13" HA_CC="CM"></AAA_PNR_TICKET>
  <COUPON COUPON_NUM="1" FROM_CITY="SCL" TO_CITY="PTY"></COUPON>
  <COUPON COUPON_NUM="2" FROM_CITY="PTY" TO_CITY="SFO"></COUPON>
  <COUPON COUPON_NUM="3" FROM_CITY="SFO" TO_CITY="SEA"></COUPON>
  <COUPON COUPON_NUM="4" FROM_CITY="SEA" TO_CITY="SFO"></COUPON>
</Eticket>
<Eticket>
  <Ticket NBR="2302167019566"></Ticket>
  <AAA_PNR_TICKET ISSUE_DATE="2017-02-13" HA_CC="CM"></AAA_PNR_TICKET>
  <COUPON COUPON_NUM="1" FROM_CITY="SCL" TO_CITY="PTY"></COUPON>
  <COUPON COUPON_NUM="2" FROM_CITY="PTY" TO_CITY="SFO"></COUPON>
  <COUPON COUPON_NUM="3" FROM_CITY="SFO" TO_CITY="SEA"></COUPON>
  <COUPON COUPON_NUM="4" FROM_CITY="SEA" TO_CITY="SFO"></COUPON>
</Eticket>

The same line of output data repeated 4 times.

I am looking for only one output line of data, instead of 4 lines, any help would be appreciated.

Upvotes: 1

Views: 77

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

You don't need to join the coupon table outside. Just the correlated subquery is enough.

select XMLElement("Eticket",
            XMLElement("Ticket", XMLAttributes(t.ticket_nbr as "NBR")),
            (
            select XMLAGG(
                XMLElement(
                    "COUPON", 
                    XMLAttributes(
                        c.cpn_num as "COUPON_NUM",
                        c.frm_city as "FROM_CITY",
                        c.to_city as "TO_CITY"
                        )
                    )
                )
            from coupon c
            where c.cpn_frm_ser = t.ticket_nbr
            ) as "coupon")
from ticket t
where t.aaa_pnr_ticket_id = '5889503';

Demo

Upvotes: 1

Related Questions