11 Replies Latest reply on Oct 4, 2013 4:45 PM by CharlieMack

    Need help! Can't validate date overlaps for a tabular column

    CharlieMack

      Hello all,

       

      The validation I created to validate whether new or updates rows does not overlap with any records in the table isn't working.

       

      The columns are StartDt and EndDt

       

       

      Validation Type: NOT EXISTS

      Validation expresion:

      select 1

          from  sample S

          WHERE S.STARTDT BETWEEN :STARTDT AND :ENDDT

      OR(S.ENDDT BETWEEN :STARTDT AND :ENDDT)

      or (S.STARTDT <= :STARTDT and S.ENDDT >= :ENDDT)

      or (:STARTDT <= S.STARTDT and :ENDDT >= S.ENDDT)

      Error Message:

      Times overlap

      When Buttons pressed

      -Select Button-

       

      I am using APEX 4.2.2

       

      The following illustration displays different scenarios and outcome based on the validation I created

      TABLE DISPLAYS THE FOLLOWING RECORDS

      NAME                   START DATE                      END DATE

      TEST                    1/1/2012                               12/31/2012

      TEST 2                 1/1/2013                               12/31/2013

      TEST3                  1/1/2014                              12/31/2014

      SCENARIO 1: User submits a contract that begins from feb 2012 to may 2012

      contract               2/1/2012                                5/1/2012

       

      OUTCOME: The user will experience an error because the dates are between 1/1/2012 and 12/31/2012

       

      SCENARIO 2: User submits a contract that begins from feb 2012 to may 2014

      contract               2/1/2012                                5/1/20124

      OUTCOME:The user will experience an error because the dates are between 1/1/2012 and 12/31/2014

       

      SCENARIO 3: User modifies "test" from 1/1/2012 & 12/31/2012 & 1/1/2012 to 5/31/2012

      test                   1/1/2012                                5/21/2012

       

      OUTCOME:The user will experience an error. In this scenario, I don't want the user to receive an error.

        • 1. Re: Need help! Can't validate date overlaps for a tabular column
          Frank Kulash

          Hi,

           

          If :startdt, :enddt, s.startdt and s.enddt all have the same datatype, then the best way to see if the ranges overlap is

           

                :startdt <=  s.enddt

          AND   :enddt   >=  s.startdt

          If one range ends at the exact moment the other one begins, do you consider that overlapping?  If not, remove the = signs from the inequality operators.

           

          This assumes :startdt <= :enddt, and s.startdt <= s.enddt

           

          I hope this answers your question.
          If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          If the problem involves parameters (such as :startdt and :enddt) then give a couple of different sets of parameters, and the resutls you want from the same sample data for each set.  Show exactly how you define and set the parameters.

          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002

          • 2. Re: Need help! Can't validate date overlaps for a tabular column
            CharlieMack


            FrankKulash,

            I created a workspace on apex.oracle.com. Login credentials are:

            Workspace: REYNACKON

            Username: Guest

            Password: iknowican

             

            I was abe to reproduce the issue on Page 900

            I am using SQL developer if that makes a difference

            • 3. Re: Need help! Can't validate date overlaps for a tabular column
              Frank Kulash

              Hi,

               

              Please post everything (CREATE TABLE and INSERT statements for sample data, test parameters, results, explanation and version) on this site.

              • 4. Re: Need help! Can't validate date overlaps for a tabular column
                CharlieMack

                I figured that the reproducing the issue on a workspace will be as good, but I will post those statements later on

                • 5. Re: Need help! Can't validate date overlaps for a tabular column
                  Etbin

                  Something to play with

                   

                  with

                  sample_table as

                  (select 'contract 2' code,'something else' name,to_date('01/01/2012','mm/dd/yyyy') startdt,to_date('12/31/2012','mm/dd/yyyy') enddt from dual union all

                  select 'contract 1','something',to_date('01/01/2011','mm/dd/yyyy'),to_date('12/31/2011','mm/dd/yyyy') from dual union all

                  select 'contract 3','testing',to_date('01/01/2013','mm/dd/yyyy'),to_date('12/31/2013','mm/dd/yyyy') from dual

                  )

                  select dual_from,

                         dual_to,

                         case when greatest(startdt,dual_from) <= least(enddt,dual_to)

                              then 'overlap'

                         end overlap,

                         startdt,

                         enddt

                    from sample_table,

                         (select to_date(:date_from,'yyyymmdd') dual_from,

                                 to_date(:date_to,'yyyymmdd') dual_to

                            from dual

                         )

                   

                  DUAL_FROMDUAL_TOOVERLAPSTARTDTENDDT
                  10/10/201111/11/2012overlap01/01/201212/31/2012
                  10/10/201111/11/2012overlap01/01/201112/31/2011
                  10/10/201111/11/2012-01/01/201312/31/2013

                   

                  Regards

                   

                  Etbin

                  • 6. Re: Need help! Can't validate date overlaps for a tabular column
                    CharlieMack

                    Does it matter which db column is the validation associated with?

                    In my case, the associated column is StartDT. Should it be EndDt?

                    • 7. Re: Need help! Can't validate date overlaps for a tabular column
                      Etbin
                      Does it matter which db column is the validation associated with?

                      I don't get it.

                      overlap is not null means the interval from dual (the one you are checking) has a non empty intersection with the interval from your table (defined by both StartDT and EndDt)

                      If you define the "wrong" column as the one contained in an already existing interval, then one or the other or both can be "wrong".


                      Regards


                      Etbin


                      with

                      sample_table as

                      (select 'contract 2' code,'something else' name,to_date('01/01/2012','mm/dd/yyyy') startdt,to_date('12/31/2012','mm/dd/yyyy') enddt from dual union all

                      select 'contract 1','something',to_date('01/01/2011','mm/dd/yyyy'),to_date('12/31/2011','mm/dd/yyyy') from dual union all

                      select 'contract 3','testing',to_date('01/01/2013','mm/dd/yyyy'),to_date('12/31/2013','mm/dd/yyyy') from dual

                      )

                      select dual_from,

                             dual_to,

                             case when greatest(startdt,dual_from) <= least(enddt,dual_to)

                                  then 'overlap'

                             end overlap,

                             startdt,

                             enddt,

                             case when dual_from between startdt and enddt

                                  then 'wrong'

                             end wrong_start,

                             case when dual_to between startdt and enddt

                                  then 'wrong'

                             end wrong_end

                        from sample_table,

                             (select to_date(:date_from,'yyyymmdd') dual_from,

                                     to_date(:date_to,'yyyymmdd') dual_to

                                from dual

                             )

                      DUAL_FROMDUAL_TOOVERLAPSTARTDTENDDTWRONG_STARTWRONG_END
                      10/10/201111/11/2012overlap01/01/201212/31/2012-wrong
                      10/10/201111/11/2012overlap01/01/201112/31/2011wrong-
                      10/10/201111/11/2012-01/01/201312/31/2013--

                       

                      DUAL_FROMDUAL_TOOVERLAPSTARTDTENDDTWRONG_STARTWRONG_END
                      10/10/201111/11/2011-01/01/201212/31/2012--
                      10/10/201111/11/2011overlap01/01/201112/31/2011wrongwrong
                      10/10/201111/11/2011-01/01/201312/31/2013--

                       

                      DUAL_FROMDUAL_TOOVERLAPSTARTDTENDDTWRONG_STARTWRONG_END
                      10/10/201011/11/2011-01/01/201212/31/2012--
                      10/10/201011/11/2011overlap01/01/201112/31/2011-wrong
                      10/10/201011/11/2011-01/01/201312/31/2013--

                       

                      DUAL_FROMDUAL_TOOVERLAPSTARTDTENDDTWRONG_STARTWRONG_END
                      10/10/201311/11/2014-01/01/201212/31/2012--
                      10/10/201311/11/2014-01/01/201112/31/2011--
                      10/10/201311/11/2014overlap01/01/201312/31/2013wrong-

                       

                      Message was edited by: Etbin some examples added

                      • 8. Re: Need help! Can't validate date overlaps for a tabular column
                        DrabJay

                        When updating a record you need to exclude this record from the records you are checking against in your validation expression.

                         

                        In your scenario 3 the validation fails because the values of the record in the application overlap with the values of the same record as stored in the database.

                        1 person found this helpful
                        • 9. Re: Need help! Can't validate date overlaps for a tabular column
                          CharlieMack

                          DrabJay,

                           

                          Thanks, so I tried this code and it didn't work

                           

                          SELECT 1 FROM SAMPLE S

                          WHERE CODE <> :CODE

                            AND (    S.STARTDT BETWEEN :STARTDT AND :ENDDT

                                  OR S.ENDDT   BETWEEN :STARTDT AND :ENDDT

                          • 10. Re: Need help! Can't validate date overlaps for a tabular column
                            DrabJay

                            Given your original scenario 3 this code should work, so I can only assume there is a difference in the data in the table you have referenced in your most recent query. If you run the query you have posted (returning the actual columns rather than a 'dummy' 1 value) directly against the database substituting in the values you enter in the application which record(s) get returned and hence are overlapping with your record.

                            • 11. Re: Need help! Can't validate date overlaps for a tabular column
                              CharlieMack

                              DrabJay,

                               

                              The answer was in front of my face all along

                               

                              I used the following SQL Statement

                              SELECT 1 FROM SAMPLE S

                              WHERE CODE <> :CODE

                                AND (    :STARTDT <= ENDDT

                              AND        :ENDDT >= STARTDT

                                    )

                               

                              All these scenarios will work based on the validation expression above based on the table below

                               

                              NAME                   START DATE                      END DATE

                              TEST                    1/1/2012                               12/31/2012

                              TEST 2                 1/1/2013                               12/31/2013

                               

                              SCENARIO
                              1: 5/5/2012 - 11/31/2012

                               

                              ERROR

                               

                              SCENARIO 2: 6/7/2010 - 2/31/12

                               

                              ERROR

                               

                              SCENARIO 3: 3/5/2010 - 11/5/2014

                               

                              ERROR

                               

                              SCENARIO 4: 5/5/2012 - 5/15/2013

                               

                              ERROR

                               

                              SCENARIO 5: 5/5/2012 - 5/15/2014

                               

                              ERROR

                               

                              SCENARIO 5: 1/1/2012 - 5/31/2012 (User updates time periods from 1/1/2012 - 12/31/2012 to 1/1/2012 - 5/31/2012)

                               

                              ACCEPTS !!