OraFAQ Forum: SQL & PL/SQL » sql query with where condition

Home » SQL & PL/SQL » SQL & PL/SQL » sql query with where condition

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql query with where condition [message #587077]

Wed, 12 June 2013 09:11

satyam_mitm@yahoo.com
Messages: 1
Registered: June 2013
Location: India

Junior Member

Hi All,

I have EMPLOYEE table that have 3 records with EMP_ID 1, 2, 3.
Now I want to run below query

select emp_id from employee where emp_id in (1, 2, 3, 4, 5);

It will return only 3 records but i want those records also which is not available in employee table.
Is this possible wihtout using another table or creating another table.
Actually I don't have enough priveleges to create table.

& want output like below

EMP_ID
1
2
3
4 Not Found
5 Not Found

Here emp_id 4, 5 is not available in employee table, but query should return those value also with comments like "Not Found"

Report message to a moderator

Re: sql query with where condition [message #587078 is a reply to message #587077]

Wed, 12 June 2013 09:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: sql query with where condition [message #587080 is a reply to message #587077]

Wed, 12 June 2013 09:16

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select nvl(to_char(e.empno), column_value||' Not found') empno
  2  from table(sys.odcinumberlist(7369,7370,7566,7555)) l
  3       left outer join
  4       emp e
  5       on e.empno = l.column_value
  6  order by 1
  7  /
EMPNO
--------------------------------------------------
7369
7370 Not found
7555 Not found
7566

4 rows selected.

Regards
Michel

[Updated on: Wed, 12 June 2013 09:17]

Report message to a moderator

Re: sql query with where condition [message #587122 is a reply to message #587080]

Wed, 12 June 2013 10:49

rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com

Senior Member

Hi Michel

A small doubt on this whether this function can be used in other schema as this function is available in sys , whether is this an oracle documented function .If yes can this function be used from other schema by creating synonym for that

[Updated on: Wed, 12 June 2013 10:50]

Report message to a moderator

Re: sql query with where condition [message #587123 is a reply to message #587122]

Wed, 12 June 2013 10:50

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, this is documented and supported.
No, you do not create a synonym on a query but a view for the query (then you can create a synonym for the view).

Regards
Michel

[Updated on: Wed, 12 June 2013 10:51]

Report message to a moderator

Re: sql query with where condition [message #587124 is a reply to message #587122]

Wed, 12 June 2013 10:51

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Michel won't have run that code in sys, so yes you can use it in other schemas - just try it.

Report message to a moderator

Re: sql query with where condition [message #587126 is a reply to message #587124]

Wed, 12 June 2013 11:01

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Oh! I didn't understand the question.
Yes, the type "odcinumberlist" is accessible to anyone but there is no synonym on it so you have to prefix it by "SYS." as I did.

Regards
Michel

Report message to a moderator

Previous Topic:	ORDER BY
Next Topic:	ORA-14251: Specified subpartition does not exist

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 04 07:04:24 CDT 2025