Sql command doesn't work correctly in visual studio - oracle

Question

I want to execute this command:

SELECT        
    WORKORDERID, 
    (SELECT WORKTYPEID FROM WORK_TYPE v WHERE v.WORKTYPEID = e.WORKTYPE) w
FROM            
    WORKORDERS e

It works in SQL Server and Visual Studio but when I want to execute this command on the oracle (I have same table in oracle and SQL Server) by Visual Studio Server Explorer I got this error:

Error in SELECT clause: expression near 'SELECT'.
Error in SELECT clause: expression near 'FROM'.
Missing FROM clause. Unable to parse query text.

And after that show data correctly but it doesn't work in dataset

I know TSQL, but I know enough about Oracle to know the sysntax isn't identical so maybe your query just isn't valid in pSql. — Coding Flow
– Coding Flow, Commented Oct 17, 2013 at 11:06

Vivek Viswanathan · Accepted Answer · 2013-10-17 11:06:30Z

1

You cant specify another select query inside the columns to be selected. Try using "Join" instead.

select e.WORKORDERID, v.WORKTYPEID 
from WORKORDERS e
join WORK_TYPE v on v.WORKTYPEID =e.WORKTYPE

This should work.

answered Oct 17, 2013 at 11:06

Vivek Viswanathan

1,96318 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Przemyslaw Kruglej Over a year ago

Of course you can if the subquery returns one row. @BenRobinson exactly the same like in Oracle. There must be something else wrong with author's query. Check this SQLFiddle: sqlfiddle.com/#!4/48569/2

Nick.Mc Over a year ago

Yes that is totally valid. Oracle doesn't like extra carriage returns though.

Collectives™ on Stack Overflow

Sql command doesn't work correctly in visual studio - oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related