3 Replies - 1083 Views - Last Post: 02 April 2011 - 01:41 PM

#1 mitchnufc   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 49
  • Joined: 31-March 11

Help with creating view in Oracle

Posted 02 April 2011 - 04:06 AM

I have 2 tables that I am trying to create a view from.

There are two tables named Customer & orderheader. The primary key in customer is cust_id and there is a foreign key in orderheader table under the column order_customer which references the primary key in customer(cust_id).

I am trying to create a view that will return any orders that have taken more than 14 days to fufil here is my code:
create view excessivefull AS
select cust_firstname, cust_surname, order_id, order_date, order_completed
from customer join orderheader using (order_customer)
where order_completed - order_date >14
group by cust_firstname, cust_surname, order_date, order_completed;



when I try and run this I get an error sayingERROR at line 3:
ORA-00904: "CUSTOMER"."ORDER_CUSTOMER": invalid identifier

If you can help me this would be greatly appreciated. Thank!

This post has been edited by mitchnufc: 02 April 2011 - 04:09 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Help with creating view in Oracle

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7448
  • View blog
  • Posts: 15,442
  • Joined: 16-October 07

Re: Help with creating view in Oracle

Posted 02 April 2011 - 04:38 AM

Sounds like CUSTOMER doesn't have an ORDER_CUSTOMER column. The column names must match in order to get away with the USING clause. I'd avoid the clause; you don't see it a lot for a reason.

Regardless of your syntax choices, though, it helps to identify who owns the columns. This will make any mismatch stand out more.

e.g.
select a.cust_firstname, a.cust_surname, b.order_id, b.order_date, b.order_completed
	from customer a
		inner join orderheader b
			on a.order_customer=b.order_customer
	where b.order_completed - b.order_date >14
	group by a.cust_firstname, a.cust_surname, b.order_date, b.order_completed;



I'm guessing there's a cust_id or something you should be joining on. Also, currently, there is no reason for a group by.
Was This Post Helpful? 0
  • +
  • -

#3 mitchnufc   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 49
  • Joined: 31-March 11

Re: Help with creating view in Oracle

Posted 02 April 2011 - 05:07 AM

Thanks for the reply we have to use the systax that has been specified to use so I am unable to use the a. part. I have a foreign key in deliveryaddress which is named delivery_cust and this references customer(cust_id)
Was This Post Helpful? 0
  • +
  • -

#4 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7448
  • View blog
  • Posts: 15,442
  • Joined: 16-October 07

Re: Help with creating view in Oracle

Posted 02 April 2011 - 01:41 PM

View Postmitchnufc, on 02 April 2011 - 08:07 AM, said:

we have to use the systax that has been specified to use so I am unable to use the a. part.


A table alias is a fundamental part of SQL syntax. However, if you can't use it, then you can explicitly reference the long way:
select customer.cust_firstname, customer.cust_surname, 
		orderheader.order_id, orderheader.order_date, orderheader.order_completed
	from customer
		inner join orderheader
			on customer.order_customer = orderheader.order_customer
	where orderheader.order_completed - orderheader.order_date >14


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1