Re: joining 2 queries

On Jan 24, 7:38 pm, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
> hi all
> i want to show all the sales from tha data i have, that meet the
> following criteria:
>
> - Product category is A or C and VAT is 12%
> - The supplier serial number (the last part after the ".") is odd.
> - The customer is from North or West
> - Customer description contains two "K"
> - The total sales value is more than 400.
> - The sale took place on a Monday, Tuesday or Wednesday.
>
> so i did it using 2 quiries as follows :
>
> 1st
> select to_char(sale_date,'Month DD, YYYY')
> "Date",to_char(sale_date,'Dy') "Day",
> customer.code, qty, prod, sale_price
> from customer,sales, product
> where sales.prod = product.code
> and customer.code = sales.customer
> and ((substr(prod,1,1)='A' or substr(prod,1,1)='C')and substr(prod,
> 3,1)=0)
> and mod(substr(suppl,7,1),1 ) = 0
> and customer.descr like '%K%K%'
> and (substr(customer.code,1,1)='A' or substr(customer.code,1,1)='B')
> and (to_char(sale_date,'Dy')='Mon' or to_char(sale_date,'Dy')='Tue' or
> to_char(sale_date,'Dy')='Wed')
>
> 2nd
> select to_char(sale_date,'Month DD, YYYY') "Date", sum
> (qty*sale_price) as "Total"
> from sales
> group by sale_date
> having sum (qty*sale_price)>400
>
> now i need to join these two together, would domebody please give any
> clue ?
> thanks in advance
> Best regards

You can create a view for the second query, and then treat the view like another table in your main query. For instance: CREATE VIEW SALES_PER_MONTH AS
select
  to_char(sale_date,'Month DD, YYYY') "Date",   sum (qty*sale_price) as "Total"
from
  sales
group by
  sale_date
having
  sum (qty*sale_price)>400;

select to_char(sales.sale_date,'Month DD, YYYY') "Date",to_char(sales.sale_date,'Dy') "Day", customer.code, qty, prod, sales.sale_price,   SPM.TOTAL
from
  customer,
  sales,
  product,
  SALES_PER_MONTH SPM
where sales.prod = product.code
and customer.code = sales.customer
and ((substr(prod,1,1)='A' or substr(prod,1,1)='C')and substr(prod, 3,1)=0)
and mod(substr(suppl,7,1),1 ) = 0
and customer.descr like '%K%K%'
and (substr(customer.code,1,1)='A' or substr(customer.code,1,1)='B') and (to_char(sales.sale_date,'Dy')='Mon' or

to_char(sales.sale_date,'Dy')='Tue' or
to_char(sales.sale_date,'Dy')='Wed')
  AND TO_CHAR(SALES.SALE_DATE,'Month DD, YYYY') = SPM.DATE;

A better way would be to not create a static view, but instead to create an inline view that exists only in your SQL statement (an inline view has a better chance to be optimized by Oracle than a static view, and is easier to maintain). To do that, just place your second SQL statement in place of "SALES_PER_MONTH" in the above SQL statement, with a "(" at the beginning of the SQL statement and ")" at the end of the SQL statement. For example:

select to_char(SALES.sale_date,'Month DD, YYYY') "Date",to_char(SALES.sale_date,'Dy') "Day", customer.code, qty, prod, SALES.sale_price,   SPM.TOTAL
from
  customer,
  sales,
  product,
  (select
    to_char(sale_date,'Month DD, YYYY') "Date",     sum (qty*sale_price) as "Total"
  from
    sales
  group by
    sale_date
  having
    sum (qty*sale_price)>400) SPM
where sales.prod = product.code
and customer.code = sales.customer
and ((substr(prod,1,1)='A' or substr(prod,1,1)='C')and substr(prod, 3,1)=0)
and mod(substr(suppl,7,1),1 ) = 0
and customer.descr like '%K%K%'
and (substr(customer.code,1,1)='A' or substr(customer.code,1,1)='B') and (to_char(SALES.sale_date,'Dy')='Mon' or

to_char(SALES.sale_date,'Dy')='Tue' or
to_char(SALES.sale_date,'Dy')='Wed')
  AND TO_CHAR(SALES.SALE_DATE,'Month DD, YYYY') = SPM.DATE;

From an efficiency perspective, it is best to not use to_char(sale_date,'Month DD, YYYY') in the inline view (or in the static view for that matter), but instead, just use SALE_DATE to avoid the performance penalty for the data type conversion. If you do that, the final WHERE clause entry will become:   AND SALES.SALE_DATE = SPM.SALE_DATE; Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jan 25 2008 - 06:08:56 CST