I couldn’t find any related topic in the forum so here it goes. I am using BO 6.1.3 with Oracle 8.1 database. We are using two different universes and about 7 different data providers to generate about 6 different types of reports. The output from the queries is exactly what we need. Later on we decided to select the parameters from the existing objects from the queries. We have come across a challenge in selecting the dates.
Here’s the issue:
The data that we are getting also has some objects like GROUP, ID and DATES that are blank sometimes since no data has been entered in those objects yet. We were able to select blank or null data from those objects by using the following example from the FAQ FAQ: Designer
Now one challenge is that we also have a date range and since some of the dates are blank we are trying to find a solution as to how should the code go by to select either the date range or select all the dates that include the blank dates. Does anyone have an idea how to go about with this… Hope my explaination states the problem clearly.
Are you trying to prompt the user for a date range in the query, or trying to filter the results once you get data? Either way, this is doable, just let know which you want.
Thanks for the quick reply. We are trying to prompt the user for a date range in the query.
Of course you must have interpreted it right but just one more time. The users should be able to select their date range or (not the B.O. OR but the normal english “or” ) all the dates that includes the blank dates as well. The date prompts does not allow to select blank dates as you must be familiar which is what we are trying to achieve. Hope this hasn’t confused you more
Thanks for your help. Took me a while to get back on the same topic. Looks like I confused you a little bit.
BO 6.1.3 and Oracle 8.1
Let me give you a scenario:
I used the Date Range just like you mentioned (but without the NULL):
(Table.Date > @Prompt('1. Enter begin date:','D',,mono,free) AND Table.Date < @Prompt('2. Enter end date:','D',,mono,free))
Here’s what I was able to achieve:
When I use the date range it works fine.
When I use the start date range as 1/1/1900 and End Date Range as a specific date like 09/15/05. It works fine too. (Basically what it does is - Everything Less Than 09/15/05)
When I use the start date range as say 03/20/03 and End Date Range as 12/31/9999. It works fine too. (Basically what it does is - Everything greater than 03/20/03.)
In the first three options we don’t want the NULL dates which works fine.
Here’s the last thing that we want to accomplish. When we select Start Date as 1/1/1900 and End Date as 12/31/9999. It should show all the dates including the NULL dates. Is there a way to achieve this or what should I add in the code that can help me achieve this? We are flexible to work on Designer or on Reporter side.
OK, now you’ll need to use a CASE statement for that. It will look something like this:
CASE WHEN @Prompt('1. Enter begin date:','D',,mono,free)= 1/1/1900 AND @Prompt('2. Enter end date:','D',,mono,free)= 12/31/9999 1=1 ELSE Table.Date > @Prompt('1. Enter begin date:','D',,mono,free) AND Table.Date < @Prompt('2. Enter end date:','D',,mono,free)
You may need to play with the formatting of the date, but essentially this is creating a condition that always evaluates to TRUE if those dates are entered, so there would essentially be no condition on the date at all.
Thanks for your reply. I tried the code that you gave me and played around with the CASE WHEN and the quotes and format. I think I am very close to achieving what I want just one last step where I am stuck not sure where I am going wrong. I am sure I am doing something really silly but can’t see it myself. Your help will be highly appreciated. Below is the code that I am using that gives me the error:
PARSE FAILED: ORA-00905: missing keyword
CASE WHEN
@Prompt('1. Enter begin date:','D','',mono,free) = '1/1/1900' AND @Prompt('2. Enter end date:','D','',mono,free) = '12/31/9999'
THEN 1=1
ELSE
Table.Date > @Prompt('1. Enter begin date:','D',,mono,free) AND Table.Date < @Prompt('2. Enter end date:','D',,mono,free)
END
Forget the CASE statement in this scenario (and drop the empty ’ ’ in the LOV parameter for @prompt as well), just use AND and OR such as:
(
@Prompt('1. Enter begin date:', 'D', ,mono, free) = To_Date ('01/01/1900', 'mm/dd/yyyy')
AND @Prompt('2. Enter end date:', 'D', ,mono, free) = To_Date ('12/31/9999', 'mm/dd/yyyy')
)
OR
(
Table.Date > @Prompt('1. Enter begin date:', 'D', ,mono, free)
AND Table.Date < @Prompt('2. Enter end date:', 'D', ,mono, free)
)
Thank you for taking some time out and working out the solution. Well not sure why the CASE WHEN code doesn’t work. My TOAD is having some issues so couldn’t confirm the CASE WHEN code. In the meantime I tried the solution provided by Andreas and it jsut worked like MAGIC Below is the code that I used to make it happen with the help of Andreas of course
(
@Prompt('1. Enter begin date:','D','schemaname tablename\objectname',mono,free) = To_Date('01/01/1900', 'mm/dd/yyyy') AND
@Prompt('2. Enter end date:','D','schemaname tablename\objectname',mono,free) = To_Date('12/31/9999', 'mm/dd/yyyy')
)
OR
(
@Select(schemaname tablename\objectname) >= @Prompt('1. Enter begin date:','D','schemaname tablename\objectname',mono,free) AND
@Select(schemaname tablename\objectname) <= @Prompt('2. Enter end date:','D','schemaname tablename\objectname',mono,free)
)
Just for reference of others: It gives me a robust way to select date range in 4 ways: (NULL dates = Date field left blank or not entered for few records NOT through OUTER JOIN)
Option 1: 1/1/1900 to 12/31/9999 – All dates WITH NULL dates
Option 2: 1/1/1900 to 10/10/2004 – All Dates <= 10/12/2004 WITHOUT NULL dates
Option 3: 10/16/2001 to 12/31/9999 – All Dates >= 10/16/2001 WITHOUT NULL dates
Option 4: 10/16/2001 to 10/10/2004 – Dates >= 10/16/2001 to <= 10/10/2004 WITHOUT NULL dates.
Man!!! Andreas and Michael and the whole team YOU ROCK!!! 8)
(BOB member since 2002-08-08)
) 
(BOB member since 2002-06-20)
MAGIC 