This discussion is archived
5 Replies Latest reply: Sep 16, 2013 1:10 AM by BluShadow RSS

Select statement from schemas in MERGE statement in USING clause.

ramya_162 Newbie
Currently Being Moderated

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 Pro
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points