OraFAQ Forum: SQL & PL/SQL » Join on TABLE OF NUMBER passed as a parameter to a function

Home » SQL & PL/SQL » SQL & PL/SQL » Join on TABLE OF NUMBER passed as a parameter to a function (Oracle 11G)

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

Join on TABLE OF NUMBER passed as a parameter to a function [message #645615]

Wed, 09 December 2015 17:02

Sam D.
Messages: 17
Registered: December 2015
Location: USA

Junior Member

Hi! I googled but cannot seem to find this. Perhaps I used wrong lingo.

I need to pass a variable length list of IDs to my function and then
JOIN the list in my select statement.

Any ideas/sample/suggestion is greatly appreciated in advance.

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645617 is a reply to message #645615]

Wed, 09 December 2015 17:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Do you realize & understand the difference between SQL & PL/SQL?
You can not blindly mix & matches features from one with the other.

It is not clear to me what exactly the "list of IDs" is for within your unposted & hidden function.

How am I supposed to know what the result your function should produce?

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645618 is a reply to message #645617]

Wed, 09 December 2015 23:09

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

Senior Member
Account Moderator

It is neither clear what "join" means here.
Maybe you could search for "varying in-list".

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645668 is a reply to message #645618]

Thu, 10 December 2015 12:23

Sam D.
Messages: 17
Registered: December 2015
Location: USA

Junior Member

Hi! Sorry for late reply. I could not connect to internet.
By "list of ids" I meant a list my UI will send of records I need to retrieve. Let's say I have a table called Part.
There are parts with IDs in that table. Let's say I need to retrieve parts whose ids are 1,4,66,89 (ids and the number of ids can chance)
SELECT * FROM Part p WHERE p.ID IN (1,4,66,89 etc)

Based on my research I know I can pass a table of number. But I am not clear how to join on that table of number
Is there anything like
SELECT * FROM Part p
JOIN p_MyTableOfNumberContainingIds on p.ID = MyTableOfNumberContainingIds.ID
?

Many thanks in advance,

[Updated on: Thu, 10 December 2015 12:24]

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645669 is a reply to message #645668]

Thu, 10 December 2015 12:35

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

Senior Member
Account Moderator

Something like:

SQL> select * from emp
  2  where empno in (select * from table(sys.odcinumberlist(7369,7698,7839)))
  3  /
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10

3 rows selected.

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645670 is a reply to message #645669]

Thu, 10 December 2015 12:38

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

Senior Member
Account Moderator

Forgot the moderator bit:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645672 is a reply to message #645669]

Thu, 10 December 2015 12:56

Sam D.
Messages: 17
Registered: December 2015
Location: USA

Junior Member

Thank you, Michel.
My issue is that the list of ids must be passed into the function and it might contain from 1 to variable number of ids.
How do I do that?

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645673 is a reply to message #645672]

Thu, 10 December 2015 12:59

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

Senior Member
Account Moderator

I fail to see how your issue differs from the solution I gave.
You have to post a test case that shows your problem to make us understand.

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645674 is a reply to message #645673]

Thu, 10 December 2015 13:02

Sam D.
Messages: 17
Registered: December 2015
Location: USA

Junior Member

There is no solution yet. I am unable to find the solution. Going back to your example, how would you pass a list of ids to your query?
Now you hardcoded the list (7369,7698,7839). What would the signature of your function look like?

Many thanks in advance,

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645675 is a reply to message #645674]

Thu, 10 December 2015 13:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Is you list the result set from a SELECT statement?

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645676 is a reply to message #645675]

Thu, 10 December 2015 13:12

Sam D.
Messages: 17
Registered: December 2015
Location: USA

Junior Member

No, it's passed from UI to Business Object to DBAccess Layer which calls Oracle functions

Report message to a moderator

Re: Join on TABLE OF NUMBER passed as a parameter to a function [message #645678 is a reply to message #645674]

Thu, 10 December 2015 13:15

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

Senior Member
Account Moderator

I hard coded the list to show you as I have no list but if you post a test case I will get the list from it.
In my example "sys.odcinumberlist(7369,7698,7839)" is your "p_MyTableOfNumberContainingIds".

Report message to a moderator

Previous Topic:	merging the column in SQL
Next Topic:	+ 0 in the where clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 25 13:04:16 CDT 2026