CODEWITHSUNDEEP
sqloracle-database
enginocal
enginocal

Reputation: 319

Combine Different SQL Queries

I have two queries, shown below. The queries select same tables, but have different conditions and columns count. I can't solve how to combine these; is there any way?

The first query is:

SELECT b.centerBranchNumber,
       b.countryCode,
       a.type,
       SUM(DECODE(a.status, 1, 1, 0)) filled
  FROM tableA a,
       tableB b
 WHERE a.branchCode = b.branchCode
   AND a.registerNumber IN (SELECT c.registerNumber
                              FROM tableC c
                             WHERE c.registerDate <= '01.02.2013'
                               AND c.state = 'A'
                               AND c.contractState != 2)
 GROUP BY b.centerBranchNumber,
          b.countryCode,
          a.type;

The results look like this:

BranchNumber     CountryCode    Type Filled
1452                 USA          0    5
1452                 USA          2    8
1452                 USA          1    16
7854                 GER          0    10
7854                 GER          1    3

The second query is:

SELECT b.centerBranchNumber,
       b.countryCode,
       a.type,
       SUM(DECODE(a.status, 0, 1, 0)) free, 
       SUM(DECODE(a.status, 2, 1, 0)) damaged    
  FROM tableA a,
       tableB b  
 WHERE a.kks_kayitdrm = 'A'    
   AND a.branchCode = b.branchCode    
   AND a.recordDate <= '01.02.2013'  
 GROUP BY b.centerBranchNumber,
           a.type,
           b.countryCode;

and the result is:

BranchNumber     CountryCode    Type Free     Damage
1452                 USA          0    5        2  
1452                 USA          2    8        1  
1452                 USA          1    16       5
7854                 GER          0    10       9  
7854                 GER          1    3        16

If I combined, I would want results as follows:

BranchNumber     CountryCode    Type   Filled    Free     Damage
1452                 USA          0      1         5        2  
1452                 USA          2      2         8        1  
1452                 USA          1      65        16       5
7854                 GER          0      7         10       9  
7854                 GER          1      45        3        16

Upvotes: 1

Views: 218

Answers (1)

Ben
Ben

Reputation: 52913

Your first query can be re-written as follows:

SELECT b.centerBranchNumber,
       b.countryCode,
       a.type,
       SUM(DECODE(a.status, 1, 1, 0)) filled
  FROM tableA a
  JOIN tableB b
    ON a.branchCode = b.branchCode
  JOIN tableC 
    ON a.registerNumber = c.registerNumber
 WHERE c.registerDate <= '01.02.2013'
   AND c.state = 'A'
   AND c.contractState != 2
 GROUP BY b.centerBranchNumber,
          b.countryCode,
          a.type

