Solved

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

Posted on 2008-06-13
3
1,133 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
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create table from select - oracle 6 65
oracle DR - data guard failover. 18 56
Read XML values 8 53
plsql job on oracle 18 72
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ā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

685 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