cancel
Showing results for 
Search instead for 
Did you mean: 

Outter Join in IN CLAUSE

uform
Frequent Advisor

Outter Join in IN CLAUSE

Hi,

I have the following requirement,

Select empid,empname from emp where
emp id in (E100,E200,E999)

E999 is not present in emp table
Now i need the output like this

E100, Adam
E200, Bob
E999, NA

Above is just an example, originally the "in" list will have like 800-900 items...and that info is not avbl in a oracle table.

PLS HELP.

5 REPLIES
Steve Lewis
Honored Contributor

Re: Outter Join in IN CLAUSE

It may not already be in an oracle table, but you can create a temporary table and insert the data, then use it to select from. That must be easier than a 900 line select statement.

create temp table empids (temp_id char(4));
insert into empids values("E100");
insert into empids values("E200");
insert into empids values("E999");
...etc...

Select emp.empid, emp.empname
from empids, outer emp
where
emp.emp_id = empids.temp_id
;

I haven't tested it because i'm at home, but I'm fairly confident.
Steve Lewis
Honored Contributor

Re: Outter Join in IN CLAUSE

Plus, if you create an index on your temporary table, the whole thing will go a whole lot quicker.
You can also populate the temporary table using a select statement, or an SQL load command if the emp ids are in a file not the database.
uform
Frequent Advisor

Re: Outter Join in IN CLAUSE

No, i cannot create temp tables in our database. its restricted and i need to go thru lot of approvals for creating a temp table.
Yogeeraj_1
Honored Contributor

Re: Outter Join in IN CLAUSE

hi,

once simple example is:

SQL> variable txt varchar2(50);
SQL> column "Name" format a30
SQL> exec :txt := 'Toby,Maxi,Mili,Mini'

PL/SQL procedure successfully completed.

SQL> r
1 with data
2 as
3 (
4 select substr (txt,
5 instr (txt, ',', 1, level ) + 1,
6 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1
)
7 as token
8 from (select ','||:txt||',' txt from dual)
9 connect by level <= length(:txt)-length(replace(:txt,',',''))+1
10 )
11 select nvl(empname,token) "Name", nvl(to_char(salary),'n/a') "Salary"
12 from data, emp
13* where token=empname (+)

Name Salary
------------------------------ ----------------------------------------
Toby 10000
Mili 11000
Maxi 12000
Mini n/a

SQL>


For your case, you can use the EXTERNAL table feature to achieve the desired results.

hope this helps!
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Outter Join in IN CLAUSE

hi,

one simple example is:

SQL> variable txt varchar2(50);
SQL> column "Name" format a30
SQL> exec :txt := 'Toby,Maxi,Mili,Mini'

PL/SQL procedure successfully completed.

SQL> r
1 with data
2 as
3 (
4 select substr (txt,
5 instr (txt, ',', 1, level ) + 1,
6 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1
)
7 as token
8 from (select ','||:txt||',' txt from dual)
9 connect by level <= length(:txt)-length(replace(:txt,',',''))+1
10 )
11 select nvl(empname,token) "Name", nvl(to_char(salary),'n/a') "Salary"
12 from data, emp
13* where token=empname (+)

Name Salary
------------------------------ ----------------------------------------
Toby 10000
Mili 11000
Maxi 12000
Mini n/a

SQL>


For your case, you can use the EXTERNAL table feature to achieve the desired results.

hope this helps!
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)