This discussion is archived
14 Replies Latest reply: Jan 16, 2013 2:43 PM by user650888 RSS

help with logic

user650888 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks, this really works, let me go through your solution and would ask you any questions

    thanks again
  • 8. Re: help with logic
    user650888 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    resolved, thanks for all the knowledge.....

Legend

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