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
  • 15. Re: Oracle Program/Query
    Greg.Spall Expert
    Currently Being Moderated

    It's a game of "Guess Who!"

    (I hated that game - never was good at it)

  • 16. Re: Oracle Program/Query
    969952 Newbie
    Currently Being Moderated

    No not like that.. please have a look here..

     

    select USERS_SEQUENCE.NEXTVAL, e.emp_id, o.org_id, d.div_id,d.div_code,d.div_dscr

        from users e, office_codes 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');

    O/P:

    {code}

    NEXTVAL    EMP_ID    ORG_ID      DIV_ID    DIV_CODE  DIV_DSCR

     

    27704    00005688              1             1              CS          Cloud Services

    27705    00007164             1               1              CS          Cloud Services

    27706    00015970              1              1              CS          Cloud Services

    27707    00007971              1              1              CS          Cloud Services

    27713    00031832              1              2              MS          Management Services

    27714    00026775              1              2              MS          Management Services

    27715    00016297              1              2              MS          Management Services

    27716    00029158              1              2              MS          Management Services

    27726    99988547              1              3              PS          Production Support

    27727    00033034              1              3              PS          Production Support

    27728    00006303              1              3              PS          Production Support

    {code}

     

    2.select * from groups;

    O/p:

    GROUP_ID    GROUP_NAME

     

    18                   CS

    14                   MS

    15                   PS

     

    Group_id is Primary Key columns.

     

    here after comparing group_id, div_code and group_name are equal so I would like to update group_Id with DIV_ID. so I have written the following code.

    {code}

    begin

     

         for i in (select d.div_id from div d, groups g where d.DIV_CODE=g.group_name)

     

        loop

     

        update groups set group_id=i.div_id;

     

        end loop;

     

        end;

    {code}

     

    but it is not updating the records .

     

    Help me out to resolve this.

  • 17. Re: Oracle Program/Query
    John Spencer Oracle ACE
    Currently Being Moderated

    Greg.Spall wrote:

     

    It's a game of "Guess Who!"

    (I hated that game - never was good at it)

     

    But they are a great band

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

    > but it is not updating the records .

     

    Earlier, you posted this error:

     

    > It is not updating... am getting ORA-00001: unique constraint (Schema.GROUPS_ID_PK) violated..

     

    That's because you apparently have a primary key on the column you are trying to update.  But you are setting it to a value that already exists.

  • 19. Re: Oracle Program/Query
    Greg.Spall Expert
    Currently Being Moderated

    969952 wrote:

     

    No not like that.. please have a look here..

     

    select USERS_SEQUENCE.NEXTVAL, e.emp_id, o.org_id, d.div_id,d.div_code,d.div_dscr

        from users e, office_codes 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');

    O/P:

    {code}

    NEXTVAL    EMP_ID    ORG_ID      DIV_ID    DIV_CODE  DIV_DSCR

     

    27704    00005688              1             1              CS          Cloud Services

    27705    00007164             1               1              CS          Cloud Services

    27706    00015970              1              1              CS          Cloud Services

    27707    00007971              1              1              CS          Cloud Services

    27713    00031832              1              2              MS          Management Services

    27714    00026775              1              2              MS          Management Services

    27715    00016297              1              2              MS          Management Services

    27716    00029158              1              2              MS          Management Services

    27726    99988547              1              3              PS          Production Support

    27727    00033034              1              3              PS          Production Support

    27728    00006303              1              3              PS          Production Support

    {code}

     

    2.select * from groups;

    O/p:

    GROUP_ID    GROUP_NAME

     

    18                   CS

    14                   MS

    15                   PS

     

    Group_id is Primary Key columns.

     

     

    Now we're getting somewhere!!

     

    You're missing the definition of groups table.

    so I'll "guess" (again).

     

    CREATE TABLE groups
       AS 
       select 18 group_id, 'CS' group_name from dual union all
             select 14         , 'MS'            from dual union all
             select 15         , 'PS'            from dual ;

     

    Then we take your query, and result set:

     

    and continue a bit more to massage the data into what you want (ie the FINAL result):

     

    WITH div AS (
                      select 27704 nextval, '00005688' emp_id, 1 org_id, 1 div_id, 'CS' div_code, 'Cloud Services'       div_dscr from dual union all
                      select 27705        , '00007164'       , 1       , 1       , 'CS'         , 'Cloud Services'                from dual union all
                      select 27706        , '00015970'       , 1       , 1       , 'CS'         , 'Cloud Services'                from dual union all
                      select 27707        , '00007971'       , 1       , 1       , 'CS'         , 'Cloud Services'                from dual union all
                      select 27713        , '00031832'       , 1       , 2       , 'MS'         , 'Management Services'           from dual union all
                      select 27714        , '00026775'       , 1       , 2       , 'MS'         , 'Management Services'           from dual union all
                      select 27715        , '00016297'       , 1       , 2       , 'MS'         , 'Management Services'           from dual union all
                      select 27716        , '00029158'       , 1       , 2       , 'MS'         , 'Management Services'           from dual union all
                      select 27726        , '99988547'       , 1       , 3       , 'PS'         , 'Production Support'            from dual union all
                      select 27727        , '00033034'       , 1       , 3       , 'PS'         , 'Production Support'            from dual union all
                      select 27728        , '00006303'       , 1       , 3       , 'PS'         , 'Production Support'            from dual
                   )      
             select *
               from div d,
                    groups g
              where d.div_code = g.group_name;

     

       NEXTVAL EMP_ID       ORG_ID     DIV_ID DI DIV_DSCR              GROUP_ID GR

    ---------- -------- ---------- ---------- -- ------------------- ---------- --

         27704 00005688          1          1 CS Cloud Services               1 CS

         27705 00007164          1          1 CS Cloud Services               1 CS

         27706 00015970          1          1 CS Cloud Services               1 CS

         27707 00007971          1          1 CS Cloud Services               1 CS

         27713 00031832          1          2 MS Management Services          2 MS

         27714 00026775          1          2 MS Management Services          2 MS

         27715 00016297          1          2 MS Management Services          2 MS

         27716 00029158          1          2 MS Management Services          2 MS

         27726 99988547          1          3 PS Production Support           3 PS

         27727 00033034          1          3 PS Production Support           3 PS

         27728 00006303          1          3 PS Production Support           3 PS

     

    11 rows selected.

     

     

    Now we just need to push that into the table - I prefer MERGE at this point .. I find the syntax easier to use.

     

    select * from groups;

     

      GROUP_ID GR

    ---------- --

            18 CS

            14 MS

            15 PS

     

     

    MERGE INTO groups old
       USING (
             WITH div AS (
                      select 27704 nextval, '00005688' emp_id, 1 org_id, 1 div_id, 'CS' div_code, 'Cloud Services'       div_dscr from dual union all
                      select 27705        , '00007164'       , 1       , 1       , 'CS'         , 'Cloud Services'                from dual union all
                      select 27706        , '00015970'       , 1       , 1       , 'CS'         , 'Cloud Services'                from dual union all
                      select 27707        , '00007971'       , 1       , 1       , 'CS'         , 'Cloud Services'                from dual union all
                      select 27713        , '00031832'       , 1       , 2       , 'MS'         , 'Management Services'           from dual union all
                      select 27714        , '00026775'       , 1       , 2       , 'MS'         , 'Management Services'           from dual union all
                      select 27715        , '00016297'       , 1       , 2       , 'MS'         , 'Management Services'           from dual union all
                      select 27716        , '00029158'       , 1       , 2       , 'MS'         , 'Management Services'           from dual union all
                      select 27726        , '99988547'       , 1       , 3       , 'PS'         , 'Production Support'            from dual union all
                      select 27727        , '00033034'       , 1       , 3       , 'PS'         , 'Production Support'            from dual union all
                      select 27728        , '00006303'       , 1       , 3       , 'PS'         , 'Production Support'            from dual
                   )      
             select distinct d.div_id, g.group_id, g.group_name, g.rowid  growid
               from div d,
                    groups g
              where d.div_code = g.group_name
             ) new
          ON ( old.rowid = growid )
       WHEN MATCHED THEN UPDATE
          SET old.group_id = new.div_id;

     

    3 rows merged.

     

      GROUP_ID GR

    ---------- --

             1 CS

             2 MS

             3 PS

     

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

  • 20. Re: Oracle Program/Query
    969952 Newbie
    Currently Being Moderated

    Div table is having around 20000 records for all those records it is not good to write as

    {code}

    1. WITH div AS
    2. select 27704 nextval, '00005688' emp_id, 1 org_id, 1 div_id, 'CS' div_code, 'Cloud Services'       div_dscr from dual union all 
    3. select 27705        , '00007164'       , 1       , 1       , 'CS'         , 'Cloud Services'                from dual union all 
    4. select 27706        , '00015970'       , 1       , 1       , 'CS'         , 'Cloud Services'                from dual union all 
    5. select 27707        , '00007971'       , 1       , 1       , 'CS'         , 'Cloud Services'                from dual union all

    ....

    ...

    {code}


    correct me if am wrong... so is there any other way to define DIV?


    Thanks 

  • 21. Re: Oracle Program/Query
    Greg.Spall Expert
    Currently Being Moderated

    Uh, yeah . use the query you posted ... I couldn't use it, because I don't have the underlying tables - you never provided a complete working sample .. so I had to manufacture workarounds.

     

    select USERS_SEQUENCE.NEXTVAL, e.emp_id, o.org_id, d.div_id,d.div_code,d.div_dscr

        from users e, office_codes 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');

  • 22. Re: Oracle Program/Query
    34MCA2K2 Journeyer
    Currently Being Moderated

    Yeah Just write Div instead of With clause, it is just to build an inline table or virtual table as you wish to call it. Remove the with clause from Merge & for God's sake don't mark my answer as correct if it works, everyone above has spent so much effort in understanding and replying to your problem

     

    Regards,

  • 23. Re: Oracle Program/Query
    Greg.Spall Expert
    Currently Being Moderated

    34MCA2K2 wrote:

     

    Yeah Just write Div instead of With clause, it is just to build an inline table or virtual table as you wish to call it. Remove the with clause from Merge & for God's sake don't mark my answer as correct if it works, everyone above has spent so much effort in understanding and replying to your problem

     

    Regards,

    lol

  • 24. Re: Oracle Program/Query
    969952 Newbie
    Currently Being Moderated

    I have written the query as follows.

     

    created a table GROUPS as you suggested.

    {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 users e, office_codes o, div d

        where e.div_id = d.div_id

        and e.org_id = o.org_id

    ),

                  (SELECT   DISTINCT d.div_id,

                                    g.GROUP_ID,

                                    g.group_name,

                                    g.ROWID growid

                    FROM   div d, GROUPS g

                   WHERE   d.div_code = g.group_name)

            ON   (old.ROWID = growid)

    WHEN MATCHED

    THEN

       UPDATE SET old.GROUP_ID = new.div_id; 

    {code}

     

    am getting Invalid table name error message...

     

    Please have a look and correct me.

     

    Thanks.

  • 25. Re: Oracle Program/Query
    Greg.Spall Expert
    Currently Being Moderated

    Post.

    A

    Complete

    Example.

  • 26. Re: Oracle Program/Query
    969952 Newbie
    Currently Being Moderated

    {code}

     

    table :

    CREATE TABLE GROUP

    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, 'PS' group_name FROM DUAL

       UNION ALL

       SELECT   13 GROUP_ID, 'SS' group_name FROM DUAL;

     

    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 users e, office_codes o, div d

        where e.div_id = d.div_id

        and e.org_id = o.org_id

    )

                  SELECT   DISTINCT d.div_id,

                                    g.GROUP_ID,

                                    g.group_name,

                                    g.ROWID growid

                    FROM   div d, GROUPS g

                   WHERE   d.div_code = g.group_name

    ) new

            ON   (old.ROWID = growid)

    WHEN MATCHED

    THEN

       UPDATE SET old.GROUP_ID = new.div_id;

    {code}

     

    Error  :

     

    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 34.

    :

     

    Here in the query I have added the SELECT statement because table is having many employeeid's so have written the query .. instead of the query you mentioned in the above example..please correct me if it's wrong.

     

    Thanks.

  • 27. Re: Oracle Program/Query
    Greg.Spall Expert
    Currently Being Moderated

    Did you test your sample?

     

    CREATE TABLE GROUP

                 *

    ERROR at line 1:

    ORA-00903: invalid table name

     

    Sample is not complete

     

        from users e, office_codes o, div d

                                      *

    ERROR at line 2:

    ORA-00942: table or view does not exist

     

    I do not have users table, office_codes, etc.

     

    Please Post a complete example.

    I have another job here, and very busy, I am more than happy to help you, however, I cannot be spending hours doing all this footwork. This is what you are being paid for. Do some basic footwork, and you'll find people here will be more than happy to help you.

  • 28. Re: Oracle Program/Query
    Hoek Guru
    Currently Being Moderated
  • 29. Re: Oracle Program/Query
    SomeoneElse Guru
    Currently Being Moderated

    You're creating a with clause called DIV and then selecting from itself.

     

    That's a recursive WITH clause.  Did you mean to do that?

     

    WITH div AS (

    select USERS_SEQUENCE.NEXTVAL, e.emp_id, o.org_id, d.div_id,d.div_code,d.div_dscr

        from users e, office_codes o, div d

Legend

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