So you want to change the expression you use for one column, based on the values from another column?
i.e. if text = x, you want just the values from t2.x. But if text = x+y, you want t2.x+t2.y?
If so, you're going to have to use some form of dynamic SQL. This will have to lookup the expression in t1. Then create the appropriate query against t2. Here's one way you can do this:
create table t1 (
id int,
text varchar2(10)
);
create table t2 (
id int,
x number,
y number
);
insert into t1 values (1, 'X');
insert into t1 values (2, 'X+Y');
insert into t2 values (1, 1, 2);
insert into t2 values (2, 3, 4);
create or replace function f (sql_text varchar2)
return sys_refcursor as
cur sys_refcursor;
begin
open cur for sql_text;
return cur;
end;
/
select t1.*,
f('select ' || text || ' from t2 where id = ' || t1.id) t2_val
from t1
join t2
on t2.id = t1.id;
ID TEXT T2_VAL
---------- ---------- --------
1 X X
---------------------------------------
1
2 X+Y X+Y
---------------------------------------
7
I'd strongly recommend against this. Performance is likely to be bad. And you're at risk of SQL injection:
select t1.*,
f('select to_char(' || text || ') from t2 where id = ' || t1.id ||
' union select table_name from user_tables') t2_val
from t1
join t2
on t2.id = t1.id;
ID TEXT T2_VAL
---------- ---------- --------
1 X TO_CHAR(X)
------------------------------
1
ADDRESSES
CUSTOMERS
CUSTOMER_ADDRESSES
EXPORT_JOB_SQLDEV_5802
MV
SH_CHANNELS
SH_PRODUCTS
SH_PROMOTIONS
SH_TIMES
T1
T2
2 X+Y TO_CHAR(X+Y)
------------------------------
7
ADDRESSES
CUSTOMERS
CUSTOMER_ADDRESSES
EXPORT_JOB_SQLDEV_5802
MV
SH_CHANNELS
SH_PRODUCTS
SH_PROMOTIONS
SH_TIMES
T1
T2
Instead of this, write your queries explicitly! e.g.:
if input = ... then
select x from t2;
elsif input = ... then
select x+y from t2
elsif
... etc. ...
end if;