?
Solved

Simplifying union query (advanced)

Posted on 2013-10-31
1
Medium Priority
?
344 Views
Last Modified: 2013-11-11
This query is having performance issues..

was wondering if anyone knows a way to simplify it

WITH DESCR AS (SELECT 
              RMCUSP.CUSREGNUM AS CHAIN, 
              RMCODP.CODLNGDES AS CHAIN_DESCRIPTION
          FROM 
            RMCODP, 
            RMCUSP  
          WHERE CUSREGNUM = CODKEYNUM AND CODCODTYP='RN'
          GROUP BY CUSREGNUM, CODLNGDES),
          
          
 BRANDS AS (SELECT  DISTINCT
                    RMITMP.ITMITMBRD AS BRAND_NO, 
                    RMCODP.CODLNGDES AS BRAND
                    FROM
                    RMITMP,
                    RMCODP
         WHERE ITMITMBRD = CODKEYNUM AND CODCODTYP = 'IB'), 
          
     ORDERS AS (
  
     SELECT 
                RMCUSP.CUSREGNUM AS CHAIN, 
                RMCODP.CODLNGDES AS CHAIN_DESCRIPTION, BRAND,
                round((Sum(RMORDP.ORDORDQTY/RMDCIP.DCICASFAC)),2) AS ORDERS,
                RMORHP.ORHDELDTE AS ORD_DATE
            FROM 
                RMCODP,
                RMCUSP,
                RMITMP, 
                RMORDP,
                RMORHP,
                RMDCIP,
                BRANDS
            WHERE CODKEYNUM = CUSREGNUM 
              AND ORDITMNUM = ITMITMNUM 
              AND CUSCUSNUM = ORHCUSNUM 
              AND CUSCUSCHN = ORHCUSCHN 
              AND ORHORDNUM = ORDORDNUM 
              AND DCIDSTCTR = ORHDSTCTR 
              AND DCIITMNUM = ORDITMNUM 
              AND BRAND_NO = ITMITMBRD
              AND CODCODTYP='RN' 
              AND CUSREGNUM <> 185 
              AND ORDITMNUM NOT IN(98,99, 994) 
              AND ORHDELDTE BETWEEN 20131013 AND 20131019
              AND DCICASFAC<>0  
            GROUP BY RMCUSP.CUSREGNUM, RMCODP.CODLNGDES, RMCODP.CODCODTYP,RMORHP.ORHDELDTE,BRAND    
UNION ALL 
           SELECT 
              CUSREGNUM AS CHAIN, 
              CODLNGDES,BRAND,
              round((Sum(RMORDP.ORDORDQTY/RMDCIP.DCICASFAC)),2) AS ORDERS , RMORHP.ORHDELDTE AS ORD_DATE 
           FROM RMCODP, RMCUSP, RMITMP, RMORDP, RMORHP,RMDCIP ,BRANDS 
          WHERE CODKEYNUM = CUSREGNUM 
            AND ORDITMNUM = ITMITMNUM 
            AND CUSCUSNUM = ORHCUSNUM 
            AND CUSCUSCHN = ORHCUSCHN 
            AND ORHORDNUM = ORDORDNUM 
            AND DCIDSTCTR = ORHDSTCTR 
            AND DCIITMNUM = ORDITMNUM 
            AND BRAND_NO = ITMITMBRD
            AND CODCODTYP = 'RN' 
            AND ITMPRDGRP <> 480 
            AND CUSREGNUM = 185 
            AND ORHDELDTE BETWEEN 20131013 AND 20131019 
            AND DCICASFAC<>0 
            AND ORDITMNUM NOT IN(98,99, 994) 
            GROUP BY CUSREGNUM, CODLNGDES, CODCODTYP, RMORHP.ORHDELDTE, BRAND),
  
