Home » SQL & PL/SQL » SQL & PL/SQL » Outter join in IN clause ?
icon5.gif  Outter join in IN clause ? [message #222094] Thu, 01 March 2007 13:38 Go to next message
jilpangs
Messages: 16
Registered: April 2005
Junior Member
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. PS: I cannot create temp tables for this purpose.
Re: Outter join in IN clause ? [message #222095 is a reply to message #222094] Thu, 01 March 2007 14:02 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
"PLS HELP. PS: I cannot create temp tables for this purpose."

Why not?

Is this an artificial problem?

Where are the 800 - 900 values coming from?
Re: Outter join in IN clause ? [message #222100 is a reply to message #222095] Thu, 01 March 2007 15:12 Go to previous messageGo to next message
jilpangs
Messages: 16
Registered: April 2005
Junior Member
coming frm one of our business users which they pull from their database ....to which we dont have access.
Re: Outter join in IN clause ? [message #222102 is a reply to message #222094] Thu, 01 March 2007 16:26 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Hi jilpangs,

Yup. Been there. I've had to come up with some pretty creative solutions to work around access restrictions on production systems more than a few times myself. I feel for you. Anyway, I have two solutions you can consider.

The first requires access to a VARRAY or NESTED TABLE collection type of VARCHAR2() values. Since you can't create temporary tables I'll assume you can't create your own types either. Instead, try looking in ALL_TYPES for an existing VARRAY or NESTED TABLE collection of VARCHAR2(). E.g. when I did
select * from all_types where typecode = 'COLLECTION' ;

I found one called SDO_STRING_ARRAY on my system.
desc sdo_string_array
   SDO_STRING_ARRAY VARRAY(1048576) OF VARCHAR2(32)

I'll use it in my solution.
drop table jilpangs_emp;

Table dropped.

create table jilpangs_emp as
  select 'E' || to_char(rownum) || '00' as empid, ename as empname
  from   scott.emp
;

Table created.

column empid format a10

select * from jilpangs_emp;

EMPID      EMPNAME
---------- ----------
E100       SMITH
E200       ALLEN
E300       WARD
E400       JONES
E500       MARTIN
E600       BLAKE
E700       CLARK
E800       SCOTT
E900       KING
E1000      TURNER
E1100      ADAMS
E1200      JAMES
E1300      FORD
E1400      MILLER

14 rows selected.

select
  t1.column_value as empid ,
  nvl( t2.empname, 'N/A' ) as empname
from
  table
  ( sdo_string_array -- list all your empid values here
    ( 'E100','E200','E300','E400','E500','E600','E700','E800',
      'E900','E1000','E1100','E1200','E1300','E1400',
      'E999'
    )
  ) t1,
  jilpangs_emp t2
where
  t1.column_value = t2.empid (+)
;

EMPID      EMPNAME
---------- ----------
E100       SMITH
E200       ALLEN
E300       WARD
E400       JONES
E500       MARTIN
E600       BLAKE
E700       CLARK
E800       SCOTT
E900       KING
E1000      TURNER
E1100      ADAMS
E1200      JAMES
E1300      FORD
E1400      MILLER
E999       N/A

15 rows selected.

I'm not sure if you'll bump into any system limits if you try to list 900 values in the SDO_STRING_ARRAY constructor, but it's worth a try.

The second solution assumes the empid values you want to list are sequential. It uses a FOR loop in a MODEL clause to generate a list of empids. Here's how it works.
select
  empid ,
  empname
from
  jilpangs_emp
model
  dimension by ( empid )
  measures     ( empname )
  rules upsert
  ( empname[ FOR empid LIKE 'E%' FROM 100 TO 2000 INCREMENT 100 ] =
      nvl( empname[ cv() ], 'N/A' )
  )
;

EMPID      EMPNAME
---------- ----------
E100       SMITH
E200       ALLEN
E300       WARD
E400       JONES
E500       MARTIN
E600       BLAKE
E700       CLARK
E800       SCOTT
E900       KING
E1000      TURNER
E1100      ADAMS
E1200      JAMES
E1300      FORD
E1400      MILLER
E1500      N/A
E1600      N/A
E1700      N/A
E1800      N/A
E1900      N/A
E2000      N/A

20 rows selected.

If you're not familiar with the MODEL clause you might want to read my tutorials on the subject. They start at this page.

SQL Snippets: SQL Features Tutorials - MODEL Clause

FOR loops like the one I used above are covered at

SQL Snippets: MODEL Clause - FOR Loops

You didn't mention whether the list of empid values you need to report on are sequential (e.g. 1,2,3) or mixed (e.g. 1,22,49). If they are sequential I'd go with solution #2, otherwise try solution #1.

Hope this helps. Good luck with it!

--
SnippetyJoe
http://www.sqlsnippets.com/

[Updated on: Thu, 01 March 2007 16:29]

Report message to a moderator

Re: Outter join in IN clause ? [message #222105 is a reply to message #222102] Thu, 01 March 2007 19:08 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Welcome aboard Snippety. Look forward to seeing your creative contributions.
Re: Outter join in IN clause ? [message #222240 is a reply to message #222105] Fri, 02 March 2007 07:49 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Thanks ebrian. It's good to be aboard. I think I recognize you from the OTN forums. Glad to find a familiar face here. Smile
Previous Topic: Append mode with REF CURSOR?!
Next Topic: SQL records display
Goto Forum:
  


Current Time: Sun Dec 04 18:35:55 CST 2016

Total time taken to generate the page: 0.04066 seconds