7 Replies Latest reply on May 15, 2014 2:27 PM by Brian Bontrager

    PLEASE HELP ME

    Bhas

      n/a

        • 1. Re: PLEASE HELP ME OUT WITH QUERY
          SKP

          Use simple UPDATE query ,is it required PLSQL?

           

          ALTER session SET NLS_DATE_FORMAT='dd-mon-yyyy HH24';

            -- Considering hour 7 as 07 AM you may change 19 for PM hour)

           

            UPDATE TMP_DATA_MINS SET

            display_volume = null,

            RULE_APPLIED ='Rule 2 : Three consecutive time intervals are missing with in the period'

            WHERE TO_DATE(DAYASDATE,'dd-mon-yyyy HH24')=to_date('25-DEC-2013 07','dd-mon-yyyy HH24');

          • 2. Re: PLEASE HELP ME OUT WITH QUERY
            Paul  Horth

            Bhas wrote:

             

            hi every one

             

            i need query to update DISPLAY_VOLUME to be null where rule apllied

             

            CREATE TABLE C2.TMP_DATA_MINS

            (

              DAYASDATE       DATE,

              HOURASNUMBER    VARCHAR2(10 BYTE),

              COSIT           INTEGER,

              LANEDIRECTION   INTEGER,

              RAW_VOLUME      NUMBER,

              DISPLAY_VOLUME  NUMBER,

              RULE_APPLIED    VARCHAR2(200 BYTE)

            )

             

            sample data

            DAYASDATE HOURASNUMBER    COSIT LANEDIRECTION RAW_VOLUME DISPLAY_VOLUME RULE_APPLIED
            12/25/2013 07:00:00          07 42 1 54 54
            12/25/2013 07:05:00          07 42 1 58 58
            12/25/2013 07:10:00          07 42 1 49 49
            12/25/2013 07:15:00          07 42 1 53 53
            12/25/2013 07:20:00           7 42 1 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013      07:25:00            7 42 1 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:30:00 7 42 1 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:35:00 07 42 1 54 54
            12/25/2013 07:40:00 07 42 1 50 50
            12/25/2013 07:45:00 07 42 1 41 41
            12/25/2013 07:50:00 07 42 1 36 36
            12/25/2013 07:55:00 07 42 1 37 37

             

            i need to set display volume to be set null for whole hour intervals

             

            o/p something like

             

            DAYASDATE HOURASNUMBER COSIT LANEDIRECTION RAW_VOLUME DISPLAY_VOLUME RULE_APPLIED
            12/25/2013 07:00:00         07 42 1 54 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:05:00          07 42 1 58 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:10:00           07 42 1 49 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:15:00            07 42 1 53 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:20:00            7 42 1 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:25:00           7 42 1 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:30:00              7 42 1 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013  07:35:00             0 7 42 1 54 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:40:00            07 42 1 50 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:45:00            07 42 1 41 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:50:00              07 42 1 36 Rule 2 : Three consecutive time intervals are missing with in the period
            12/25/2013 07:55:00              07 42 1 37 Rule 2 : Three consecutive time intervals are missing with in the period

             

             

            i was trying to doo with following code but no change in output

             

            create or Replace procedure C2.Update_x

             

            as

             

            cursor c3 is select * from tmp_data_mins;

             

            R_Extract_Date date;

             

            R_Extract_Rule varchar2(200);

             

            begin

             

            for r in c3

             

            loop

             

                 R_Extract_Date:= r.dayasdate;

             

                 R_Extract_Rule:= r.RULE_APPLIED;

             

             

             

                if ( R_Extract_Rule = 'Rule 2 : Three consecutive time intervals are missing with in the period') then

             

                 R_Extract_Date:= to_char(R_Extract_Date,'yyyymmdd hh24');

             

                 Update tmp_data_mins set display_volume = null,

              

                 RULE_APPLIED ='Rule 2 : Three consecutive time intervals are missing with in the period'

                 where to_char(DAYASDATE,'yyyymmdd hh24')= R_Extract_Date;

             

                end if;

             

            end loop;

             

            end;

            Firstly, I would say you need to learn how to handle dates properly.

             

            Why are you assigning a string to a date, or comparing a string with a date?

             

            Secondly, you need to learn to avoid using cursor loops and just do a single set-based update or merge.

            • 3. Re: PLEASE HELP ME OUT WITH QUERY
              Brian Bontrager

              Much to learn here.

              1. Don't write PL/SQL when SQL will do.  You are making it much harder than it needs to be.  Hint: The SQL statement will look very similar to your requirement sentence, and not be much longer.

               

              If this is an exercise in learning PL/SQL, then we can look at some of those issues:

               

              2. You are testing the value of a column, and then setting the value of that column back to itself.  Why?  Only update columns that need to actually change.

              3. R_EXTRACT_DATE is a date variable.  You can't set it to a string.  What might happen in this case is an implicit conversion back to DATE.  This is another unnecessary (and potential data-corrupting) statement. Later you are converting DAYASDATE (a date) into a string to compare incorrectly to a date.  Compare dates to dates in their native form - no need for the TO_CHAR in either case.

              4. It looks like you want to update the same row the cursor is processing.  There is a better/easier way:  Search documentation or Google for "PL/SQL SELECT FOR UPDATE" and "WHERE CURRENT OF"

               

              Points 2, 3, and 4 become irrelevant when you fix point 1, but are mentioned so you know where you have other opportunity for education.

              • 4. Re: PLEASE HELP ME OUT WITH QUERY
                Bhas

                Thanks for coming back to me.

                 

                i  have to process 1.5 million rows from table in that case sql works?

                • 5. Re: PLEASE HELP ME OUT WITH QUERY
                  Bhas

                  Thanks for coming back to me.

                  i  have to process 1.5 million rows from table where each contains  give an idea how to do that

                  • 6. Re: PLEASE HELP ME OUT WITH QUERY
                    Paul  Horth

                    Bhas wrote:

                     

                    Thanks for coming back to me.

                    i  have to process 1.5 million rows from table where each contains  give an idea how to do that

                    Each contains what?

                     

                    As far as I can tell you want to update display_volume to null if the extract rule is 'Rule 2 : Three consecutive time intervals are missing with in the period'

                     

                    In which case

                     

                    update tmp_data_mins

                    set display_volume = null

                    where rule_applied = 'Rule 2 : Three consecutive time intervals are missing with in the period';

                     

                    If that's not what you meant, please provide insert statements to go with your create table,

                    example output that you expect and how to get from input to output.

                    • 7. Re: PLEASE HELP ME OUT WITH QUERY
                      Brian Bontrager

                      See Paul's answer. If something can be done equivalently in SQL or PL/SQL, use SQL.