Re: same reseult set with less column

dt1649651_at_yahoo.com schrieb:
> I am trying to get the same resultset with an original query but one
> last column less.
>
> For example the original query is "select x,y,z from table1 where
> condition1".
>
> How can I get the resultset of "select x,y from table1 where
> condition1" without knowing in advance the original query.
>
> Thanks for your help.
>
> DT
>

In PL/SQL this might be a start. But this script still has a lot of problems, like is FROM always upper case. What happens when sub selects are used .....

DECLARE

     v_sql_handle        PLS_INTEGER;
     v_column_list       DBMS_SQL.desc_tab;
     v_number_of_cols    NUMBER;
     v_sql               VARCHAR2(32000) := 'SELECT * FROM v$session';
     v_new_sql           VARCHAR2(32000);
     v_seperator         VARCHAR2(10);

BEGIN

     v_sql_handle := DBMS_SQL.open_cursor;
     DBMS_SQL.parse (v_sql_handle, v_sql, DBMS_SQL.native);
     DBMS_SQL.describe_columns (v_sql_handle, v_number_of_cols, 

     v_seperator := '';
     v_new_sql := 'SELECT ';

     FOR v_i IN 1 .. v_number_of_cols
     LOOP

         IF (v_column_list(v_i).col_name <> 'SADDR') THEN

             v_new_sql := v_new_sql || v_seperator || 
v_column_list(v_i).col_name;
             v_seperator := ', ';

         END IF;

     END LOOP;


     v_new_sql := regexp_replace(v_sql,'.*FROM', v_new_sql || ' FROM');

     dbms_output.put_line(v_new_sql);

END; Good luck
Björn Received on Mon Sep 29 2008 - 09:24:03 CDT