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

        It's a game of "Guess Who!"

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

        • 16. Re: Oracle Program/Query
          969952

          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

            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

              > 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

                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

                  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

                    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

                      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

                        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

                          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

                            Post.

                            A

                            Complete

                            Example.

                            • 26. Re: Oracle Program/Query
                              969952

                              {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

                                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
                                  • 29. Re: Oracle Program/Query
                                    SomeoneElse

                                    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