Solved

Oracle - Combine 2 queries into 1

Posted on 2015-02-05
10
165 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 74

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 74

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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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 74

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
 
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 74

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 74

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

856 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