11 Replies Latest reply on Jan 5, 2018 7:58 AM by BluShadow

    Procedure

    3565381

      Hai I have created a table and inserted the values in it. I need to update the gen_id from 00 to 03 for each and every loc_id. I am attaching the procedure along with this please tell the error in it.

      Thanks in Advance.

       

      CREATE TABLE sub_loc_tb

      (

          loc_id    NUMBER

        , city    VARCHAR2(30)

        , town    VARCHAR2(30)

        , gen_id    NUMBER

      );

       

      INSERT INTO sub_loc_tb VALUES (101, 'Chennai', 'T-Nagar', NULL);

      INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Velachery', NULL);

      INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Adayar', NULL);

      INSERT INTO sub_loc_tb VALUES (102, 'Kancheepuram', 'Tambaram', NULL);

      INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Sri-Perumbudur', NULL);

      INSERT INTO sub_loc_tb VALUES (103, 'Thoothukudi', 'Thiruchendur', NULL);

      INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Kovilpatti', NULL);

      INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Sivakasi', NULL);

       

      DECLARE

          v_seq     VARCHAR2(300);

          v_cnt     NUMBER := 0;

         

      BEGIN

          FOR i IN (SELECT city FROM sub_loc_tb)

          LOOP

          UPDATE sub_loc_tb

             SET gen_id = 0 || v_cnt

           WHERE city IS NOT NULL;

          FOR j IN (SELECT loc_id, city FROM sub_loc_tb)

          LOOP

          UPDATE sub_loc_tb

             SET gen_id = v_cnt + 1

           WHERE loc_id = j.loc_id

             AND city IS NULL;

          END LOOP;

        END LOOP;

      END;

      /

        • 1. Re: Procedure
          siri.jagadeesh

          Can you explain the little bit more..

          • 2. Re: Procedure
            BluShadow

            You have 5 rows in your data that have a NULL loc_id.  Assuming that's the key to your table, you have a big problem.

            The second update in your code won't work because there is no records with a null city and a loc_id that can be matched.

             

            If you think that the NULL loc_id rows relate to the row that was inserted before with a loc_id, then you are mistaken.  Rows in a table do not have an order, so the null records have no relationship to any other records, unless you actually put a relationship directly in the data itself.

            • 3. Re: Procedure
              Sevdimali Isayev

              Hi,

               

              gen_idNUMBER

              is number, you can't store 00 or any other like this.

              try to change datatype to varchar2.

              • 4. Re: Procedure
                Bommi

                Hi,

                     Could you please define gen_id as VARCHAR2 type and try.

                     If you define it as NUMBER and if we give 03, then it will take as 3 only as it is number type

                 

                 

                Regards,

                Bommi

                • 5. Re: Procedure
                  BluShadow

                  If, instead, you actually have a loc_id that relates the records, then you can do something like the following...

                   

                  SQL> CREATE TABLE sub_loc_tb
                    2  (
                    3      loc_id    NUMBER
                    4    , city    VARCHAR2(30)
                    5    , town    VARCHAR2(30)
                    6    , gen_id    NUMBER
                    7  )
                    8  /

                   

                  Table created.

                   

                  SQL> INSERT INTO sub_loc_tb VALUES (101, 'Chennai', 'T-Nagar', NULL);

                   

                  1 row created.

                   

                  SQL> INSERT INTO sub_loc_tb VALUES (101, NULL, 'Velachery', NULL);

                   

                  1 row created.

                   

                  SQL> INSERT INTO sub_loc_tb VALUES (101, NULL, 'Adayar', NULL);

                   

                  1 row created.

                   

                  SQL> INSERT INTO sub_loc_tb VALUES (102, 'Kancheepuram', 'Tambaram', NULL);

                   

                  1 row created.

                   

                  SQL> INSERT INTO sub_loc_tb VALUES (102, NULL, 'Sri-Perumbudur', NULL);

                   

                  1 row created.

                   

                  SQL> INSERT INTO sub_loc_tb VALUES (103, 'Thoothukudi', 'Thiruchendur', NULL);

                   

                  1 row created.

                   

                  SQL> INSERT INTO sub_loc_tb VALUES (103, NULL, 'Kovilpatti', NULL);

                   

                  1 row created.

                   

                  SQL> INSERT INTO sub_loc_tb VALUES (103, NULL, 'Sivakasi', NULL);

                   

                  1 row created.

                   

                  SQL> commit;

                   

                  Commit complete.

                   

                  SQL> merge into sub_loc_tb s
                    2  using (select loc_id, city, town
                    3              ,row_number() over (partition by loc_id order by city nulls last, town) as rn
                    4        from  sub_loc_tb
                    5        ) t
                    6  on (    t.loc_id = s.loc_id
                    7      and t.town = s.town
                    8    )
                    9  when matched then
                  10    update set gen_id = rn
                  11  /

                   

                  8 rows merged.

                   

                  SQL> select *
                    2  from sub_loc_tb;

                   

                      LOC_ID CITY                           TOWN                               GEN_ID
                  ---------- ------------------------------ ------------------------------ ----------
                         101 Chennai                        T-Nagar                                 1
                         101                                Velachery                               3
                         101                                Adayar                                  2
                         102 Kancheepuram                   Tambaram                                1
                         102                                Sri-Perumbudur                          2
                         103 Thoothukudi                    Thiruchendur                            1
                         103                                Kovilpatti                              2
                         103                                Sivakasi                                3

                   

                  8 rows selected.

                   

                  and if you want gen_id to show the number left padded with spaces to e.g. 2 digits, then you would use to_char when you query the data (you would NOT make the column VARCHAR2 and store numbers as strings as some may suggest!)

                   

                  SQL> select loc_id, city, town, to_char(gen_id,'fm00') as gen_id
                    2  from sub_loc_tb;

                   

                      LOC_ID CITY                           TOWN                           GEN
                  ---------- ------------------------------ ------------------------------ ---
                         101 Chennai                        T-Nagar                        01
                         101                                Velachery                      03
                         101                                Adayar                         02
                         102 Kancheepuram                   Tambaram                       01
                         102                                Sri-Perumbudur                 02
                         103 Thoothukudi                    Thiruchendur                   01
                         103                                Kovilpatti                     02
                         103                                Sivakasi                       03

                   

                  8 rows selected.

                   

                  Formatting numbers (or dates) to a particular display format is the last thing that should be done, and using varchar2 is NOT how you should store data.

                  1 person found this helpful
                  • 6. Re: Procedure
                    BluShadow

                    989764 wrote:

                     

                    Hi,

                    Could you please define gen_id as VARCHAR2 type and try.

                    If you define it as NUMBER and if we give 03, then it will take as 3 only as it is number type

                     

                     

                    Regards,

                    Bommi

                     

                     

                    I suggest you read the community document: PL/SQL 101 : DataTypes - NUMBER

                    • 7. Re: Procedure
                      Cookiemonster76

                      Here's what this code actually does:

                      Loop over the table

                       

                      Update all the rows in the table to set gen_id to 00 (it's always 00 because you don't increment v_cnt at any point)

                      Loop over the table again

                      For each row update any rows with the same loc_id where city is null (never true as BluShadow mentioned) and set gen_id to 1 (it's always 1 for this update)

                       

                      end loop

                       

                      end loop

                       

                       

                      So all that actually does is set gen_id to 0 for all rows repeatedly.

                      But if you want help coming up with working code you're first going to have to address the point BluShadow raised about the rows not being related.

                      • 8. Re: Procedure
                        3565381

                        I need the output as

                         

                        LOC_ID CITY `      TOWN        GEN_ID

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

                           101 Chennai    T-Nagar   00
                           Velachery   01
                           Adayar   02
                           102 Kancheepuram   Tambaram   00
                           Sri-Perumbudur  01
                           103 ThoothukudiThiruchendur   00
                                          Kovilpatti   01
                           Sivakasi   02
                        • 9. Re: Procedure
                          Etbin

                          If you're lucky assigning correct loc_id

                           

                          with

                          sub_loc_tb as

                          (select 101 loc_id,'Chennai' city,'T-Nagar' town,NULL gen_id from dual union all

                          select NULL,NULL,'Velachery',NULL from dual union all

                          select NULL,NULL,'Adayar',NULL from dual union all

                          select 102,'Kancheepuram','Tambaram',NULL from dual union all

                          select NULL,NULL,'Sri-Perumbudur',NULL from dual union all

                          select 103,'Thoothukudi','Thiruchendur', NULL from dual union all

                          select NULL,NULL,'Kovilpatti',NULL from dual union all

                          select NULL,NULL,'Sivakasi',NULL from dual

                          )

                          select case when lag(loc_id,1,1) over (order by loc_id) != loc_id

                                      then loc_id

                                 end loc_id,

                                 city,

                                 town,

                                 to_char(row_number() over (partition by loc_id order by null) - 1,'00') gen_id

                            from (select last_value(loc_id ignore nulls) over (order by rownum) loc_id,city,town

                                    from sub_loc_tb

                                 )

                           

                          LOC_IDCITYTOWNGEN_ID
                          101ChennaiT-Nagar00
                          --Velachery01
                          --Adayar02
                          102KancheepuramTambaram00
                          --Sri-Perumbudur01
                          103ThoothukudiThiruchendur00
                          --Kovilpatti01
                          --Sivakasi02

                           

                          Regards

                           

                          Etbin

                          • 10. Re: Procedure
                            BluShadow

                            The reason Etbin says "If you're lucky" is because it will be pure luck for your "null" loc_id records to be associated with the records that have a loc_id in the way that you want.

                            You have to remember that having data in a table is just a "set" of data, which has no particular order.  So inserting the data like this:

                             

                            INSERT INTO sub_loc_tb VALUES (101, 'Chennai', 'T-Nagar', NULL);

                            INSERT INTO sub_loc_tb VALUES (102, 'Kancheepuram', 'Tambaram', NULL);

                            INSERT INTO sub_loc_tb VALUES (103, 'Thoothukudi', 'Thiruchendur', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Velachery', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Adayar', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Kovilpatti', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Sivakasi', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Sri-Perumbudur', NULL);

                             

                            or like this:

                             

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Velachery', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Adayar', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Sri-Perumbudur', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Kovilpatti', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Sivakasi', NULL);

                            INSERT INTO sub_loc_tb VALUES (101, 'Chennai', 'T-Nagar', NULL);

                            INSERT INTO sub_loc_tb VALUES (102, 'Kancheepuram', 'Tambaram', NULL);

                            INSERT INTO sub_loc_tb VALUES (103, 'Thoothukudi', 'Thiruchendur', NULL);

                             

                            or like this:

                             

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Adayar', NULL);

                            INSERT INTO sub_loc_tb VALUES (103, 'Thoothukudi', 'Thiruchendur', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Sivakasi', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Sri-Perumbudur', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Kovilpatti', NULL);

                            INSERT INTO sub_loc_tb VALUES (102, 'Kancheepuram', 'Tambaram', NULL);

                            INSERT INTO sub_loc_tb VALUES (101, 'Chennai', 'T-Nagar', NULL);

                            INSERT INTO sub_loc_tb VALUES (NULL, NULL, 'Velachery', NULL);

                             

                            or any other order.... will give you exactly the same set of data to work with.

                             

                            There is NO association between records just because of the order you insert them to a table.

                             

                            If you need an association between records, to allow you to order them, or group them etc. then you need to provide that association as part of the data.

                            • 11. Re: Procedure
                              BluShadow

                              Let's take Etbins solution and just change the order of the data within the WITH clause to prove it...

                               

                              SQL> ed
                              Wrote file afiedt.buf

                                1  with
                                2  sub_loc_tb as
                                3  (select 101 loc_id,'Chennai' city,'T-Nagar' town,NULL gen_id from dual union all
                                4  select 102,'Kancheepuram','Tambaram',NULL from dual union all
                                5  select 103,'Thoothukudi','Thiruchendur', NULL from dual union all
                                6  select NULL,NULL,'Sri-Perumbudur',NULL from dual union all
                                7  select NULL,NULL,'Velachery',NULL from dual union all
                                8  select NULL,NULL,'Adayar',NULL from dual union all
                                9  select NULL,NULL,'Kovilpatti',NULL from dual union all
                              10  select NULL,NULL,'Sivakasi',NULL from dual
                              11  )
                              12  select case when lag(loc_id,1,1) over (order by loc_id) != loc_id
                              13              then loc_id
                              14        end loc_id,
                              15        city,
                              16        town,
                              17        to_char(row_number() over (partition by loc_id order by null) - 1,'00') gen_id
                              18    from (select last_value(loc_id ignore nulls) over (order by rownum) loc_id,city,town
                              19            from sub_loc_tb
                              20*        )
                              SQL> /

                                  LOC_ID CITY         TOWN           GEN
                              ---------- ------------ -------------- ---
                                     101 Chennai      T-Nagar        00
                                     102 Kancheepuram Tambaram       00
                                     103              Velachery      00
                                                      Adayar         01
                                                      Kovilpatti     02
                                                      Sivakasi       03
                                         Thoothukudi  Thiruchendur   04
                                                      Sri-Perumbudur 05

                               

                              8 rows selected.

                               

                              See, the result is completely different, yet it's still the exact same set of data.