Outter Join in IN CLAUSE

[uform]

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.

[Steve Lewis]

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]

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]

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]

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

[Yogeeraj_1]

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