Solved

Date Clause

Posted on 2010-11-30
13
219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 143

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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
 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

738 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