1 2 3 Previous Next 40 Replies Latest reply: Jul 30, 2013 4:59 PM by rp0428 Go to original post RSS
      • 30. Re: Oracle Program/Query
        969952

        Please have a look into the complete statements.

        Table:

        {code}

        CREATE TABLE GROUPS

        AS

           SELECT   18 GROUP_ID, 'MS' group_name FROM DUAL

           UNION ALL

           SELECT   14 GROUP_ID, 'CS' group_name FROM DUAL

           UNION ALL

           SELECT   15 GROUP_ID, 'PSS' group_name FROM DUAL

           UNION ALL

           SELECT   13 GROUP_ID, 'SS' group_name FROM DUAL;

        {code}

         

        Users1 Table :

        USER_ID  
        DIV_ID                                        

        BRANCH_ID                                

         

        office_code:

        ORG_ID                               
        ORG_CODE                                      

        ORG_NAME 

        columns.

         

        SELECT   USERS_SEQUENCE.NEXTVAL,

                 e.emp_id,

                 o.org_id,

                 d.div_id,

                 d.div_code,

                 d.div_dscr

          FROM   users1 e, office_code o, div d

        WHERE   e.div_id = d.div_id AND e.org_id = o.org_id

                 AND d.div_dscr IN

                          ('Management Services',

                           'Cloud Services',

                           'Production Support',

                           'Services support');

        then I have written the below  statement

        {code}

        MERGE INTO GROUPS old

        USING (WITH div AS

               ( SELECT   USERS_SEQUENCE.NEXTVAL,

                                                   e.emp_id,

                                                   o.org_id,

                                                   d.div_id,

                                                   d.div_code,

                                                   d.div_dscr

                                   FROM   users1 e, office_codes o, oarm_div d)

               SELECT   DISTINCT d.div_id,

                                        g.GROUP_ID,

                                        g.group_name,

                                        g.ROWID growid

                        FROM   oarm_div d, OARM_GROUP_DIVS g

                       WHERE   d.div_code = g.group_name ) new ON (old.ROWID = growid)

        WHEN MATCHED THEN UPDATE SET old.GROUP_ID = new.div_id;

        while trying to execute Merge statement getting the error message as follows:

         

        {code}

        WHEN MATCHED

        THEN

           UPDATE SET old.GROUP_ID = new.div_id

        Error at line 1

        ORA-32039: recursive WITH clause must have column alias list

         

        Script Terminated on line 35.

        {code}

        • 31. Re: Oracle Program/Query
          969952

          Perfect .. you are right.. I have changed the query from WITH DIV as to WITH  T as ( query)

          now am getting

           

            WHEN MATCHED THEN UPDATE 

                    SET old.group_id = new.div_id

          Error at line 1

          ORA-02287: sequence number not allowed here.. mesage

          • 32. Re: Oracle Program/Query
            Greg Spall

            Ok, I give up ..

             

             

             

            seriously . how many times do we need to request information from you?

            Why do you insist on ignoring requests to help you? Why do you not read the links provided and provide the information requested?

            You obviously don't want help that badly.

             

            I tried, I really really tried.

            But you simply won't provide us with the information we need to help you.

            Sorry, I can't help you. Perhaps others have more patience. I'm done with this thread.

            (and it's a shame, because it seems like such as a simple issue - this thread shouldn't have went past 4 posts.)

             

            Jul 26, @10:51 am (Greg)

            Write a single SQL statement that returns what you want to see in the target table

             

            Jul 26, @11:03 am (Frank)

            Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

            ...

            See the forum FAQ: https://forums.oracle.com/message/9362002

             

            Jul 26, @11:44 am (Greg)

            Create a small sample test case.

            Fake data, fake tables (similar enough to your own so you can relate).

             

            Jul 26, @12:07 pm (Frank)

            No kidding: without knowing what you want to do, it's very hard to say how to do it.  Post some sample data (CREATE TABLE and INSERT statements for all tables involved,

             

            Jul 26, @1:21 pm (Greg)

            We're back to:

            "Please set up a simple example test case" so we can help.

             

            Jul 26, @3:46 pm (Greg):

            When you give us something to work with, we can generate results

             

            Jul 26, @4:31 pm (Greg):

            you never provided a complete working sample

             

            Jul 29, @10:16 am (Greg):

            Post.

            A

            Complete

            Example.

             

            Jul 29, @10:36 am (Greg):

            Did you test your sample?

            Please Post a complete example.

             

            Jul 29, @10:38 am (Hoek)

            In addition to Greg:

            The Tom Kyte Blog: How to ask questions

            • 33. Re: Oracle Program/Query
              969952

              Please don't get irritate.. without getting any output which data you want me to post here.. I have posted the existing tables and mergwe statement and posted the expected result .. but here here am posting expected result .. atble are alredy posted here..

              Please have a look.

               

              users1 :

              EMP_ID            
              LVL_ID                

              ORG_ID

               

              GROUPS :

              GROUP_ID            

              GROUP_NAME

               

              data existed in Groups table as follows : 

              Group_ID          Group_name

              18                      MS

              14                      CS

              13                       PS

              15                      SS

               

              office_codes :

              ORG_ID                
              ORG_CODE          

              ORG_NAME

               

              Div

               

              DIV_ID                      

              DIV_CODE               

              DIV_DSCR

              data existed in DIV table as follows

              Div_Id Div_code      Div_DSCR

               

              3             SS            Source Systems

              2             PS            Production Support

              1            CS            Cloud Services

              4             MS            Management Services

               

              Now I would like to map group_id with DIV_ID update group_id with DIV_ID . whatever DIV_ID existed that should be mapped as Group_id in any environment.

              for this requiremnt I have created a table Groups

               

              1. CREATE TABLE GROUPS

              AS

                 SELECT   18 GROUP_ID, 'MS' group_name FROM DUAL

                 UNION ALL

                 SELECT   14 GROUP_ID, 'CS' group_name FROM DUAL

                 UNION ALL

                 SELECT   15 GROUP_ID, 'SS' group_name FROM DUAL

                 UNION ALL

                 SELECT   13 GROUP_ID, 'PS' group_name FROM DUAL;

              2.

              WITH res AS ( 

                                    select USERS_SEQUENCE.NEXTVAL,

                       e.emp_id,

                       o.org_id,

                       d.div_id,

                       d.div_code,

                       d.div_dscr from users1 e, office_codes o, div d

              WHERE   e.div_id = d.div_id AND e.org_id = o.org_id

                                 )       

                           select * 

                             from div d, 

                                  groups g 

                            where d.div_code = g.group_name; 

              3. Merge :

               

              Merge statement have written as mentioned above

               

              but getting the error which have mentioned ebove.

               

              sample data for the above requirement is :

               

              please let me knwo if you need more information.

              • 34. Re: Oracle Program/Query
                SomeoneElse

                If you're getting an error about the sequence, just remove it.

                 

                I can't see where you're using it anyway.


                • 35. Re: Oracle Program/Query
                  969952

                  Hi,

                   

                  I have written the query as follows.

                  {code}

                  SELECT   DISTINCT l.fname,

                                    l.lastname,

                                    l.employeeid,

                                    ofc.org_id,

                                    od.div_id,

                                    e.branch_id,

                                    e.lvl_id,

                                    g.GROUP_ID

                    FROM   users1 e,

                           office_codes ofc,

                           div od,

                           notes_process l,

                           groups1 g

                  WHERE       e.div_id = od.div_id

                           AND e.org_id = ofc.org_id

                           AND od.div_code = g.group_name

                           and e.emp_id=L.EMPLOYEEID

                  {code}

                  here in notes_process table contains

                  fname,lastname,employeeID columns.

                   

                  The above query returned 50 records. So I would like to write a script which will insert these 5o records into three tables

                  1. Users1  2. Groups  3. groups1 tables.

                  what are the columns existed in the tables those values should filled with the query retured values.

                   

                  Thanks

                  • 36. Re: Oracle Program/Query
                    SomeoneElse

                    > So I would like to write a script which will insert these 5o records into three tables

                     

                    INSERT INTO your_table (columns....)

                    SELECT columns....

                    FROM   (your query);

                    • 37. Re: Oracle Program/Query
                      969952

                      no...  using pl/sql  at a time i would like to insert all values into above three tables/...

                      • 38. Re: Oracle Program/Query
                        SomeoneElse

                        You can't merge into three tables with a single statement (not with sql or pl/sql).

                         

                        There's a statement called a multi-table insert.  Maybe that's what you want:

                         

                        http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#i2095116

                        • 39. Re: Oracle Program/Query
                          969952

                          Hi All,

                           

                          Thank you very much for your valuable suggestions and support.. The issue has been resolved.

                          • 40. Re: Oracle Program/Query
                            rp0428

                            Then mark the thread ANSWERED.


                            1 2 3 Previous Next