Some sqlplus errors

Question

when I run following code,

select count_ee_cnum.counts + count_eefaculty.counts + count_cs_cnum.counts + count_cs_faculty.counts
from 
    ( select count(Ex.cnum) as counts
    from enrolled Ex
    where Ex.cnum in (
    select distinct Ex.cnum
    from faculty Fx, faculty Fy, class Cx, class Cy, enrolled Ex, enrolled Ey
    where Fx.dept = 'EE' and Fy.dept = 'CS' and Cx.fid = Fx.fid and Cy.fid = Fy.fid and Ex.cnum = Cx.cnum and Ey.cnum = Cy.cnum)) count_ee_cnum,

    (select count(Fx.dept) as counts
    from faculty Fx
    where Fx.dept = 'EE') count_ee_faculty,

    (select count(Ey.cnum) as counts
    from enrolled Ey
    where Ey.cnum in (
    select distinct Ey.cnum
    from faculty Fx, faculty Fy, class Cx, class Cy, enrolled Ex, enrolled Ey
    where Fx.dept = 'EE' and Fy.dept = 'CS' and Cx.fid = Fx.fid and Cy.fid = Fy.fid and Ex.cnum = Cx.cnum and Ey.cnum = Cy.cnum)) count_cs_cnum,

    (select count(Fy.dept) counts
    from faculty Fy
    where Fy.dept = 'CS') count_cs_faculty;

The SQLPLUS gives me an error says

where Fy.dept = 'CS') count_cs_faculty
                      *
ERROR at line 3:
ORA-00933: SQL command not properly ended

I have tried many ways to eliminate this error, but however, it seems not working.

Check the braces. I think you have missed the braces from the very first count statement. — Abhijith Nagarajan
– Abhijith Nagarajan, Commented Sep 24, 2013 at 15:30

Alex Poole · Accepted Answer · 2013-09-24 16:59:02Z

To focus purely on the actual error you're currently getting, the ERROR at line 3 is a bit of a giveaway, as the line that is highlighted is about line 23.

SQL*Plus treats a blank line as the end of the statement:

A blank line in a SQL statement or script tells SQL*Plus that you have finished entering the command, but do not want to run it yet.

The first 20 lines of your script are being ignored; they are seen as three separate statements which you end (with a blank line) but never run. The last three lines are a fourth independent statement, which you do run, because of the terminating semicolon. And that statement is incomplete, fairly obviously.

You can either remove the blank lines from the script, or change how SQL*Plus treats them, by adding set sqlblanklines on to the script before this query.

Of course, you'll then need to address the issues others have raised with what the (whole) query is doing, but that's a separate subject.

Lokesh · Accepted Answer · 2013-09-24 15:40:52Z

0

This is weird query as you are just using first subquery in select and rest are just placed in from for no reason!!

I think problem lies here:

(select count(Ey.cnum) as counts
from enrolled Ey
where Ey.cnum in (
select distinct Ey.cnum
from faculty Fx, faculty Fy, class Cx, class Cy, enrolled Ex, enrolled Ey
where Fx.dept = 'EE' and Fy.dept = 'CS' and Cx.fid = Fx.fid and Cy.fid = Fy.fid and Ex.cnum = Cx.cnum and Ey.cnum = Cy.cnum)) count_cs_cnum

You are using Ey alias in nested query as well as outside, this will confuse sql engine.

answered Sep 24, 2013 at 15:40

Lokesh

7,9487 gold badges50 silver badges86 bronze badges

4 Comments

Twelfth Over a year ago

I didn't think this would cause an issue...nested alias's being the same just adds confusion to the developer and not the engine. Am i wrong with that?

Lokesh Over a year ago

You are right, it causes confusion but also we can use alias of outer query in inner query. Now in this case how does sql engine identify which alias is that? I dont see any other issue with query.

Twelfth Over a year ago

It's simply listing subqueries with no attempt to join them. The cross join cluster**** that this causes is the only other error I can see

Lokesh Over a year ago

Why is cross join an error? So if i have "Select 1 from A, B". This will be cross join but this will not be error. Agree?

Collectives™ on Stack Overflow

Some sqlplus errors

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related