BusinessObjects Board

Date Lov problam

building semantic layer
#1

Hi,
i have date object (dd/mm/yyyy HH:ss:mm).i am trying to edit the lovs and i am given bellow query

SELECT DISTINCT
CDW_ASA_COMPOSITE_CAL_DIM.CALENDAR_DATE
FROM
CDW_ASA_COMPOSITE_CAL_DIM
UNION
SELECT ‘*’ FROM DUAL

But its giving error.

Pls help me how to display * value and dates in Lovs.

ramireddy.g (BOB member since 2005-03-24)

@prompt and default for all dates
"All" option in date prompt.
#2

Hi Ramireddy,

It is giving error due to the “mismatch in datatype”.

Are you trying to show the all values using “*”?

If so, you can try following 2 probable solutions:

  1. You may ask your Database team to add a default date value like 01/01/1900 and communicate the users regarding this as a default value. You need to do the required changes in other places replacing “*” with “default date”.
  2. If this date field is not being used for any further calculation, you may advise your database team to change the datatype to any CHARACTER type. In that case, you can append the “*” value in this column.

Please let me know whether it makes sense to you.

adhow (BOB member since 2006-08-08)

#3

Read this topic for having ALL in LOV. But also consider this topic which talks about the necessity of LOVs for Date.

Jansi :india: (BOB member since 2008-05-12)

#4

Hi Adhow ,
Thanks for replay.

i have one question, if we create one Derived table in that we will convert the data type ‘date’ to ‘char’ using to_char() function for date column.
then create new object for date and this new object is used for only LOVs.

this solution will work?

thanks,

ramireddy.g (BOB member since 2005-03-24)

#5

you dont have to create a derived table for this. Al you need to do is create another object of datatype char with the sql as to_char(yourdate, format).
In the LOV of this object you can union a select ‘*’ from dual to display * along with the dates.
You can use the newly created object as the LOV of the date object.

amsmi :india: (BOB member since 2006-09-08)

#6

Jansi :india: (BOB member since 2008-05-12)

#7

Hi Ramireddy,

I agree with amsmi. You can try that rather going for derived table or modification in data base level. But, use of “*” as an ALL values need some other activities also. Please be a bit careful while doing the same; please customize that part in sync with the changes (applying To_Char function) you are doing here.

All the very best!!

adhow (BOB member since 2006-08-08)

#8

You really don’t want to do this though, especially if you have a lot of data.
Unless yo have implemented function-based indexing, using to_char will really hamper performance because it will prevent the use of any index that has been created on the date column (which should be there if it is either a join or a commonly used column in whre clauses).

Please refer to the links that Jansi has suggested rather than pursuing this; I’d hate to think that you told someone you got the to_char idea from Bob when there is a better solution posted.

#9

Thanks everyone for all your help and suggestions

Dee

bobboard (BOB member since 2009-03-04)