Pl sql query for case stmt

i have query where if insert date is checking between two dates i.e date1 and date 2.i want records if date2+8 is less than or equal to date1.and if date2 is greater than one week let's say 10 days greater than date1 it should only get records for 1 week.
i need to use case statement.but i am not sure how to make it.Need assistance.


  select *
         from order_network_element_response
        where case insert_dt when
        insert_dt between to_date('date1', 'MM/DD/YYYY') and
              to_date('date2', 'MM/DD/YYYY') + 8<=to_date('date1', 'MM/DD/YYYY')
         --     get all records for those days
         when
        insert_dt between to_date('date1', 'MM/DD/YYYY') and
              to_date('date2', 'MM/DD/YYYY') + 8>to_date('date1', 'MM/DD/YYYY')
       ---       get records for one week
ANISHKA1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
so, my last suggestion will do that.

the trick is the function LEAST(), which returns the lower value of the 2 arguments...
select *
  from order_network_element_response
  where insert_dt between to_date('date1', 'MM/DD/YYYY') 
    and least ( to_date('date1', 'MM/DD/YYYY') + 8, to_date('date2', 'MM/DD/YYYY'))

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
select *
  from order_network_element_response
   where insert_dt between to_date('date1', 'MM/DD/YYYY') and least ( to_date('date2', 'MM/DD/YYYY') + 8, to_date('date1', 'MM/DD/YYYY'))

Open in new window

0
 
ANISHKA1Author Commented:
not getting expected result.
queries goal is date 2 is less than one week return those rows but if user enters date 2 greater than one week then return only one week records from date1 to date1+8.

i think we might have to use case or if statement but not sure how to implement.

Need assistance.Thanks,
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, simple reversion of date1 and date2 in the second part:
select *
  from order_network_element_response
  where insert_dt between to_date('date1', 'MM/DD/YYYY') 
    and least ( to_date('date1', 'MM/DD/YYYY') + 8, to_date('date2', 'MM/DD/YYYY'))

Open in new window

0
 
ANISHKA1Author Commented:
not as per required..can we do procedure and put if condition for dates ..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>not as per required..
in how far?

>can we do procedure and put if condition for dates ..
in oracle, procedures will not be able to return data for SELECT, unless using a REF CURSOR...

so, you might want to explain a bit more about how it should work, and how you are running the query?
0
 
ANISHKA1Author Commented:
 


let me try to explain the requirement.user has to enter date1 and date2 .in select statement or pl/sql we have to check the date2 input that date2 should not be greater than one week value of date one for example if date1='09/01/2008' then date2='09/15/2008' is not valid input date 2 is exceeding one week value than date1.in this situation we have to return only one week records.
second condition is that if date 2 is not exceeds one week condition then return only those days record.for example date1='09/01/2008' and date2='09/06/2008 then it should return 6 days recode from date1.
So the basic requirement is that record should not exceed more than one week. Following are the colums getting from query.
         MIN,MDN,ACTIVATION_DT,ACCOUNT_ID,PROCDATETIME,ERRCODE

As per my knowledge we can use condition to check date2 and date1
like
insert_dt between date1 and date2
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.