Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Join two table functions together

Hello, I am brand new to Oracle. I have two table functions that work, but I am trying to join the resulting records together.
1st Table Function:
SELECT Email FROM TABLE((idcs_client.user_profiles));
Results in:
Email as column
2nd Table Function:
Select * From Table(idcs_client.get_user_membership1('[email protected]'));
Results In:
GROUP_NAME as column
Membership1
Membership2
I am trying to join these together so I can get all the GROUP_NAMES from the 2nd table function for each email address in the first table function. The 2nd table function requires me to enter the email address in the parameter.
My desired result would be:
Email | GROUP_NAME
[email protected] | Membership1
[email protected] | Membership2
[email protected] | Membership2
[email protected] | Membership1
[email protected] | Membership2
Any idea how to join these two together?
Answers
-
If I understood you right:
select t1.group_name, t2.email from table(idcs_client.get_user_membership1(t2.email)) t1, table((idcs_client.user_profiles)) t2 /
SY.
-
I just tried this and I received:
ORA-00904: "T2"."EMAIL": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 3 Column: 48
-
Please move this to the SQL forum/space.
-
Ah, you're right:
create or replace function user_profiles return sys.OdciVarchar2List is begin return sys.OdciVarchar2List('[email protected]','[email protected]','[email protected]'); end; / create or replace function get_user_membership1( p_email varchar2 ) return sys.OdciVarchar2List is begin if p_email = '[email protected]' then return sys.OdciVarchar2List('Membership1','Membership2'); elsif p_email = '[email protected]' then return sys.OdciVarchar2List('Membership2'); elsif p_email = '[email protected]' then return sys.OdciVarchar2List('Membership1','Membership2'); end if; end; / select t1.column_value group_name, t2.column_value email from table(get_user_membership1(t2.column_value)) t1, table((user_profiles)) t2 / * ERROR at line 3: ORA-00904: "T2"."COLUMN_VALUE": invalid identifier SQL>
And I can't figure it out so far.
SY.
-
It works like this in Oracle 21XE:
select * from table(user_profiles) t1 cross apply table(get_user_membership1(t1.column_value)) Result Sequence Result Sequence -------------------- -------------------- [email protected] Membership1 [email protected] Membership2 [email protected] Membership2 [email protected] Membership1 [email protected] Membership2
-
Thanks, I was testing in 19C. And I finally figured it out. Oracle overlooks dependencies and tries to execute FROM clause tabvles in order they are listed. If we flip the order:
select t1.column_value group_name, t2.column_value email from table((user_profiles)) t2, table(get_user_membership1(t2.column_value)) t1 / GROUP_NAME EMAIL -------------------- -------------------- Membership1 [email protected] Membership2 [email protected] Membership2 [email protected] Membership1 [email protected] Membership2 [email protected] SQL>
Interestinlgly enough leading hint doesn't work:
select /*+ leading(t2,t1) */ t1.column_value group_name, t2.column_value email from table(get_user_membership1(t2.column_value)) t1, table((user_profiles)) t2 / from table(get_user_membership1(t2.column_value)) t1, * ERROR at line 4: ORA-00904: "T2"."COLUMN_VALUE": invalid identifier SQL>
On other hand why would it - it hints in what order tables to be joined not calculated.
SY.