Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Unanswered: What is the Equivalent to T-SQL CROSS APPLY in Oracle

    What is the Equivalent to T-SQL CROSS APPLY in Oracle

    I have some queries which need to be migrated to oracle compliant query.

    CROSS APPLY and OUTER APPLY, i read lateral joins but couldn't find an example.

    Thanks a lot for the helpful info.

  2. #2
    Join Date
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    Hi, there is no equivalent of cross apply in oracle.
    Alternatives solutions depends on what you want to do.
    If you want some help, please give more informations.

    Regards,
    Eric

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What exactly does CROSS APPLY do?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Pehaps you are referring to a cross join? Well thats really easy, just don't provide any columns to join and Oracle will join every row in the left table with every row in the right table also known as a Cartesian join.
    Code:
    select * 
      from left_table, 
           right_table;
    Note that I have not specified any join criteria.
    And here is an example in SQL*PLUS:
    Code:
    dayneo@RMSD> create table left_table(id number, value number);
    
    Table created.
    
    dayneo@RMSD> create table right_table(rid number, rvalue number);
    
    Table created.
    
    dayneo@RMSD> insert into left_table values(1, 1);
    
    1 row created.
    
    dayneo@RMSD> insert into left_table values(2, 2);
    
    1 row created.
    
    dayneo@RMSD> insert into right_table values(3, 3);
    
    1 row created.
    
    dayneo@RMSD> insert into right_table values(4, 4);
    
    1 row created.
    
    dayneo@RMSD> commit;
    
    Commit complete.
    
    dayneo@RMSD> set autotrace on explain
    dayneo@RMSD> select *
      2  from left_table,
      3       right_table;
    
            ID      VALUE        RID     RVALUE
    ---------- ---------- ---------- ----------
             1          1          3          3
             1          1          4          4
             2          2          3          3
             2          2          4          4
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3601767939
    
    ------------------------------------------------------------------------------------
    | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time   |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |             |     4 |   208 |     7   (0)| 00:00:01 |
    |   1 |  MERGE JOIN CARTESIAN|             |     4 |   208 |     7   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL  | LEFT_TABLE  |     2 |    52 |     3   (0)| 00:00:01 |
    |   3 |   BUFFER SORT        |             |     2 |    52 |     4   (0)| 00:00:01 |
    |   4 |    TABLE ACCESS FULL | RIGHT_TABLE |     2 |    52 |     2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement
    
    dayneo@RMSD>

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Cool

    Sorry, my sincere apologies I should have read your full question. CROSS APPLY is used with TVF's (table valued functions) and I assume your code is the same.

    In Oracle, the equivalent of a TVF is a PIPELINED function. The use is almost identical to SQL servers implementation.

    The following is a horribly simple example of it.
    Code:
    dayneo@RMSD> CREATE OR REPLACE TYPE date_table_type AS TABLE OF DATE
      2  /
    
    Type created.
    
    dayneo@RMSD>
    dayneo@RMSD> create or replace function mypipefnc(p_id in number) return date_table_type pipelined as
      2  begin
      3
      4     for l_row in (select created
      5                     from all_objects
      6                    where object_id = p_id) loop
      7
      8             pipe row(l_row.created);
      9
     10     end loop;
     11
     12  end mypipefnc;
     13  /
    
    Function created.
    
    dayneo@RMSD>
    dayneo@RMSD> select o.object_name, o.object_type, f.column_value
      2    from all_objects o,
      3         TABLE(mypipefnc(o.object_id)) f
      4  where o.owner not in ('PUBLIC')
      5    and rownum <= 5
      6  /
    
    OBJECT_NAME                    OBJECT_TYPE         COLUMN_VA
    ------------------------------ ------------------- ---------
    DUAL                           TABLE               30-JUN-05
    SYSTEM_PRIVILEGE_MAP           TABLE               30-JUN-05
    TABLE_PRIVILEGE_MAP            TABLE               30-JUN-05
    STMT_AUDIT_OPTION_MAP          TABLE               30-JUN-05
    RE$NV_LIST                     TYPE                30-JUN-05
    
    dayneo@RMSD>
    Obviously you can have more sophisticated return types defined, I just made it a single date column for simplicity, but you can make it any type you want. Also note that since I just used a simple table of date, the column name used in SQL will default to COLUMN_VALUE. When you specify more complex return types, the column names will be as specified in the return type.

    Hope this helps.

  6. #6
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •