Solved

Oracle - Combine 2 queries into 1

Posted on 2015-02-05
10
159 Views
Last Modified: 2015-02-05
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
Comment
Question by:Wilder1626
  • 5
  • 5
10 Comments
 
LVL 11

Author Comment

by:Wilder1626
ID: 40591938
FYI, Both queries are pulling from the same tables
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40591939
please provide some sample data and expected results.


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

Expert Comment

by:sdstuber
ID: 40591957
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 40591994
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40592034
>>> 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 11

Author Comment

by:Wilder1626
ID: 40592065
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 40592077
try the concat_agg  with distinct

 concat_agg(DISTINCT oi.item_id) wacs
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40592118
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 40592135
try the concatagg  with distinct

 concatagg(DISTINCT oi.item_id) wacs
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 40592153
Thank you so much for your help.

I added the DISTINCT and all work perfectly well
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now