SALES AS (     SELECT CUSREGNUM AS CHAIN, 
            CODLNGDES, BRAND, RMSLDP.SLDDELDTE AS SALE_DATE ,
            round((Sum(RMSLDP.SLDTRNQTY/RMDCIP.DCICASFAC)),2) AS SALES  
       FROM 
            RMCODP, 
            RMCUSP,
            RMITMP, 
            RMSLDP, 
            RMDCIP,
            BRANDS
      WHERE SLDITMNUM = DCIITMNUM 
        AND CODKEYNUM = CUSREGNUM 
        AND SLDITMNUM = ITMITMNUM 
        AND CUSCUSNUM = SLDCUSNUM 
        AND CUSCUSCHN = SLDCUSCHN
       AND CUSDFTDCN = DCIDSTCTR
        AND BRAND_NO = ITMITMBRD
        AND CODCODTYP='RN' 
        AND (CUSREGNUM <> 185) 
        AND SLDITMNUM NOT IN(98,99, 994) 
        AND SLDDELDTE BETWEEN 20131013 AND 20131019  
        AND DCICASFAC <> 0  
   GROUP BY CUSREGNUM, CODLNGDES, CODCODTYP , RMSLDP.SLDDELDTE ,BRAND  
  
  UNION ALL                         
  
     SELECT CUSREGNUM AS CHAIN, 
            CODLNGDES, BRAND, RMSLDP.SLDDELDTE AS SALE_DATE,
            round((Sum(RMSLDP.SLDTRNQTY/RMDCIP.DCICASFAC)),2) AS SALES 
       FROM RMCODP, RMCUSP, RMITMP, RMSLDP , RMDCIP , BRANDS 
      WHERE SLDITMNUM = DCIITMNUM 
        AND CODKEYNUM = CUSREGNUM 
        AND SLDITMNUM = ITMITMNUM 
        AND CUSCUSNUM = SLDCUSNUM 
        AND CUSCUSCHN = SLDCUSCHN 
        AND CUSDFTDCN = DCIDSTCTR  
        AND BRAND_NO = ITMITMBRD
        AND CODCODTYP = 'RN' 
        AND ITMPRDGRP <> 480 
        AND CUSREGNUM = 185 
        AND SLDDELDTE BETWEEN 20131013 AND 20131019 
        AND DCICASFAC <> 0    
        AND SLDITMNUM NOT IN(98,99, 994) 
   GROUP BY CUSREGNUM, CODLNGDES, CODCODTYP, RMSLDP.SLDDELDTE, BRAND)
   
   
   
select descr.chain,
       descr.chain_description,
      orders.ord_date,
       sales.sale_date,
      orders.orders,
       sales.sales,
       sales.brand
  from (descr LEFT OUTER join orders on orders.chain = descr.chain) LEFT OUTER join sales on sales.chain = descr.chain
where ord_date = sale_date and orders.brand = sales.brand
 
From: John Terrero [mailto:johnt@dorasnaturals.com] 
Sent: Thursday, October 31, 2013 1:22 PM
To: julios@dorasnaturals.com
Subject: try this
 
 
SELECT 
     chain,
     chain_description, 
     round(sum(orders)) orders,
     round(sum(sales)) sales, 
     round(sum(orders - sales)*-1) as difference 
FROM casediflv0
WHERE ord_date BETWEEN 20131013 AND 20131019
GROUP BY 
chain, chain_description
ORDER BY chain
 
 
 

Open in new window

0
Comment
Question by:FutureDBA-
[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
1 Comment
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39615691
A side note: it may not improve performance, but the syntax like this:
FROM 
                RMCODP,
                RMCUSP,
                RMITMP, 
                RMORDP,
                RMORHP,
                RMDCIP,
                BRANDS

Open in new window

is now considered not standard. To make it ANSI-compliant you should switch to INNER JOIN. As I said it is a syntax change only and may not improve performance.

To check the performance please run this query with EXPLAIN PLAN. Then check what is taking too much time. You may need to add some indices to tables
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

777 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