Oracle 12c - SQL string build issue

Question

This is a follow up to a previous question: Dynamically build select statement in Oracle 12c

I am trying to build a select statement dynamically but having a problem building the column alias names. The column alias name must be retrieved from MAIN_TABLE. Please refer to the code inside <> below:

declare
    upper_level number;
    t_sql varchar2(1000);
    t_sql_val varchar2(500);
    l_sql varchar2(1000);
begin

--upper_level will always be given
    select 3 into upper_level from dual;

--build the fixed string
    l_sql:='SELECT ID,
     Title,
     Desc,
     Type,';


    for lvl in 1..upper_level
     loop
--build the column names and alias names
         t_sql:=t_sql||'TYPE_'||lvl||' <SELECT TYPE_'||lvl||' FROM MAIN_TABLE WHERE ID = 1>,';

     end loop;

--finish building the statement     
     t_sql:=rtrim(t_sql,',');
     l_sql:=l_sql||t_sql;
     l_sql:=l_sql||' FROM SCHEMA.TABLE
    WHERE ID = 1;';

--dbms_output.put_line(l_sql);

end;

This was my attempt:

declare
    upper_level number;
    t_sql varchar2(1000);
    v_sql varchar2(1000);
    v_sql_val varchar2(1000);
    t_sql_val varchar2(500);
    l_sql varchar2(1000);
begin

--upper_level will always be given
    select 3 into upper_level from dual;

--build the fixed string
    l_sql:='SELECT ID,
     Title,
     Desc,
     Type,';


    for lvl in 1..upper_level
     loop
--build the column names and alias names
         t_sql:='TYPE_'||lvl||;
         v_sql:='SELECT '||t_sql||' FROM MAIN_TABLE WHERE ID=1';
         EXECUTE IMMEDIATE v_sql into v_sql_val;
         t_sql:=t_sql||' '||v_sql_val||',';
     end loop;

--finish building the statement     
     t_sql:=rtrim(t_sql,',');
     l_sql:=l_sql||t_sql;
     l_sql:=l_sql||' FROM SCHEMA.TABLE
    WHERE ID = 1;';

dbms_output.put_line(l_sql);

end;

My attempted result:

SELECT ID,
     Title,
     Desc,
     Type,TYPE_3 LVL_3 FROM SCHEMA.TABLE
    WHERE ID = 1;

My expected result:

SELECT ID,
     Title,
     Desc,
     Type,TYPE_1 LVL_1, TYPE_2 LVL_2, TYPE_3 LVL_3 FROM SCHEMA.TABLE
    WHERE ID = 1;

How can I achieve this?

user3224907 · Accepted Answer · 2014-06-03 13:45:06Z

1

I have figured out how to achieve expected result:

declare
    upper_level number;
    t_sql varchar2(1000);
    v_sql varchar2(1000);
    v_sql_val varchar2(1000);
    t_sql_val varchar2(500);
    l_sql varchar2(1000);
begin

--upper_level will always be given
    select 3 into upper_level from dual;

--build the fixed string
    l_sql:='SELECT ID,
     Title,
     Desc,
     Type,';


    for lvl in 1..upper_level
     loop
--build the column names and alias names
     t_sql:=t_sql||'TYPE_'||lvl||'_CD';
     v_sql:='SELECT TYPE_'||lvl||'_CD FROM MAIN_TABLE WHERE ID=1';
     EXECUTE IMMEDIATE v_sql into v_sql_val;
     t_sql:=t_sql||' '||v_sql_val||',';
     end loop;

--finish building the statement     
     t_sql:=rtrim(t_sql,',');
     l_sql:=l_sql||t_sql;
     l_sql:=l_sql||' FROM SCHEMA.TABLE
    WHERE ID = 1;';

dbms_output.put_line(l_sql);

end;

answered Jun 3, 2014 at 13:45

user3224907

7683 gold badges16 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

redsoxlost Over a year ago

So your select statement would be something like this SELECT ID, Title, Desc, Type,Type_1_CD .... Type_2_CD .. ??

Collectives™ on Stack Overflow

Oracle 12c - SQL string build issue

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related