Join two table functions together

rgiljohann

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

[email protected]

[email protected]

[email protected]

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?

Solomon Yakobson

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.

rgiljohann

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

thatJeffSmith-Oracle

Please move this to the SQL forum/space.

Solomon Yakobson

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.

cormaco

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         

Solomon Yakobson

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.