asked on

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

Wilder1626

ASKER

FYI, Both queries are pulling from the same tables

Sean Stuber

please provide some sample data and expected results.

"combine" doesn't really tell us what you're trying to do

Sean Stuber

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;

Wilder1626

ASKER

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.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

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:

I would like to see for example on the first row: GDI only once, since it shows 4 times.

Sean Stuber

try the concat_agg with distinct

concat_agg(DISTINCT oi.item_id) wacs

Wilder1626

ASKER

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:

Sean Stuber

try the concatagg with distinct

concatagg(DISTINCT oi.item_id) wacs

Wilder1626

ASKER

Thank you so much for your help.

I added the DISTINCT and all work perfectly well