6 Replies Latest reply: Mar 8, 2013 9:00 AM by Solomon Yakobson RSS

    How to join these two queries

    Igor S.
      Hi experts,

      I need to join two queries but not sure how:
      select id from test_table1;
      
      select *
        from table(f_function(null
                             ,null
                             ,1 -- the id
                             ,sysdate);
      One query has IDs needed to run the second query. Is there a way to join those two?

      The result should be all columns from test_table1 + all columns from f_function.

      Best regards,
      Igor

      Edited by: Igor S. on Mar 8, 2013 5:18 AM
        • 1. Re: How to join these two queries
          Solomon Yakobson
          select  *
            from  test_table1,
                  table(
                        f_function(
                                   null,
                                   null,
                                   id,
                                   sysdate
                                  )
                       )
          /
          SY.
          • 2. Re: How to join these two queries
            Igor S.
            No join?
            • 3. Re: How to join these two queries
              Solomon Yakobson
              Igor S. wrote:
              No join?
              Listing comma-separated tables is Oracle join syntax. But TABLE operator makes it a join of a different flavor. Oracle will take one row from test_table1, execute function f_function, table it using table operator and join that test_table1 row to results. Then it will take next row from test_table1 and do the same.

              SY.
              • 4. Re: How to join these two queries
                Igor S.
                select  *
                  from  test_table1,
                        table(
                              f_function(
                                         null,
                                         null,
                                         id,
                                         sysdate
                                        )
                             )
                / 
                select  *
                  from  test_table1,
                        table(
                              f_function(
                                         null,
                                         null,
                                         id,
                                         sysdate
                                        ) xyz
                             )
                 where test_table1.id = xyz.id
                /
                So these two queries are the same?
                • 5. Re: How to join these two queries
                  Frank Kulash
                  Hi,
                  Igor S. wrote:
                  select  *
                  from  test_table1,
                  table(
                  f_function(
                  null,
                  null,
                  id,
                  sysdate
                  )
                  )
                  / 
                  select  *
                  from  test_table1,
                  table(
                  f_function(
                  null,
                  null,
                  id,
                  sysdate
                  ) xyz
                  )
                  where test_table1.id = xyz.id
                  /
                  So these two queries are the same?
                  Try it and see.

                  You'll find that the 2nd one produces an error. But if you change it to
                  select  *
                    from  test_table1,
                          table(
                                f_function(
                                           null,
                                           null,
                                           id,
                                           sysdate
                                          )      -- No alias here
                               ) xyz             -- Alias here, instead
                   where test_table1.id = xyz.id
                  /
                  Then, assuming f_function produces a column called id, it will work.

                  Whether it produces the same results or not depends on what the function returns, and whether either id is NULL.
                  If the id column that the function returns is the same as the id value that you pass to it, and is never NULL, then the 2 queries will produce the same results.

                  Either way, each row of test_table1 will be joined to each row that the function produces with the argument(s) from that row. The column names produced by the function and the values in those columns are determined by the function; they do not need to have anything in common with any table. In practice, a function like f_function will usually not return an column that is always identical to any of its inputs, since that value is already available from the input.
                  • 6. Re: How to join these two queries
                    Solomon Yakobson
                    Igor S. wrote:
                    So these two queries are the same?
                    As Frnak already mentioned, alias is in wrong place. Other than that, I can't tell since you didn't post function result type structure. If id is one of attributes in function return type, then it will work. Otherwise it will fail.

                    SY.