CODEWITHSUNDEEP
sqloracle-database
Ganesan VC
Ganesan VC

Reputation: 53

How to remove union all in last line of output

I am trying to create a query which is fetching all the project/schema with same table name with UNION ALL. But i am getting the output with UNION ALL in all PROJECT / Schema. i am expecting result which should remove the union all from the last project / schema.

Current Output :

select 'ProjectA' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectA.event UNION ALL
select 'ProjectB' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectB.event UNION ALL
select 'ProjectC' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectC.event UNION ALL
select 'ProjectD' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectD.event UNION ALL

Expected Output :

select 'ProjectA' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectA.event UNION ALL
select 'ProjectB' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectB.event UNION ALL
select 'ProjectC' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectC.event UNION ALL
select 'ProjectD' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectD.event ;

Query :

SET SERVEROUTPUT ON
 Declare
v_sql varchar2(500);
v_sql2 varchar2(500);
v_prj_id varchar2(4000):='ProjectA,ProjectB,ProjectC,ProjectD';

BEGIN


 Dbms_Output.Put_Line('v_prj_id='||v_prj_id);
 --
FOR i IN (SELECT trim(regexp_substr(v_prj_id, '[^,]+', 1, LEVEL)) l
    FROM dual 
    CONNECT BY LEVEL <= regexp_count(v_prj_id, ',') + 1 
   ) LOOP

   v_sql :=  'select '''|| i.l ||''' AS "PRJ_ID", EVENT, email,modified_by,modified from ' || i.l || 
    '.Table UNION ALL';

   Dbms_Output.Put_Line (v_sql);

    --
   END LOOP;
   EXECUTE IMMEDIATE v_sql ;

    v_sql2 :=  RTRIM (v_sql, 'UNION ALL');
   EXECUTE IMMEDIATE v_sql2 ;
   Dbms_Output.Put_Line (v_sql2);

   END;
   /

Upvotes: 1

Views: 772

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272446

RTRIM is not the correct function. It removes all characters from right present in the whitelist so it will trim more than just UNION ALL.

You could use SUBSTR and INSTR:

SELECT str
     , RTRIM(str, ' UNION ALL') AS bad
     , SUBSTR(str, 1, INSTR(str, ' UNION ALL', -1) - 1) AS good
FROM (
    SELECT 'SELECT * FROM FOO.LLANOINU UNION ALL' AS str FROM dual
) x

Upvotes: 1

VBoka
VBoka

Reputation: 9083

The code you had is already doing the trim of your string in v_sql But if you want to execute it all at the same time then you will have to do this:

begin
Declare
   v_sql varchar2(500);
   v_sql2 varchar2(500);
   v_prj_id varchar2(4000):='ProjectA,ProjectB,ProjectC,ProjectD';
BEGIN

   FOR i IN (SELECT trim(regexp_substr(v_prj_id, '[^,]+', 1, LEVEL)) l
             FROM dual 
             CONNECT BY LEVEL <= regexp_count(v_prj_id, ',') + 1 
   ) LOOP


       v_sql :=  v_sql || 'select '''|| i.l ||''' AS "PRJ_ID", EVENT, email,modified_by,modified from ' || i.l || '.Table UNION ALL ' || chr(10) ;

   END LOOP;

    v_sql2 :=  RTRIM(v_sql, 'UNION ALL ' || chr(10) ) || ';';

    Dbms_Output.Put_Line (v_sql2);

   END;
end;
/

The v_sql2 will now have the entire desired code. Here is the DEMO

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You'd usually use string manipulation functions, e.g.

v_sql := substr(v_sql, 1, length(v_sql) - length('UNION ALL'));

Upvotes: 0

Related Questions