Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle - Combine 2 queries into 1

Posted on 2015-02-05
10
Medium Priority
?
171 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

715 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