We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Oracle SqlPlus Conditional Union

Rubensv
Rubensv asked
on
Medium Priority
2,918 Views
Last Modified: 2008-01-09
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)
Comment
Watch Question

Senior Technologist
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.