This discussion is archived
1 2 3 Previous Next 40 Replies Latest reply: Jul 30, 2013 2:59 PM by rp0428 Go to original post RSS
  • 30. Re: Oracle Program/Query
    969952 Newbie
    Currently Being Moderated

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

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

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

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

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

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

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

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

  • 38. Re: Oracle Program/Query
    SomeoneElse Guru
    Currently Being Moderated

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

    Hi All,

     

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

  • 40. Re: Oracle Program/Query
    rp0428 Guru
    Currently Being Moderated

    Then mark the thread ANSWERED.


1 2 3 Previous Next

Legend

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