Solved

Date Clause

Posted on 2010-11-30
13
220 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.
Suggested Courses

628 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