Solved

Date Clause

Posted on 2010-11-30
13
215 Views
Last Modified: 2012-05-10
I need the following query to altered

select *
from BLOCKMEMBER
where trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO

Without disturbing the above I want check one more cndition
if EFFECTIVEDATEFROM is less than sysdate and if EFFECTIVEDATETO is null then it should appear in output of query.

if EFFECTIVEDATEFROM is greater than sysdate and if EFFECTIVEDATETO is null then it should NOT appear in output of query.

and I need to conver this stored procedure with input MEMBER_ID OR EMPLOYEE_ID OR CLUB_ID
0
Comment
Question by:GRChandrashekar
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34237597
what about this:
select *
from BLOCKMEMBER
where trunc(sysdate) between EFFECTIVEDATEFROM and NVL(EFFECTIVEDATETO, sysdate)

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 34237610
Works ok but if EFFECTIVEDATEFROM is equal to sysdate then it is not coming in output. it should come
0
 
LVL 12

Expert Comment

by:enachemc
ID: 34237611
select *
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (EFFECTIVEDATEFROM < trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID)


p_ are paremeters
0
 

Author Comment

by:GRChandrashekar
ID: 34237620
angelIII:
enachemc:

Works ok but if EFFECTIVEDATEFROM is equal to sysdate then it is not coming in output. it should come
0
 
LVL 1

Expert Comment

by:sunny25
ID: 34237624
I hope this fulfils your requirement
select *
from BLOCKMEMBER
where trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or
(EFFECTIVEDATEFROM <TRUNC(sysdate) AND EFFECTIVEDATETO IS NULL )
0
 

Author Comment

by:GRChandrashekar
ID: 34237635
sunny25:
Works ok but if EFFECTIVEDATEFROM is equal to sysdate then it is not coming in output. it should come
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

by:enachemc
ID: 34237637
select *
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (EFFECTIVEDATEFROM <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID)
0
 

Author Comment

by:GRChandrashekar
ID: 34237644
enachemc: Stil not working
0
 

Author Comment

by:GRChandrashekar
ID: 34237649
I have given effectivefrom date as 30/11/2010 09:00:00 PM which is sysdate and effective to is null
0
 
LVL 12

Expert Comment

by:enachemc
ID: 34237659
select *
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID)
0
 
LVL 1

Expert Comment

by:sunny25
ID: 34237664
try this
EFFECTIVEDATEFROM <=TRUNC(sysdate) AND EFFECTIVEDATETO IS NULL
0
 

Author Comment

by:GRChandrashekar
ID: 34237671
@ enachemc:

This is perfect.
Request please give me stored procedure to pass date instead of sysdate and to pass MEMBER_ID OR
EMPLOYEE_ID
OR
CLUB_ID
0
 
LVL 12

Accepted Solution

by:
enachemc earned 500 total points
ID: 34237713
create or replace procedure prod(dt date, p_MEMBER_ID number, p_EMPLOYEE_ID number, p_CLUB_ID number)
res number;
begin
select id into re into
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID);

--if you want only one of the IDs, just make the others NULL in the calling sql
end prod;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now