Oracle Cross Join with various parameters

Question

SELECT tb.ID, tb.NAME, square
FROM MY_TABLE tb
         CROSS JOIN
         (SELECT POWER(tb.ID,2) as square FROM DUAL)

I want to figure out whether it is possible to do something like this, cross join particular result set to subquery while putting in value from result set to subquery.

Power function here is just for the example. Instead of it I have massive subquery that should be completed with various parameters.

This particular example fails with ORA-00904

Basically I need to add to each row of the given result set the result of subquery, but I need to complete this subquery using data from this row, so each time subquery would be completed with various data.

Answer 1

This is wrong:

(SELECT POWER(tb.ID,2) as square FROM DUAL)
              -----
              this

because that subquery doesn't "see" my_table (whose alias is tb).

---

Code that works might be e.g.

select tb.id, tb.name, x.square
from my_Table tb
cross join (select power(tb2.id, 2) as square from my_table tb2) x;

but ... you didn't say what you actually expect as result.

Answer 2

In Oracle 11, you could derive the column without a join:

SELECT tb.ID,
       tb.NAME,
       POWER(tb.ID,2) AS square
FROM   MY_TABLE tb

From Oracle 12, if you want to use a sub-query then you can use a LATERAL join:

SELECT tb.ID,
       tb.NAME,
       square
FROM   MY_TABLE tb
       CROSS JOIN LATERAL (
         SELECT POWER(tb.ID,2) as square FROM DUAL
       )

or APPLY:

SELECT tb.ID,
       tb.NAME,
       square
FROM   MY_TABLE tb
       CROSS APPLY (
         SELECT POWER(tb.ID,2) as square FROM DUAL
       )