14 Replies Latest reply: Jan 16, 2013 4:43 PM by user650888 RSS

    help with logic

    user650888
      Been trying to solve this problem since last two days, and found out the crucial mistake I made

      almost there but need help with this question
      create table test_g(x date);
      
      insert into test_g (X)
      values (to_date('01-11-2001', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('06-11-2001', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('07-11-2001', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('14-11-2001', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('25-11-2001', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('02-11-2011', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('03-11-2012', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('06-11-2012', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('09-11-2012', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('14-11-2012', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('17-11-2012', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('22-11-2012', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('03-12-2012', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('04-12-2012', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('12-12-2012', 'dd-mm-yyyy'));
      
      insert into test_g (X)
      values (to_date('31-12-2012', 'dd-mm-yyyy'));
      
      commit;
      I have to set v_b2 to true in below procedure based on the following:

      check to see if the input parameter p_in_date is equal to one of the latest two dates for the given month / year

      EG: If the input parameter is a date in November 2001, then I want to check if that date is equal to 11/14/2001 or 11/25/2001 which are the last latest two dates for the month of novmber 2001

      below is my code
      CREATE OR REPLACE PROCEDURE PTEST(p_in_date date)
      IS
      v_min date;
      v_max1 date;
      v_max2 date;
      
      v_b1 boolean:= FALSE;
      v_b2 boolean := FALSE;
      BEGIN
        
      v_b1 := FALSE;
      v_b2 := FALSE;
        
      dbms_output.put_line('p_in_date: '||p_in_date);
        
      select min(x)
      into v_min
      from test_g
      where trunc(x,'MM') = TRUNC(p_in_date,'MM');
      
      IF TRUNC(v_min) = trunc(p_in_date) then
         dbms_output.put_line('yes 1');
        v_b1 := true;
      end if;
      
      if v_b1 then
        dbms_output.put_line('yes1');
        
      end if;
      
      dbms_output.put_line('v_min: '||v_min);
      
      -- here i want to check if p_in_date is either equal to the earliest entry in test_g for the given month / year
      -- or one of the latest two dates for the given month / year, eg: if p_in_date is any month in Nov 2001, check if
      -- p_in_date is eihter 11/14/2001 or 11/25/2001
      
      
      
      END;
      Edited by: user650888 on Jan 14, 2013 2:31 PM
        • 1. Re: help with logic
          Frank Kulash
          Hi,
          user650888 wrote:
          Been trying to solve this problem since last two days, and found out the crucial mistake I made

          almost there but need help with this question ...
          What is the question? Do you mean:
          I have to set v_b2 to true in below procedure based on the following:

          check to see if the input parameter p_in_date is equal to one of the latest two dates for the given month / year

          EG: If the input parameter is a date in November 2001, then I want to check if that date is equal to 11/14/2001 or 11/25/2001 which are the last latest two dates for the month of novmber 2001
          Here's one way:
          CREATE OR REPLACE PROCEDURE ptest 
          (   p_in_date  IN   DATE
          )
          IS
              v_b2     BOOLEAN     := FALSE;
          BEGIN    
              FOR  r  IN (
                       WITH   got_r_num     AS
                       (
                               SELECT     x
                            ,          DENSE_RANK () OVER ( PARTITION BY  TRUNC (x, 'MONTH')
                                                                         ORDER BY          x  DESC
                                                      )         AS r_num
                            FROM    test_g
                            WHERE   x     >=              TRUNC (p_in_date, 'MONTH')
                            AND        x     <  ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                                       , 1
                                              )
                       )
                     SELECT DISTINCT
                                  *     -- or list columns wanted
                     FROM      got_r_num
                     WHERE     r_num     <= 2
                     AND     x     = p_in_date
                 )
              LOOP
               v_b2 := TRUE;
              END LOOP;
          
              -- optional testing
              dbms_output.put (  'Was '
                            ||  TO_CHAR (p_in_date, 'DD-Mon-YYYY HH24:MI:SS')
                        ||  ' one of the last 2 dates in '
                        ||  TO_CHAR (p_in_date, 'FMMonth, YYYY')
                        ||  '?  '
                        );
              IF  v_b2
              THEN
                  dbms_output.put_line ('Yes.');
              ELSE
                  dbms_output.put_line ('No.');
              END IF;
          END ptest;
          >
          below is my code ...
          Is it doing what you want or not?
          If it is doing what you want, what is the question?
          If not, post a coulple of parameters that make it give the wrong results, and post what the correct results are in each case.
          • 2. Re: help with logic
            user650888
            I do not prefer to change the existing code, thinking in terms of finding an sql that will populate two variables, one with the maximum date from test_g (11/25/2001), the other with the maximum but one (11/14/2001) for the month of November 2001

            so I can proceed with v_b2 being true or not
            • 3. Re: help with logic
              Frank Kulash
              Hi,
              user650888 wrote:
              I do not prefer to change the existing code, thinking in terms of finding an sql that will populate two variables, one with the maximum date from test_g (11/25/2001), the other with the maximum but one (11/14/2001) for the month of November 2001
              Sorry, I don't understand.
              Is the procedure doing what you want or not?
              If not, then you have to change the existing code. The existing code will continue to produce the existing results.
              • 4. Re: help with logic
                user650888
                OK, Given the below code for the data posted above

                 CREATE OR REPLACE PROCEDURE PTEST(p_in_date date)
                IS
                v_min date;
                v_max1 date;
                v_max2 date;
                 
                v_b1 boolean:= FALSE;
                v_b2 boolean := FALSE;
                BEGIN
                  
                v_b1 := FALSE;
                v_b2 := FALSE;
                  
                dbms_output.put_line('p_in_date: '||p_in_date);
                  
                select min(x)
                into v_min
                from test_g
                where trunc(x,'MM') = TRUNC(p_in_date,'MM');
                 
                IF TRUNC(v_min) = trunc(p_in_date) then
                   dbms_output.put_line('yes 1');
                  v_b1 := true;
                end if;
                 
                if v_b1 then
                  dbms_output.put_line('yes1');
                  
                end if;
                 
                dbms_output.put_line('v_min: '||v_min);
                 
                -- here i want to check if p_in_date is either equal to the earliest entry in test_g for the given month / year
                -- or one of the latest two dates for the given month / year, eg: if p_in_date is any month in Nov 2001, check if
                -- p_in_date is eihter 11/14/2001 or 11/25/2001
                 
                 
                 
                END;
                if i execute above like this

                begin

                ptest(to_date('11/03/2012','MM/DD/YYYY'));

                -- ptest(to_date('03-11-2012','DD-MM-YYYY'));


                end;

                It prints yes 1, meaning it has successfully checked that the date entered is the minum entry for November 2012 in test_g

                Now similary, if I execute above for '11/25/2001', I just want to set the boolean v_b2 to be true, (by checking if the input parameter is equal to either (11/25/2001, latest date for Nov 2001 or the one before that which is 11/14/2001)
                • 5. Re: help with logic
                  SamFisher
                  Sort the dates based on the year 2001(YYYY) in descending order. Get the first 2 records based on rownumber.
                  DECLARE
                     p_in_date   DATE := 'Jan-10-2012';
                     v           DATE;
                     v_flag      varchar2(1) := 'N';
                  BEGIN
                     BEGIN
                        SELECT *
                          INTO v
                          FROM (SELECT *
                                  FROM (  SELECT *
                                            FROM test_g
                                           WHERE TO_CHAR (x, 'MMYYYY') =
                                                    TO_CHAR (p_in_date, 'MMYYYY')
                                        ORDER BY x DESC)
                                 WHERE ROWNUM < 3)
                         WHERE x = p_in_date;
                         v_flag := 'Y';
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           -- Flag is not set   
                           dbms_output.put_line(sqlerrm);
                     END;
                  END;
                  Not tested. But just a kinda pseudo code for reference.
                  • 6. Re: help with logic
                    Frank Kulash
                    Hi,

                    What's wrong with the solution I posted earlier?
                    If you're really only interest in whether p_in_date is one of the latest 2 dates or not, then you can simplify the loop to:
                    ... FOR  r  IN (
                                 WITH   in_order     AS
                                 (
                                         SELECT DISTINCT
                                                x
                                      FROM      test_g
                                      WHERE     x     >=              TRUNC (p_in_date, 'MONTH')
                                      AND          x     <  ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                                                 , 1
                                                        )
                                      ORDER BY  x   DESC
                                 )
                               SELECT    x
                               FROM      got_r_num
                               WHERE     ROWNUM     <= 2
                           )
                        LOOP
                            IF  r.x  = p_in_date
                         THEN
                             v_b2 := TRUE;
                         END IF;
                        END LOOP;
                    If you still have problems, post the complete code where you tried to use this, and the code that calls it. Point out where the results are wrong, and post the exact results you want to get from the same input(s) and the same sample data.

                    Please mark your duplicate thread {message:id=10791848} as "Answered".
                    • 7. Re: help with logic
                      user650888
                      Thanks, this really works, let me go through your solution and would ask you any questions

                      thanks again
                      • 8. Re: help with logic
                        user650888
                        Thanks, i am trying to understand this part
                        FOR  r  IN (
                                     WITH   got_r_num     AS
                                     (
                                             SELECT     x
                                          ,          DENSE_RANK () OVER ( PARTITION BY  TRUNC (x, 'MONTH')
                                                                                       ORDER BY          x  DESC
                                                                    )         AS r_num
                                          FROM    test_g
                                          WHERE   x     >=              TRUNC (p_in_date, 'MONTH')
                                          AND        x     <  ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                                                     , 1
                                                            )
                                     )
                                   SELECT DISTINCT
                                                *     -- or list columns wanted
                                   FROM      got_r_num
                                   WHERE     r_num     <= 2
                                   AND     x     = p_in_date
                               )
                            LOOP
                             v_b2 := TRUE;
                            END LOOP;
                        so basically with this
                           WHERE   x     >=              TRUNC (p_in_date, 'MONTH')
                                          AND        x     <  ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                                                     , 1
                                                            )
                        You are saying given a date, from the 1st of that month till the last day of that month - correct ?
                        Does trunc and using 'MONTH' extracts the month FOR THAT YEAR ?

                        I understand got_r_num will have the latest two dates for that month given a date

                        so,

                        DENSE_RANK () OVER ( PARTITION BY TRUNC ( x, 'MONTH') - - why are you using trunc here ?

                        also,

                        your structure is similar to

                        FOR record_name in cursor_name LOOP
                        v_b2 := true;
                        END LOOP;

                        Is it similar to our normal cursor for loop ?

                        I am wondering how v_b2 is correctly not set to true for dates other than latest two dates for the month, is it because the control
                        only goes to the loop because you said

                        SELECT DISTINCT
                                  *     -- or list columns wanted
                             FROM got_r_num
                             WHERE r_num     <= 2
                             AND     x     = p_in_date

                        so if the input param date is not one of the two latest dates, then the control does not go to the loop at all ?
                        • 9. Re: help with logic
                          user650888
                          ok
                          FOR  r  IN (
                                       WITH   got_r_num     AS
                                       (
                                               SELECT     x
                                            ,          DENSE_RANK () OVER ( PARTITION BY  TRUNC (x, 'MONTH')
                                                                                         ORDER BY          x  DESC
                                                                      )         AS r_num
                                            FROM    test_g
                                            WHERE   x     >=              TRUNC (p_in_date, 'MONTH')
                                            AND        x     <  ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                                                       , 1
                                                              )
                                       )
                                     SELECT DISTINCT
                                                  *     -- or list columns wanted
                                     FROM      got_r_num
                                     WHERE     r_num     <= 2
                                     AND     x     = p_in_date
                                 )
                              LOOP
                               v_b2 := TRUE;
                              END LOOP;
                          The below ranks all the records from the table for a particular month and year extracted from the date
                          WITH   got_r_num     AS
                                       (
                                               SELECT     x
                                            ,          DENSE_RANK () OVER ( PARTITION BY  TRUNC (x, 'MONTH')
                                                                                         ORDER BY          x  DESC
                                                                      )         AS r_num
                                            FROM    test_g
                                            WHERE   x     >=              TRUNC (p_in_date, 'MONTH')
                                            AND        x     <  ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                                                       , 1
                                                              )
                                       )
                          SELECT DISTINCT
                                                  *     -- or list columns wanted
                                     FROM      got_r_num
                                     WHERE     r_num     <= 2
                                     AND     x     = p_in_date
                          the above checks if the input parameter is equal to the latest two dates, and if yes, the control goes to the loop where you
                          set the booleann to true,

                          is my explanation correct ?
                          • 10. Re: help with logic
                            Frank Kulash
                            Hi,
                            user650888 wrote:
                            ... so basically with this
                            WHERE   x     >=              TRUNC (p_in_date, 'MONTH')
                                              AND        x     <  ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                                                         , 1
                                                                )
                            You are saying given a date, from the 1st of that month till the last day of that month - correct ?
                            Right.
                            More precisely, what is is saying is that x has to be after the beginning of the month that contains p_in_date, and also that x must be before the beginning of the next month.
                            Does trunc and using 'MONTH' extracts the month FOR THAT YEAR ?
                            All functions, including TRUNC are documented in the SQL language manual
                            TRUNC (p_in_date, 'MONTH') returns the first DATE in the same month as p_in_date.
                            I understand got_r_num will have the latest two dates for that month given a date

                            so,

                            DENSE_RANK () OVER ( PARTITION BY TRUNC ( x, 'MONTH') - - why are you using trunc here ?
                            That was left over from an earlier solution when the requirements were less clear. Since the WHERE clause is limiting the whole result set to just one month, you don't need that PARTITION BY clause at all. Sorry, I should have removed it. (Leaving it in doesn't change the results, it just doesn't do any good.)
                            also,

                            your structure is similar to

                            FOR record_name in cursor_name LOOP
                            v_b2 := true;
                            END LOOP;

                            Is it similar to our normal cursor for loop ?
                            I'm not sure I understand the question.
                            You can either code the entire query in the FOR statement (like I did) or define a cursor in the DECLARE section, and reference it in the FOR statement. The difference is just in coding style. In this case, they will be equally efficient.
                            I am wondering how v_b2 is correctly not set to true for dates other than latest two dates for the month, is it because the control
                            only goes to the loop because you said

                            SELECT DISTINCT
                                      *     -- or list columns wanted
                                 FROM got_r_num
                                 WHERE r_num     <= 2
                                 AND     x     = p_in_date

                            so if the input param date is not one of the two latest dates, then the control does not go to the loop at all ?
                            I think you understand. More precisely, control goes to the loop but does not go inside the loop.
                            If the query returns N rows, then the body of the FOR loop is executed N times.
                            If the query returns 0 rows, then the body of the FOR loop is executed 0 times.
                            • 11. Re: help with logic
                              user650888
                              drop table test_g 
                              
                              create table test_g(x date, y number);
                               
                              insert into test_g (X,y)
                              values (to_date('01-11-2001', 'dd-mm-yyyy'),1);
                               
                              insert into test_g (X,y)
                              values (to_date('06-11-2001', 'dd-mm-yyyy'),2);
                               
                              insert into test_g (X,y)
                              values (to_date('07-11-2001', 'dd-mm-yyyy'),3);
                               
                              insert into test_g (X,y)
                              values (to_date('14-11-2001', 'dd-mm-yyyy'),4);
                               
                              insert into test_g (X,y)
                              values (to_date('25-11-2001', 'dd-mm-yyyy'),5);
                               
                              insert into test_g (X,y)
                              values (to_date('02-11-2011', 'dd-mm-yyyy'),6);
                               
                              insert into test_g (X,y)
                              values (to_date('03-11-2012', 'dd-mm-yyyy'),7);
                               
                              insert into test_g (X,y)
                              values (to_date('06-11-2012', 'dd-mm-yyyy'),8);
                               
                              insert into test_g (X,y)
                              values (to_date('09-11-2012', 'dd-mm-yyyy'),9);
                               
                              insert into test_g (X,y)
                              values (to_date('14-11-2012', 'dd-mm-yyyy'),10);
                               
                              insert into test_g (X,y)
                              values (to_date('17-11-2012', 'dd-mm-yyyy'),11);
                               
                              insert into test_g (X,y)
                              values (to_date('22-11-2012', 'dd-mm-yyyy'),12);
                               
                              insert into test_g (X,y)
                              values (to_date('03-12-2012', 'dd-mm-yyyy'),13);
                               
                              insert into test_g (X,y)
                              values (to_date('04-12-2012', 'dd-mm-yyyy'),14);
                               
                              insert into test_g (X,y)
                              values (to_date('12-12-2012', 'dd-mm-yyyy'),15);
                               
                              insert into test_g (X,y)
                              values (to_date('31-12-2012', 'dd-mm-yyyy'),16);
                               
                              commit;
                              I just added a column and selecting y based on the date passed, I am getting different results in dev environment vs test environment

                              is this because of different date format ?
                              CREATE OR REPLACE PROCEDURE ptest 
                              (   p_in_date  IN   DATE
                              )
                              IS
                                  v_b2    BOOLEAN := FALSE;
                                  v_seq number;
                                  
                              BEGIN    
                              
                                select y
                                into v_seq
                                from test_g
                                where x =  p_in_date;
                                
                                
                                  FOR  r  IN (
                                         WITH   got_r_num AS
                                         (
                                                 SELECT   x
                                             ,        DENSE_RANK () OVER ( PARTITION BY  TRUNC (x, 'MONTH')
                                                                               ORDER BY      x  DESC
                                                             )       AS r_num
                                             FROM    test_g
                                             WHERE   x    >=              TRUNC (p_in_date, 'MONTH')
                                             AND     x    <  ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                                            , 1
                                                            )
                                         )
                                        SELECT DISTINCT
                                                  *   -- or list columns wanted
                                        FROM      got_r_num
                                        WHERE     r_num   <= 2
                                        AND   x   = p_in_date
                                    )
                                  LOOP
                                  v_b2 := TRUE;
                                  END LOOP;
                               
                                  -- optional testing
                                  dbms_output.put (  'Was '
                                              ||  TO_CHAR (p_in_date, 'DD-Mon-YYYY HH24:MI:SS')
                                          ||  ' one of the last 2 dates in '
                                          ||  TO_CHAR (p_in_date, 'FMMonth, YYYY')
                                          ||  '?  '
                                          );
                                  IF  v_b2
                                  THEN
                                      dbms_output.put_line ('Yes.');
                                  ELSE
                                      dbms_output.put_line ('No.');
                                  END IF;
                              END ptest;
                              how to make sure i get the correct value of y given a date ?

                              is below correct way of comparing dates ? ( x = p_in_date)

                              select y
                              into v_seq
                              from test_g
                              where x = p_in_date;
                              • 12. Re: help with logic
                                user650888
                                Also if I want to find out the earliest date for that month and year from test_g given a date


                                Eg: If input parameter is 07-NOV-2001, I want to see 01-NOV-2001 (From above data), basically from the given date, check the
                                table what is the earliest date for that month and year

                                I am using this

                                select min( x)
                                from test_g
                                where trunc( x,'MM') = TRUNC(x,'MM');

                                Is this correct ?
                                • 13. Re: help with logic
                                  Frank Kulash
                                  Hi,
                                  user650888 wrote:
                                  I just added a column and selecting y based on the date passed, I am getting different results in dev environment vs test environment

                                  is this because of different date format ?
                                  It's hard to tell what the problem is, since I can't re-create it. I'm guessing different date formats is NOT the issue, because you're correctly using TO_DATE for all string-to-date conversions.
                                  Are you sure you're running the exact same procedure, with the exact same data, on both systems?
                                  Give an example or two of different results you get on the two systems.
                                  ... how to make sure i get the correct value of y given a date ?

                                  is below correct way of comparing dates ? ( x = p_in_date)
                                  Yes, that's one correct way of comparing dates.
                                  select y
                                  into v_seq
                                  from test_g
                                  where x = p_in_date;
                                  SELECT ... INTO will raise an error if the query does not return exactly 1 row.
                                  If you use aggregate functions without a GROUP BY clause, the query is guaranteed to return 1 row, so
                                  SELECT  MIN (y)
                                  INTO     v_seq
                                  FROM     test_g
                                  WHERE      x     =  p_in_date;
                                  will not raise a run-time error, regardless of what's in the table.
                                  If there is no row where x = p_in_date, then it will set v_seq to NULL.
                                  If there are 2 or more rows where x = p_in_date (that means x is not unique, unlike your sample data), then it will set v_seq to the lowest of the matching y values.
                                  Also if I want to find out the earliest date for that month and year from test_g given a date


                                  Eg: If input parameter is 07-NOV-2001, I want to see 01-NOV-2001 (From above data), basically from the given date, check the
                                  table what is the earliest date for that month and year

                                  I am using this

                                  select min( x)
                                  from test_g
                                  where trunc( x,'MM') = TRUNC(x,'MM');

                                  Is this correct ?
                                  When you have the exact same expression on both sides of an = sign, the result is always TRUE (unless the expression is NULL.)
                                  Did you mean
                                  select  min( x)
                                  from    test_g
                                  where   trunc( x,'MM') = TRUNC(p_in_date,'MM');
                                  That will get the right results, but it's inefficient. It's faster if you use x alone, without any function, on one side of the = sign, like I did:
                                  ...            WHERE   x    >=              TRUNC (p_in_date, 'MONTH')
                                                 AND     x    <  ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                                                , 1
                                                                )
                                  If the table is small, the difference in speed may only be a fraction of a second. You'll have to decide for yourself how important efficiency is in this case.
                                  • 14. Re: help with logic
                                    user650888
                                    resolved, thanks for all the knowledge.....