Solved

Error Creating View:  ORA-00933: SQL command not properly ended

Posted on 2008-06-13
3
1,146 Views
Last Modified: 2008-09-13
Hi all,

I have a view I am trying to create using TOAD, or SQL Plus for that matter.  I get the error message:  Error Creating View:  ORA-00933: SQL command not properly ended  If I just run the select statement, it works fine.  Please help!!!

Here is the full code:

CREATE VIEW APPS.REPORTS_EMPLOYEECLOCK
AS
SELECT p2.full_name, p.full_name, sum(t.clock), sum(t.pay), sum(t.labor) from (select distinct person_id, full_name from hr.per_all_people_f where current_employee_flag = 'Y') p, hr.per_all_assignments_f a, (select distinct person_id, full_name, person_type_id from hr.per_all_people_f where current_employee_flag = 'Y') p2, (select c.person_id, c.time_in, nvl(c.total_hours,0) as clock, nvl(c.supervisor_hours,0) as pay, sum(nvl(l.break_adjusted_time_out - l.time_in,0)*24) as labor from ctl.timeclock_clock_card c, ctl.timeclock_labor_card l where c.person_id = l.person_id(+) and l.time_in(+) between c.time_in and c.time_out group by c.person_id, c.time_in, c.total_hours, c.supervisor_hours) t where t.person_id = p.person_id and t.person_id = a.person_id(+) and a.supervisor_id = p2.person_id(+) group by p2.full_name, p.full_name order by p2.full_name, p.full_name;
0
Comment
Question by:jwebster77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 21779808
Perhaps if you format your code better you may be able to see where the problem lies. I don't know your logic so only you can check for logical errors.

See the following as my interpretation of what you're trying to do and see if it's the wrong idea:


CREATE VIEW APPS.REPORTS_EMPLOYEECLOCK
AS
SELECT 
	p2.full_name, 
	p.full_name, 
	sum(t.clock), 
	sum(t.pay), 
	sum(t.labor) 
from (select 
		distinct person_id, 
		full_name 
	from hr.per_all_people_f 
	where current_employee_flag = 'Y') p, 
		hr.per_all_assignments_f a, 
		(select 
			distinct person_id, 
			full_name, 
			person_type_id 
		from hr.per_all_people_f 
		where current_employee_flag = 'Y') p2, 
			(select c.person_id, 
				c.time_in, 
				nvl(c.total_hours,0) as clock, 
				nvl(c.supervisor_hours,0) as pay, 
				sum(nvl(l.break_adjusted_time_out - l.time_in,0)*24) as labor 
			from ctl.timeclock_clock_card c, 
				ctl.timeclock_labor_card l 
			where c.person_id = l.person_id(+) 
				and l.time_in(+) between c.time_in 
				and c.time_out 
			group by c.person_id, c.time_in, c.total_hours, c.supervisor_hours) t 
where t.person_id = p.person_id 
	and t.person_id = a.person_id(+) 
	and a.supervisor_id = p2.person_id(+) group by p2.full_name, p.full_name 
order by p2.full_name, p.full_name;

Open in new window

0
 
LVL 4

Expert Comment

by:jtrifts
ID: 21785454
Does the DML work without the "APPS." prefix?
i.e.
CREATE VIEW REPORTS_EMPLOYEECLOCK
AS
SELECT p2.full_name
, p.full_name
, sum(t.clock)
, sum(t.pay)
, sum(t.labor)
from (select distinct person_id
                   , full_name
         from hr.per_all_people_f
         where current_employee_flag = 'Y') p
, hr.per_all_assignments_f a
, (select distinct person_id
              , full_name
              , person_type_id
   from hr.per_all_people_f
   where current_employee_flag = 'Y') p2
, (select c.person_id
             , c.time_in
              , nvl(c.total_hours,0) as clock
              , nvl(c.supervisor_hours,0) as pay
              , sum(nvl(l.break_adjusted_time_out - l.time_in,0)*24) as labor
   from ctl.timeclock_clock_card c
         , ctl.timeclock_labor_card l
   where c.person_id = l.person_id(+)
   and l.time_in(+) between c.time_in and c.time_out
   group by c.person_id, c.time_in, c.total_hours, c.supervisor_hours) t
where t.person_id = p.person_id
and t.person_id = a.person_id(+)
and a.supervisor_id = p2.person_id(+)
group by p2.full_name, p.full_name
order by p2.full_name, p.full_name;
0
 

Accepted Solution

by:
jwebster77 earned 0 total points
ID: 21848455
The problem was an issue with the Toad software an not the query itself.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question