Home » SQL & PL/SQL » SQL & PL/SQL » I have problem with creation for View !!! (10g , xp sp2)  () 1 Vote
icon5.gif   I have problem with creation for View !!! [message #323202] Tue, 27 May 2008 13:12 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I made that code :-

SQL> create or replace view job as
2 select EMPLOYEE_ID,START_DATE,END_DATE,
3 (select JOB_TITLE from jobs
4 where JOB_ID = (select JOB_ID from job_history)) JOB_TITLE ,
5 DEPARTMENT_ID from JOB_HISTORY;

View created.

SQL> select * from job;
select * from job
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


all what I need is appear JOB_TITLE column that stored in jobs table instead of JOB_ID column that exist in job_history column.... how could I achieve that ?!
Re: I have problem with creation for View !!! [message #323203 is a reply to message #323202] Tue, 27 May 2008 13:21 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Sounds like a basic join.
Re: I have problem with creation for View !!! [message #323205 is a reply to message #323202] Tue, 27 May 2008 13:34 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you create a query, first don't use scalar subquery.
When you found a query without this then optimize it with scalar subquery.

Starting with scalar subquery is just a way to show that you don't understand SQL and you are too lazy to try to understand it.

Regards
Michel

Previous Topic: Number of columns in a table
Next Topic: Query Output Question
Goto Forum:
  


Current Time: Sat Dec 10 12:52:16 CST 2016

Total time taken to generate the page: 0.05379 seconds