14 Replies Latest reply: Feb 5, 2013 12:18 AM by M.Hassan_999 RSS

    returne between dates in case when

    M.Hassan_999
      i want to return between dates values on case when clauses on where clauses

      like

      and
      (case
      when (cc.segment3 NOT like '4%' and cc.segment3 NOT like '5%')
      then (between to_date('01/07/2012','dd/mm/rrrr') and to_date('31/07/2012','dd/mm/rrrr'))

      when (cc.segment3 like '4%' or cc.segment3 like '5%')
      then (between to_date('01/07/2012','dd/mm/rrrr') and to_date('31/07/2012','dd/mm/rrrr'))
      end) h.default_effective_date

      the problem is in the = operator but i don't know haw to use this

      any help??
        • 1. Re: returne between dates in case when
          SomeoneElse
          Maybe a simple if logic instead of case?
          and h.default_effective_date between to_date('01/07/2012','dd/mm/rrrr') and to_date('31/07/2012','dd/mm/rrrr')
          and (
                  (cc.segment3 NOT like '4%' and cc.segment3 NOT like '5%')
                  or
                  (cc.segment3 like '4%' or cc.segment3 like '5%')
              )
          • 2. Re: returne between dates in case when
            AlbertoFaenza
            Hi Hassan,

            Please next time if you are asking a question don't remove the flag <i>Mark this thread as a question</i>.

            Regards.
            Al
            • 3. Re: returne between dates in case when
              M.Hassan_999
              sorry i write it in wrong way the correct one is
              and
              (case
              when (cc.segment3 NOT like '4%' and cc.segment3 NOT like '5%')
              then (between to_date('01/07/2012','dd/mm/rrrr') and to_date('31/07/2012','dd/mm/rrrr'))
              when (cc.segment3 like '4%' or cc.segment3 like '5%')
              then (between to_date('01/07/2003','dd/mm/rrrr') and to_date('31/07/2012','dd/mm/rrrr'))
              end) h.default_effective_date


              So the solution's not going to work here
              • 4. Re: returne between dates in case when
                Peter vd Zwan
                Hi,

                Another way to use case is like this:
                with test as
                (
                select date '2013-01-30' d1, date '2013-01-31' d2, '444' s3 from dual union all
                select date '2013-01-20' d1, date '2013-01-31' d2, '333' s3 from dual union all
                select date '2013-01-21' d1, date '2013-02-10' d2, '555' s3 from dual union all
                select date '2013-01-22' d1, date '2013-03-10' d2, '333' s3 from dual union all
                select date '2013-01-22' d1, date '2013-03-10' d2, '333' s3 from dual union all
                select date '2013-01-22' d1, date '2013-03-10' d2, '555' s3 from dual union all
                select date '2013-01-22' d1, date '2013-03-10' d2, '555' s3 from dual 
                
                )
                select
                  *
                from
                  test
                where
                  1 = case when s3 like '4%'
                                and d1 between date '2013-01-29' and date '2013-01-31'
                                then 1
                           when s3 like '5%'
                                and d2 between date '2013-01-29' and date '2013-01-31'
                                then 1
                          else 0 end
                ;
                Regards,

                Peter
                • 5. Re: returne between dates in case when
                  SomeoneElse
                  OK, so move the date predicate:
                  and (
                          (cc.segment3 NOT like '4%' and cc.segment3 NOT like '5%'
                           and h.default_effective_date between to_date('01/07/2012','dd/mm/rrrr') and to_date('31/07/2012','dd/mm/rrrr')
                          )
                          or
                          (cc.segment3 like '4%' or cc.segment3 like '5%'
                           and h.default_effective_date between to_date('01/07/2003','dd/mm/rrrr') and to_date('31/07/2012','dd/mm/rrrr')
                          )
                      )
                  • 6. Re: returne between dates in case when
                    M.Hassan_999
                    i didn't recognize the flag in the first place but I'll take this note next time

                    could you help me in my problem
                    • 7. Re: returne between dates in case when
                      M.Hassan_999
                      this is helping bu i need to put the dates as aparameter so haw i did this

                      like

                      and
                      (case
                      when (cc.segment3 NOT like '4%' and cc.segment3 NOT like '5%')
                      then (between to_date(:p_date_datet1,'dd/mm/rrrr') and to_date(:p_date_END,'dd/mm/rrrr'))

                      when (cc.segment3 like '4%' or cc.segment3 like '5%')
                      then (between to_date(:p_date_datet2,'dd/mm/rrrr') and to_date(:p_date_END,'dd/mm/rrrr'))
                      end) h.default_effective_date .


                      could you help please
                      • 8. Re: returne between dates in case when
                        M.Hassan_999
                        it's getting wrong values i think because the OR i need it to be and -got my point -
                        • 9. Re: returne between dates in case when
                          M.Hassan_999
                          haw could i change this into question??
                          • 10. Re: returne between dates in case when
                            SomeoneElse
                            it's getting wrong values i think because the OR i need it to be and -got my point -
                            I don't know what you're telling/asking here.

                            Maybe you should post sample tables and data and show us what you want the results to be.
                            • 11. Re: returne between dates in case when
                              M.Hassan_999
                              ok here is the full coad

                              select
                              DECODE(SUBSTR (CC.segment1, 1, 1), 'J', 'OFFSHORE', 'I', 'OFFSHORE','IN COUNTRY')branch_nature,
                              decode(cc.segment1,'A6260','Head Office','Branches') branch_type,
                              cc.segment1,
                              decode (h.currency_code,'EGP','mahly','agnaby') currency_code ,
                              cc.segment3,
                              t.description,

                              CASE
                              when h.currency_code in('EGP') then sum(l.entered_cr)
                              else 0
                              end entered_cr,--sum(l.entered_cr) entered_cr,

                              CASE
                              when h.currency_code in('EGP') then sum(l.entered_dr)
                              else 0
                              end entered_dr, --sum(l.entered_dr) entered_dr,

                              CASE
                              when h.currency_code NOT in('EGP') then sum(l.accounted_cr)
                              else 0
                              end accounted_Dr,--0 accounted_Dr,

                              CASE
                              when h.currency_code NOT in('EGP') then sum(l.accounted_dr)
                              else 0
                              end accounted_cr --0 accounted_cr



                              from apps.gl_je_headers h,
                              apps.gl_je_lines L,
                              apps.gl_code_combinations cc,
                              apps.fnd_flex_values_tl t ,
                              applsys.fnd_user us,
                              apps.gl_je_batches b
                              where h.status = 'P'
                              and us.user_id = h.Created_by
                              --and h.currency_code in('EGP')
                              and l.je_header_id = h.je_header_id
                              and l.code_combination_id =cc.code_combination_id
                              and cc.segment3 = t.flex_value_meaning
                              and cc.segment3 in ('31000020','40505020')
                              and cc.segment1 in ('A5550','B0010')
                              -- and (DECODE(SUBSTR (CC.segment1, 1, 1), 'J', 'OFFSHORE', 'I', 'OFFSHORE','IN COUNTRY') =:P_branch_nature OR :P_branch_nature is NULL)
                              --and (decode(cc.segment1,'A6260','Head Office','Branches') = :P_BRANCH_TYPE OR :P_BRANCH_TYPE is NULL)
                              and t.description is not null
                              and t.language ='AR'
                              and h.je_batch_id = b.je_batch_id



                              /**********************************************************************/
                              and
                              (case
                              when (cc.segment3 NOT like '4%' and cc.segment3 NOT like '5%')
                              then (between to_date(:P_Start_date,'dd/mm/rrrr') and to_date(:P_END_date,'dd/mm/rrrr'))
                              when (cc.segment3 like '4%' or cc.segment3 like '5%')
                              then (between to_date(:P_Start_date2,'dd/mm/rrrr') and to_date(:P_END_date,'dd/mm/rrrr'))
                              end) h.default_effective_date
                              /**********************************************************************/

                              /*
                              and (case
                              when (cc.segment3 like '4%' or cc.segment3 like '5%')
                              then (h.default_effective_date)
                              end) between to_date('01/07/2012','dd/mm/rrrr') and to_date('31/07/2012','dd/mm/rrrr')*/



                              group by cc.segment3 , t.description , cc.segment1,h.currency_code;

                              the part between tages need to modigied
                              • 12. Re: returne between dates in case when
                                SomeoneElse
                                the part between tages need to modigied
                                OK, so what about the code I gave you?
                                • 13. Re: returne between dates in case when
                                  Peter vd Zwan
                                  Hi,

                                  Try this:
                                  /*
                                  and 
                                  1 = case when cc.segment3 NOT like '4%' and cc.segment3 NOT like '5%'
                                                and h.default_effective_date between to_date(:P_Start_date,'dd/mm/rrrr') and to_date(:P_END_date,'dd/mm/rrrr')
                                          then 1
                                  
                                          when cc.segment3 like '4%' or cc.segment3 like '5%'
                                          and h.default_effective_date between to_date(:P_Start_date2,'dd/mm/rrrr') and to_date(:P_END_date,'dd/mm/rrrr')
                                          then 1
                                      else 0
                                    end
                                  
                                  /*
                                  Regards
                                  Peter
                                  • 14. Re: returne between dates in case when
                                    M.Hassan_999
                                    It's Working Thank YOu :D