sql syntax

kuntilanak
kuntilanak used Ask the Experts™
on
why is it giving me this error:

ERROR at line 1:
ORA-00933: SQL command not properly ended

select itemname from x.item, (select itemno from x.sale, x.department where dname=deptname and deptfloor = 2) as t where item.itemno = t.itemno;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
I presume x is the schema name owning the tables "sale", "item" and "department" ?

something like this ...
select i itemname 
 from x.item i
 where exists ( select null
      from x.sale s
      join x.department d
        on s.dname = d.deptname 
       and d.deptfloor = 2
      where x.itemno = i.itemno 
  )  

Open in new window

Author

Commented:
why can't I use as?

Author

Commented:
can you point out the error in my original query?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
you can use "as", but the EXISTS is likely to give better performance.

how do you run the query? in pl/sql possibly?

then, you have to know that you cannot run SELECT like that inside pl/sql.

Author

Commented:
sql++
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Is this what you are trying to do?

~bp
SELECT itemname
FROM   x.item i,
       x.sale s,
       x.department d
WHERE  s.dname = d.deptname
AND    d.deptfloor = 2
AND    i.itemno = s.itemno; 

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
ok, but as pl/sql block, or simply the "SELECT" as such?

Author

Commented:
your querying method seems a bit different with mine.. I never used select i itemno, I always use select itemno
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
you should use table aliases all the time, as it makes it 100% clear from which table the column is coming from.
when you have to read the query later, you don't have to look up the table column design to know, you can just read it from the query.

Author

Commented:
angelll I have no idea what a table aliases is? is it the as?
@kuntilanak,
I think, based upon the aliased able, that the statement shouldhave started
SELECT i.itemno
I.e., a period between the "i" and he "iemno".

Author

Commented:
if you're referring to the as, yes that is my main problem.. never been successfull using as
select itemname from x.item, (select itemno from x.sale, x.department where dname=deptname and deptfloor = 2 as t) where item.itemno = t.itemno;

Open in new window

SharathData Engineer

Commented:
AngelIII's  statement - I presume x is the schema name owning the tables "sale", "item" and "department" ?

kuntilanak  - Can you confirm this?

Author

Commented:
that is true
Test your restores, not your backups...
Top Expert 2016
Commented:
To get back on track here, the problem with your original query was the "AS", I think you know that now, right?  So your original query should work by removing that, as in:

select itemname from x.item, (select itemno from x.sale, x.department where dname=deptname and deptfloor = 2) t where item.itemno = t.itemno;

That being said, it seems like you are trying to do the following:

"Looking for itemname's in x.item where they are in the x.sale table and where the deptfloor of the deptname on that sale record is a 2".

If I have that right, then why wouldn't my earlier proposal be a clean way to approach that?

SELECT i.itemname
FROM x.item i, x.sale s, x.department d
WHERE s.dname = d.deptname
AND d.deptfloor = 2
AND i.itemno = s.itemno;

Like someone else here I typically like to use aliases for qualifying the column references in any queries that have more than 1 table (and sometimes even when there is only one table).

~bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial