Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Oracle - Combine 2 queries into 1

Hi

I have 2 Query that i would like to put into 1.

My first query is:
 select TTFAP.LOAD_START_DATE_PERIOD, 
       TTFAP.LOAD_START_DATE_YEAR, 
       TTFAP.RATE_GROUP_ID, 
       TTFAP.ORIGIN, 
       TTFAP.DEST, 
       TTFAP.BILL, 
       TTFAP.LOAD_ID, 
       TTFAP.LOAD_TYPE, 
       TTFAP.TRIP_MODE_TYPE, 
       TTFAP.CARRIER_ID, 
       TTFAP.CARRIER_NAME, 
       TTFAP.EQUIPMENT_TYPE_ID, 
       TTFAP.NUM_STOPS,  
       TTFAP.MILES, 
       TTFAP.CUBE, 
       TTFAP.WEIGHT, 
       TTFAP.PCS, 
       TTFAP.PALLETS, 
       TTFAP.PAID_FUEL_ACCESS_COST, 
       TTFAP.PAID_LINEHAUL_COST
FROM   TMMGR.TEMP_TRANS_FIN_ACT_RPT TTFAP , ORD_LOAD OL, ORD_ITEM OI
WHERE  TTFAP.LOAD_ID = OL.LOAD_ID
AND OL.ORD_ID = OI.ORD_ID
AND LOAD_START_DATE_PERIOD = '6' AND 
       LOAD_START_DATE_YEAR = '2014' AND 
       STATUS = '70' 
  GROUP BY TTFAP.LOAD_START_DATE_PERIOD, 
       TTFAP.LOAD_START_DATE_YEAR,
       TTFAP.RATE_GROUP_ID, 
       TTFAP.ORIGIN, 
       TTFAP.DEST, 
       TTFAP.BILL, 
       TTFAP.LOAD_ID, 
       TTFAP.LOAD_TYPE, 
       TTFAP.TRIP_MODE_TYPE, 
       TTFAP.CARRIER_ID, 
       TTFAP.CARRIER_NAME, 
       TTFAP.EQUIPMENT_TYPE_ID, 
       TTFAP.NUM_STOPS, 
       TTFAP.MILES, 
       TTFAP.CUBE, 
       TTFAP.WEIGHT, 
       TTFAP.PCS, 
       TTFAP.PALLETS, 
       TTFAP.PAID_FUEL_ACCESS_COST, 
       TTFAP.PAID_LINEHAUL_COST
ORDER  BY LOAD_START_DATE_PERIOD,LOAD_START_DATE_YEAR,RATE_GROUP_ID,bill; 

Open in new window


and my second query is:
SELECT (XMLAGG(XMLELEMENT(WACS,ITEM_ID ||',')).EXTRACT('//text()')) AS WACS
FROM
  (SELECT DISTINCT OI.ITEM_ID
  FROM TMMGR.TEMP_TRANS_FIN_ACT_RPT TTFAP ,
    ORD_LOAD OL,
    ORD_ITEM OI
  WHERE TTFAP.LOAD_ID        = OL.LOAD_ID
  AND OL.ORD_ID              = OI.ORD_ID
  AND LOAD_START_DATE_PERIOD = '6'
  AND LOAD_START_DATE_YEAR   = '2014'
  AND STATUS                 = '70'
  )
ORDER BY 1

Open in new window