Turning your second into the ANSI syntax as well (it'll help with more complicated queries as it's extremely obvious when you've done something wrong) it becomes this:

SELECT b.centerBranchNumber,
       b.countryCode,
       a.type,
       SUM(DECODE(a.status, 0, 1, 0)) free, 
       SUM(DECODE(a.status, 2, 1, 0)) damaged    
  FROM tableA a
  JOIN tableB b
    ON a.branchCode = b.branchCode      
 WHERE a.kks_kayitdrm = 'A'
   AND a.recordDate <= '01.02.2013'  
 GROUP BY b.centerBranchNumber,
           a.type,
           b.countryCode

As you can see there's a large amount of similarity between the two queries. In order to get the result set as you want it there are two ways of doing it. Move the non-similar conditions into a CASE statement in your SUM. Alternatively, you can UNION ALL the two queries together and sum again. I would prefer to use the CASE statement as you reduce the amount of work that you have to do; the downside is that the query becomes less easy to read.

The common part of your query is:

SELECT b.centerBranchNumber,
       b.countryCode,
       a.type
  FROM tableA a
  JOIN tableB b
    ON a.branchCode = b.branchCode 
 GROUP BY b.centerBranchNumber,
          a.type,
          b.countryCode

Taking this as the base you can slowly add to it until you get the same result. You have to remember to add the same conditions to the WHERE / JOINS to ensure that you get the same result.

If we first add all your conditions; you have to change the JOIN on tableC in the first query to a LEFT OUTER JOIN. This is exactly the same as the WHERE clause will restrict the result set as if it were a INNER JOIN.

SELECT b.centerBranchNumber,
       b.countryCode,
       a.type
  FROM tableA a
  JOIN tableB b
    ON a.branchCode = b.branchCode
  LEFT OUTER JOIN tableC 
    ON a.registerNumber = c.registerNumber
 WHERE c.registerDate <= '01.02.2013'
   AND c.state = 'A'
   AND c.contractState != 2
 GROUP BY b.centerBranchNumber,
          b.countryCode,
          a.type

Adding the conditions on the second query you now need an OR in your WHERE clause:

SELECT b.centerBranchNumber,
       b.countryCode,
       a.type
  FROM tableA a
  JOIN tableB b
    ON a.branchCode = b.branchCode
  LEFT OUTER JOIN tableC 
    ON a.registerNumber = c.registerNumber
 WHERE ( c.registerDate <= '01.02.2013'
         AND c.state = 'A'
         AND c.contractState != 2 
             )
    OR ( a.kks_kayitdrm = 'A'
         AND a.recordDate <= '01.02.2013' 
             )
 GROUP BY b.centerBranchNumber,
          b.countryCode,
          a.type

Next, your dates seem to be more than a little strange. You don't seem to be doing any conversion of the string literal '01.02.2013' to a date and as it's not in the form YYYYMMDD your comparisons will not work. You're relying on your NLS_DATE_FORMAT being of the form DD.MM.YYYY, which you cannot guarantee for every session. To compare a date you should either explicitly convert using the built-in TO_DATE() function, and the appropriate format model, or an ANSI datetime literal, as I explain more fully in this answer.

Suffice it to say that Oracle explicitly recommends against using implicit conversion because:

  • SQL statements are easier to understand when you use explicit data type conversion functions.

  • Implicit data type conversion can have a negative impact on performance, especially if the data type of a column value is converted to that of a constant rather than the other way around.

  • Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.

  • Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.

To compare your dates I would therefore recommend using TO_DATE(), for instance:

WHERE ( c.registerDate <= to_date('01.02.2013', 'dd.mm.yyyy')

Lastly we can add the SUMs in; this means repeating your previous conditions within them. You can simplify this for your first query as whether something is "filled" is solely dependent on the LEFT OUTER JOIN, which means that if the registerNumber of that table, in the JOIN, is not null then the conditions are true.

In both cases I use a CASE statement as opposed to a DECODE; this is because CASE is a lot more powerful, and enforces the correct data-types, which is not guaranteed with DECODE.

Putting all this together your final query becomes:

SELECT b.centerBranchNumber
     , b.countryCode
     , a.type
     , sum(case when a.status = 1 and c.registernumber is not null then 1
                else 0
           end) as filled
     , sum(case when a.status = 0 
                     and a.kks_kayitdrm = 'A'
                     and a.recordDate <= to_date('01.02.2013', 'dd.mm.yyyy')
                     then 1
                else 0
           end) as free
     , sum(case when a.status = 2
                     and a.kks_kayitdrm = 'A'
                     and a.recordDate <= to_date('01.02.2013', 'dd.mm.yyyy')
                     then 1
                else 0
           end) as damaged
  FROM tableA a
  JOIN tableB b
    ON a.branchCode = b.branchCode
  LEFT OUTER JOIN tableC 
    ON a.registerNumber = c.registerNumber
 WHERE ( c.registerDate <= to_date('01.02.2013', 'dd.mm.yyyy')
         AND c.state = 'A'
         AND c.contractState != 2 
             )
    OR ( a.kks_kayitdrm = 'A'
         AND a.recordDate <= to_date('01.02.2013', 'dd.mm.yyyy')
             )
 GROUP BY b.centerBranchNumber
        , b.countryCode
        , a.type

You can use this approach more generically to combine queries, where it is appropriate.

Upvotes: 1

Related Questions