5 Replies Latest reply: Sep 16, 2013 3:10 AM by BluShadow RSS

    Select statement from schemas in MERGE statement in USING clause.

    ramya_162

      Hi Experts,

       

       

      In the following merge statement in the USINg clause

      I am using a select stament of one schema WEDB.

      But that same select statement should take data from 30 schemeas

      and then check the condition below condition

       

       

      [code]ON(source.DNO = target.DNO

      AND source.BNO=target.BNO);[/code]

       

       

      For this I thought that using UNIONALL for select statement of the schemas as below.

       

       

       

       

      [code]SELECT

      DNO,

      BNO,

      c2,

      c3,

      c4,

      c5,

      c6,

      c7

      .

      .

      .

      .

      c70

      FROM   WEDB.GHR_TABLE b, WEDB.TSR_TABLE c

      WHERE     b.LINE_NO = 1

      AND b.DNO = h.DNO

      AND c.DNO(+) = b.DNO

      AND c.BNO(+) = b.BNO)

      UNIONALL

      SELECT

      DNO,

      BNO,

      c2,

      c3,

      c4,

      c5,

      c6,

      c7

      .

      .

      .

      .

      c70

      FROM   HR.GHR_TABLE b, HR.TSR_TABLE c

      WHERE     b.LINE_NO = 1

      AND b.DNO = h.DNO

      AND c.DNO(+) = b.DNO

      AND c.BNO(+) = b.BNO)[/code]

       

       

      But the query becomes very huge.

      How to make this very simple.

      We have one schema_info table that contains all 30 schemanames.

      By using that table can we simplify the query.

       

       

       

       

      [code]CREATE OR REPLACE PROCEDURE insert_update

      IS

      BEGIN

      MERGE INTO target_data

      USING (SELECT

      DNO,

      BNO,

      c2,

      c3,

      c4,

      c5,

      c6,

      c7

      .

      .

      .

      .

      c70

      FROM   WEDB.GHR_TABLE b, WEDB.TSR_TABLE c

      WHERE     b.LINE_NO = 1

      AND b.DNO = h.DNO

      AND c.DNO(+) = b.DNO

      AND c.BNO(+) = b.BNO)) source

      ON(source.DNO = target.DNO

      AND source.BNO=target.BNO)

      WHEN MATCHED THEN

      UPDATE SET

      target.DNO=source.DNO

      target.BNO=source.BNO

      target.c1=source.c1,

      target.c2=source.c2,

      .

      .

      .

      target.c25=source.c25

      WHEN NOT MATCHED THEN

      INSERT (target.c1,target.c2,.....c25)

      VALUES (source.c1, source.c2, .....source.c25);

      END;

      /[/code]

       

       

      Please help me.

       

       

      Thanks.

        • 1. Re: Select statement from schemas in MERGE statement in USING clause.
          davidp 2

          If the table list is well defined and won't change, you should build the very big query. You can use an SQL or PL/SQL script to help build it, but the right thing to do is to build the query as an actual piece of code.  If you are only using columns c1 to c25 in your INSERT and UPDATE actions, you only need to select those, not c26 to c70.

           

          The other option is to build the big query into a large string in PL/SQL ( PL/SQL strings can be up to 32k long) and EXECUTE IMMEDIATE myquery; That is native dynamic SQL.

          If the string is too long for 32k, you might need to use a CLOB, but I haven't tried execute immediate on a CLOB. I know there is an interface for DBMS_SQL to work on a clob.

          That is the way to do it if the list of schemas may change.

          E.g.

            selectlist varchar2(1000) := 'SELECT DNO,BNO,c1,c2,c3,c4,c5,c6,c7..c25';

            whereclause varchar2(100)  := 'WHERE     b.LINE_NO = 1 AND b.DNO = h.DNO AND c.DNO(+) = b.DNO AND c.BNO(+) = b.BNO'

            myquery varchar2(32000);

          begin

            myquery := 'MERGE INTO target_data USING (';

            for sch in (select schemaname from schema_info order by schemaname) loop

             if ( myquery is not nul) then

               myquery := myquery || ' UNION ALL ';

              end if;

              myquery := myquery || ' UNION ALL ' || selectlist || 'FROM '

               || sch.schemaname || '.GHR_TABLE b, ' || sch.schemaname || '.TSR_TABLE c '

               || whereclause ;

            end loop;

            myquery := myquery || q'!

          ) source

          ON(source.DNO = target.DNO

          AND source.BNO=target.BNO)

          WHEN MATCHED THEN

          UPDATE SET

          target.DNO=source.DNO

          target.BNO=source.BNO

          target.c1=source.c1,

          target.c2=source.c2,

          .

          .

          .

          target.c25=source.c25

          WHEN NOT MATCHED THEN

          INSERT (target.c1,target.c2,.....c25)

          VALUES (source.c1, source.c2, .....source.c25)!';

          execute immediate myquery;

          • 2. Re: Select statement from schemas in MERGE statement in USING clause.
            BluShadow

            It's not clear what you actually want.

             

            So, you seem to want to query 30 schemas with the same query for each, and union the results together... then what's the problem.  yes, 30 unions are going to be a large SQL statement... that's what happens if you split similar data across 30 schemas rather than storing it all in one schema with partitioning or some such method to seperate the data within the one table.

             

            I guess you could create a view on each of the 30 schemas and then just query from those views in your merge statement, which would make the merge statement itself appear neater, though the underlying SQL that is executed would still be the same.

            • 3. Re: Select statement from schemas in MERGE statement in USING clause.
              ramya_162

              Thanks for your suggestion.

               

               

              Yes I want all 70 columns.

              I have tried as you suggested  but I am getting PL/SQL value or numeric error.

              I have tried with LONG and CLOB datatypes.

               

               

              Please help me.

               

               

              Thanks.

              • 4. Re: Select statement from schemas in MERGE statement in USING clause.
                ramya_162

                Hi ,

                 

                 

                My requirement is in the merge statement of USING clause I am using

                a select statement that is using tables from one schema.

                We have 30 schemas in the DB I want to use single merge statement

                of same select statement that should use the same tables from all 30 schemas.

                We have one schema_info table that is having all 30 schema names.

                 

                 

                Please help me.How to implement this.

                 

                 

                Thanks.

                • 5. Re: Select statement from schemas in MERGE statement in USING clause.
                  BluShadow

                  ramya_162 wrote:

                   

                  Thanks for your suggestion.

                   

                   

                  Yes I want all 70 columns.

                  I have tried as you suggested  but I am getting PL/SQL value or numeric error.

                   

                  Where is this error?  I cannot see any running code or where the error is occurring.  How can we help you if we cannot see it?

                   

                  I have tried with LONG and CLOB datatypes.

                   

                  Why? We can't see your code to know why you think you need such datatypes.   LONG datatype has been deprecated for over a decade, so don't use it.

                   

                  My requirement is in the merge statement of USING clause I am using

                  a select statement that is using tables from one schema.

                  We have 30 schemas in the DB I want to use single merge statement

                  of same select statement that should use the same tables from all 30 schemas.

                  We have one schema_info table that is having all 30 schema names.

                   

                  Yes, you've already said that's your requirement.

                  What purpose to you hope to get from storing the schema names in a table?  You want to write code that writes code for you?  Sounds like rubbish design.

                   

                  Just write your SQL statement to do the merge... it may be big, but if it works it works.