I would like to add the XMLAGG compilation section into the first query:
SELECT (XMLAGG(XMLELEMENT(WACS,ITEM_ID ||',')).EXTRACT('//text()')) AS WACS
FROM
  (SELECT DISTINCT OI.ITEM_ID

Open in new window


How can i do that?

Thanks again
0
Wilder1626
Asked:
Wilder1626
  • 5
  • 5
1 Solution
 
Wilder1626Author Commented:
FYI, Both queries are pulling from the same tables
0
 
sdstuberCommented:
please provide some sample data and expected results.


"combine" doesn't really tell us what you're trying to do
0
 
sdstuberCommented:
what version of the db?  If this is 11gR2 or higher, then listagg would be better than xmlagg to get your text concatenation

just taking a guess at what you want
try this...


  SELECT ttfap.load_start_date_period,
         ttfap.load_start_date_year,
         ttfap.rate_group_id,
         ttfap.origin,
         ttfap.dest,
         ttfap.bill,
         ttfap.load_id,
         ttfap.load_type,
         ttfap.trip_mode_type,
         ttfap.carrier_id,
         ttfap.carrier_name,
         ttfap.equipment_type_id,
         ttfap.num_stops,
         ttfap.miles,
         ttfap.cube,
         ttfap.weight,
         ttfap.pcs,
         ttfap.pallets,
         ttfap.paid_fuel_access_cost,
         ttfap.paid_linehaul_cost,
         LISTAGG(oi.item_id, ',') WITHIN GROUP (ORDER BY oi.item_id) wacs
    FROM tmmgr.temp_trans_fin_act_rpt ttfap, ord_load ol, ord_item oi
   WHERE ttfap.load_id = ol.load_id
     AND ol.ord_id = oi.ord_id
     AND load_start_date_period = '6'
     AND load_start_date_year = '2014'
     AND status = '70'
GROUP BY ttfap.load_start_date_period,
         ttfap.load_start_date_year,
         ttfap.rate_group_id,
         ttfap.origin,
         ttfap.dest,
         ttfap.bill,
         ttfap.load_id,
         ttfap.load_type,
         ttfap.trip_mode_type,
         ttfap.carrier_id,
         ttfap.carrier_name,
         ttfap.equipment_type_id,
         ttfap.num_stops,
         ttfap.miles,
         ttfap.cube,
         ttfap.weight,
         ttfap.pcs,
         ttfap.pallets,
         ttfap.paid_fuel_access_cost,
         ttfap.paid_linehaul_cost
ORDER BY load_start_date_period,
         load_start_date_year,
         rate_group_id,
         bill;
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Wilder1626Author Commented:
HI

I have Oracle 11.

You code make sense but I have have this error below:
ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 22 Column: 42

SELECT
  TTFAP.LOAD_START_DATE_PERIOD,
  TTFAP.LOAD_START_DATE_YEAR,
  TTFAP.RATE_GROUP_ID,
  TTFAP.ORIGIN,
  TTFAP.DEST,
  TTFAP.BILL,
  TTFAP.LOAD_ID,
  TTFAP.LOAD_TYPE,
  TTFAP.TRIP_MODE_TYPE,
  TTFAP.CARRIER_ID,
  TTFAP.CARRIER_NAME,
  TTFAP.EQUIPMENT_TYPE_ID,
  TTFAP.NUM_STOPS,
  TTFAP.MILES,
  TTFAP.CUBE,
  TTFAP.WEIGHT,
  TTFAP.PCS,
  TTFAP.PALLETS,
  TTFAP.PAID_FUEL_ACCESS_COST,
  TTFAP.PAID_LINEHAUL_COST,
  LISTAGG(OI.ITEM_ID, ',') WITHIN GROUP (ORDER BY OI.ITEM_ID) WACS
FROM
  TMMGR.TEMP_TRANS_FIN_ACT_RPT TTFAP,
  ORD_LOAD OL,
  ORD_ITEM OI
WHERE
  TTFAP.LOAD_ID            = OL.LOAD_ID
AND OL.ORD_ID              = OI.ORD_ID
AND LOAD_START_DATE_PERIOD = '6'
AND LOAD_START_DATE_YEAR   = '2014'
AND STATUS                 = '70'
GROUP BY
  TTFAP.LOAD_START_DATE_PERIOD,
  TTFAP.LOAD_START_DATE_YEAR,
  TTFAP.RATE_GROUP_ID,
  TTFAP.ORIGIN,
  TTFAP.DEST,
  TTFAP.BILL,
  TTFAP.LOAD_ID,
  TTFAP.LOAD_TYPE,
  TTFAP.TRIP_MODE_TYPE,
  TTFAP.CARRIER_ID,
  TTFAP.CARRIER_NAME,
  TTFAP.EQUIPMENT_TYPE_ID,
  TTFAP.NUM_STOPS,
  TTFAP.MILES,
  TTFAP.CUBE,
  TTFAP.WEIGHT,
  TTFAP.PCS,
  TTFAP.PALLETS,
  TTFAP.PAID_FUEL_ACCESS_COST,
  TTFAP.PAID_LINEHAUL_COST
ORDER BY
  LOAD_START_DATE_PERIOD,
  LOAD_START_DATE_YEAR,
  RATE_GROUP_ID,
  BILL;  

Open in new window


i will also work on samples for the table. This may also help.
0
 
sdstuberCommented:
>>> I have Oracle 11.

what subversion?

based on your error probably 11gR1  (11.1.0.x)


you can try just using

(XMLAGG(XMLELEMENT(WACS,oi.ITEM_ID ||',')).EXTRACT('//text()')) AS WACS

too

  SELECT ttfap.load_start_date_period,
         ttfap.load_start_date_year,
         ttfap.rate_group_id,
         ttfap.origin,
         ttfap.dest,
         ttfap.bill,
         ttfap.load_id,
         ttfap.load_type,
         ttfap.trip_mode_type,
         ttfap.carrier_id,
         ttfap.carrier_name,
         ttfap.equipment_type_id,
         ttfap.num_stops,
         ttfap.miles,
         ttfap.cube,
         ttfap.weight,
         ttfap.pcs,
         ttfap.pallets,
         ttfap.paid_fuel_access_cost,
         ttfap.paid_linehaul_cost,
         (XMLAGG(XMLELEMENT(wacs, oi.item_id || ',')).EXTRACT('//text()')) AS wacs
    FROM tmmgr.temp_trans_fin_act_rpt ttfap, ord_load ol, ord_item oi
   WHERE ttfap.load_id = ol.load_id
     AND ol.ord_id = oi.ord_id
     AND load_start_date_period = '6'
     AND load_start_date_year = '2014'
     AND status = '70'
GROUP BY ttfap.load_start_date_period,
         ttfap.load_start_date_year,
         ttfap.rate_group_id,
         ttfap.origin,
         ttfap.dest,
         ttfap.bill,
         ttfap.load_id,
         ttfap.load_type,
         ttfap.trip_mode_type,
         ttfap.carrier_id,
         ttfap.carrier_name,
         ttfap.equipment_type_id,
         ttfap.num_stops,
         ttfap.miles,
         ttfap.cube,
         ttfap.weight,
         ttfap.pcs,
         ttfap.pallets,
         ttfap.paid_fuel_access_cost,
         ttfap.paid_linehaul_cost
ORDER BY load_start_date_period,
         load_start_date_year,
         rate_group_id,
         bill;

Open in new window



or try concat_agg from this article...

http://www.experts-exchange.com/Database/Oracle/A_9391-How-to-Create-User-Defined-Aggregates-in-Oracle.html

  SELECT ttfap.load_start_date_period,
         ttfap.load_start_date_year,
         ttfap.rate_group_id,
         ttfap.origin,
         ttfap.dest,
         ttfap.bill,
         ttfap.load_id,
         ttfap.load_type,
         ttfap.trip_mode_type,
         ttfap.carrier_id,
         ttfap.carrier_name,
         ttfap.equipment_type_id,
         ttfap.num_stops,
         ttfap.miles,
         ttfap.cube,
         ttfap.weight,
         ttfap.pcs,
         ttfap.pallets,
         ttfap.paid_fuel_access_cost,
         ttfap.paid_linehaul_cost,
         concat_agg(oi.item_id) wacs
    FROM tmmgr.temp_trans_fin_act_rpt ttfap, ord_load ol, ord_item oi
   WHERE ttfap.load_id = ol.load_id
     AND ol.ord_id = oi.ord_id
     AND load_start_date_period = '6'
     AND load_start_date_year = '2014'
     AND status = '70'
GROUP BY ttfap.load_start_date_period,
         ttfap.load_start_date_year,
         ttfap.rate_group_id,
         ttfap.origin,
         ttfap.dest,
         ttfap.bill,
         ttfap.load_id,
         ttfap.load_type,
         ttfap.trip_mode_type,
         ttfap.carrier_id,
         ttfap.carrier_name,
         ttfap.equipment_type_id,
         ttfap.num_stops,
         ttfap.miles,
         ttfap.cube,
         ttfap.weight,
         ttfap.pcs,
         ttfap.pallets,
         ttfap.paid_fuel_access_cost,
         ttfap.paid_linehaul_cost
ORDER BY load_start_date_period,
         load_start_date_year,
         rate_group_id,
         bill;

Open in new window

0
 
Wilder1626Author Commented:
I'Il ask IT about what subversion we have.

When i use: (XMLAGG(XMLELEMENT(WACS,oi.ITEM_ID ||',')).EXTRACT('//text()')) AS WACS

It works but in the WACS result i get multiple duplicated values when they should be distinct values:
duplicated values
I would like to see for example on the first row: GDI only once,  since it shows 4 times.
0
 
sdstuberCommented:
try the concat_agg  with distinct

 concat_agg(DISTINCT oi.item_id) wacs
0
 
Wilder1626Author Commented:
Just try like below and no error. Hope i did it right.

It did run but i still see duplicates

-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
----    Concatenation Aggregate for strings (a.k.a. stragg)
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE concat_agg_type
    AS OBJECT
(
    -- This could be defined to 32767, but since the aggregate will be used in SQL,
    -- the 4000 limit is appropriate since that's all that SQL varchar2 strings support
    v_result VARCHAR2(4000),

    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_agg_type)
        RETURN NUMBER,

    MEMBER FUNCTION odciaggregateiterate(self IN OUT concat_agg_type, p_string IN VARCHAR2)
        RETURN NUMBER,

    MEMBER FUNCTION odciaggregatemerge(self IN OUT concat_agg_type, ctx2 IN concat_agg_type)
        RETURN NUMBER,

    MEMBER FUNCTION odciaggregateterminate(self          IN     concat_agg_type,
                                           returnvalue      OUT VARCHAR2,
                                           flags         IN     NUMBER
                                          )
        RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY concat_agg_type
IS
    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        ctx  := concat_agg_type(NULL);  -- initialize the concatenation to NULL
        RETURN odciconst.success;
    END odciaggregateinitialize;

    MEMBER FUNCTION odciaggregateiterate(self IN OUT concat_agg_type, p_string IN VARCHAR2)
        RETURN NUMBER
    IS
    BEGIN
        self.v_result  := self.v_result || ',' || p_string;   -- Append a delimiter and new value
        RETURN odciconst.success;
    END odciaggregateiterate;

    MEMBER FUNCTION odciaggregatemerge(self IN OUT concat_agg_type, ctx2 IN concat_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        -- If merging, simply concatenate them together
        -- Since each string will either be NULL or delimiter prefixed, no need to re-delimit
        self.v_result  := self.v_result || ctx2.v_result;
        RETURN odciconst.success;
    END odciaggregatemerge;

    MEMBER FUNCTION odciaggregateterminate(self          IN     concat_agg_type,
                                           returnvalue      OUT VARCHAR2,
                                           flags         IN     NUMBER
                                          )
        RETURN NUMBER
    IS
    BEGIN
        -- Since we prefix the string initially with a comma, remove the extra here before returning
        returnvalue  := LTRIM(self.v_result, ',');
        RETURN odciconst.success;
    END odciaggregateterminate;
END;
/
 
 CREATE OR REPLACE FUNCTION concatagg(p_string VARCHAR2)
    RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
    USING concat_agg_type;
 
    
SELECT ttfap.load_start_date_period,
         ttfap.load_start_date_year,
         ttfap.rate_group_id,
         ttfap.origin,
         ttfap.dest,
         ttfap.bill,
         ttfap.load_id,
         ttfap.load_type,
         ttfap.trip_mode_type,
         ttfap.carrier_id,
         ttfap.carrier_name,
         ttfap.equipment_type_id,
         ttfap.num_stops,
         ttfap.miles,
         ttfap.cube,
         ttfap.weight,
         ttfap.pcs,
         ttfap.pallets,
         ttfap.paid_fuel_access_cost,
         ttfap.paid_linehaul_cost,
         concatagg(oi.item_id) wacs
    FROM tmmgr.temp_trans_fin_act_rpt ttfap, ord_load ol, ord_item oi
   WHERE ttfap.load_id = ol.load_id
     AND ol.ord_id = oi.ord_id
     AND load_start_date_period = '6'
     AND load_start_date_year = '2014'
     AND status = '70'
GROUP BY ttfap.load_start_date_period,
         ttfap.load_start_date_year,
         ttfap.rate_group_id,
         ttfap.origin,
         ttfap.dest,
         ttfap.bill,
         ttfap.load_id,
         ttfap.load_type,
         ttfap.trip_mode_type,
         ttfap.carrier_id,
         ttfap.carrier_name,
         ttfap.equipment_type_id,
         ttfap.num_stops,
         ttfap.miles,
         ttfap.cube,
         ttfap.weight,
         ttfap.pcs,
         ttfap.pallets,
         ttfap.paid_fuel_access_cost,
         ttfap.paid_linehaul_cost
ORDER BY load_start_date_period,
         load_start_date_year,
         rate_group_id,
         bill;

Open in new window


Result:
cancatagg result
0
 
sdstuberCommented:
try the concatagg  with distinct

 concatagg(DISTINCT oi.item_id) wacs
0
 
Wilder1626Author Commented:
Thank you so much for your help.

I added the DISTINCT and all work perfectly well
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now