• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2862
  • Last Modified:

Oracle SqlPlus Conditional Union

I have two tables (Table_rea and table_estimate) with the same fields and i need to create union including contional field with the first table.

Table 1.

select month, customer, sum(bill) as bill_month
from table_real
where date_bill between '20060901' and '20060930'
group by month,customer
union
select month, customer, sum(bill) as bill_month
from table_estimate
where date_bill between '20060901' and '20060930'
group by month,customer

The table_real in september have one record and i need join the next record estimate, but when the user make a query in octubre i do not need estimate becouse this moment i know the other real record
Example1. (situation in September)
Table_real
Month,  customer, bill
09        MR0015    $20
XX        XXXXX     XXX  (this moment i do not know this record each month we need to record)

Table_estimate
Month, customer, bill
09       MR0015    $17  (i need join this record the table_real)
-----------------------------------------------------------------------------------
Example 2 (Situation in october)
 Month,  customer, bill
09        MR0015    $20
09        MR0015    $17  (this moment i know the second record, and i do not need this record from table_estimate)

Table_estimate
Month, customer, bill
09       MR0015    $17  (i do not need join this record the table_real)
0
Rubensv
Asked:
Rubensv
1 Solution
 
Stephen LappinSenior TechnologistCommented:
I am not quite sure that I totally inderstand, but if you wish to exlcude returning a row from TABLE-ESTIMATE where a corresponing row exists  in TABLE_REAL, then the example below should work.

select month, customer, sum(bill) as bill_month
from table_real
where date_bill between '20060901' and '20060930'
group by month,customer
union
select month, customer, sum(bill) as bill_month
from table_estimate te
where date_bill between '20060901' and '20060930'
where not exists (select null
                          from table_real tr
                         where tr.month = te.month
                         and    tr.customer = te.customer
                         )
group by month,customer
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now