Forum Stats

  • 3,824,917 Users
  • 2,260,439 Discussions
  • 7,896,346 Comments

Discussions

Join two table functions together

rgiljohann
rgiljohann Member Posts: 3 Green Ribbon

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?

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,455 Red Diamond

    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
    rgiljohann Member Posts: 3 Green Ribbon

    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
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,571 Employee

    Please move this to the SQL forum/space.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,455 Red Diamond

    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
    cormaco Member Posts: 1,939 Silver Crown

    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
    Solomon Yakobson Member Posts: 19,455 Red Diamond

    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.