RE: VIEWS

Hello,

The problem is that you are trying to use a group function "sum()"
without adding the non group columns to the group by clause. 
I mean, you should add "Order_Date", "region"... And every other column
to the "group by" statement in order to work.
For example, try to issue the following statement in SQL*Plus (SCOTT
schema... ), "select ename, job, hiredate, sum (sal) from emp group by
ename, job;". You'll get an error because hiredate is not a group by
expression. 
Now issue this one "select ename, job, hiredate, sum (sal) from emp
group by ename, job, hiredate;", you'll get the desired results.

Also, the "group by" clause can act as an "order by", so you can omit if
you want your "order by" clause. And don't forget that group functions
do not include NULL column values in their computations!

Best regards,

pep0.

> -----Mensaje original-----
> De: Juan Rodriguez [mailto:jmracura@xxxxxxxxx] 
> Enviado el: lunes, 25 de noviembre de 2002 5:31
> Para: Oracle
> Asunto: [oracle] VIEWS
> 
> 
> Hi, I'm trying to create a view.  I started with the select 
> statement and 
> it works well except for when I try to add the function (sum
> (soi.quantity*unit_price)
> 
> here is my code... can anyone tell me what I am doing wrong.
> 
> Thanks a million...
> 
> select soi.id as order#, to_char(order_date,'mm/dd/yyyy')as 
> Order_Date, region, rtrim(employee.emp_fname)||' 
> '||rtrim(employee.emp_lname) as 
> Sales_Rep,
> sales_rep as Rep_ID, company_name,rtrim(fname)||' '||rtrim(lname) as 
> customer,
> '('||substr(customer.phone,1,3)||')'||substr(customer.phone,4,3)||'-
> '||substr(customer.phone,7) as phone,
>       sum(soi.quantity*unit_price) value
> from sales_order_items as soi, sales_order, employee, 
> customer, product where prod_id=product.id and 
> soi.id=sales_order.id and 
> cust_id=customer.id and
> sales_rep=emp_id
> group by soi.id, company_name
> order by soi.id
> /
> 
> Juan
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or 
> to unsubscribe send a blank email to 
> %%email.unsub%%.
> 
> 



---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to leave-oracle-1796914O@xxxxxxxxxxxxx