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

On Jan 24, 7:38*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
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.

On Jan 24, 7:38*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
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.

On Jan 24, 7:38*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
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.

On Jan 24, 7:38*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
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.

In addition to Charles's excellent idea, let me add a note:

On Jan 24, 7:38*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:

any number mod 1 is 0. Assuming that suppl is always 7 characters and
the 7th character is always numeric, what you want is

and mod(substr(suppl,7,1),2) = 1

Of course if you had the case where the seventh character was not
numeric you'd get an error. A better way might be

and substr(suppl,length(suppl),1) in ('1','3','5','7','9')

In addition to Charles's excellent idea, let me add a note:

On Jan 24, 7:38*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:

any number mod 1 is 0. Assuming that suppl is always 7 characters and
the 7th character is always numeric, what you want is

and mod(substr(suppl,7,1),2) = 1

Of course if you had the case where the seventh character was not
numeric you'd get an error. A better way might be

and substr(suppl,length(suppl),1) in ('1','3','5','7','9')

In addition to Charles's excellent idea, let me add a note:

On Jan 24, 7:38*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:

any number mod 1 is 0. Assuming that suppl is always 7 characters and
the 7th character is always numeric, what you want is

and mod(substr(suppl,7,1),2) = 1

Of course if you had the case where the seventh character was not
numeric you'd get an error. A better way might be

and substr(suppl,length(suppl),1) in ('1','3','5','7','9')

In addition to Charles's excellent idea, let me add a note:

On Jan 24, 7:38*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:

any number mod 1 is 0. Assuming that suppl is always 7 characters and
the 7th character is always numeric, what you want is

and mod(substr(suppl,7,1),2) = 1

Of course if you had the case where the seventh character was not
numeric you'd get an error. A better way might be

and substr(suppl,length(suppl),1) in ('1','3','5','7','9')