Skip to Content
avatar image
Former Member

Promt Performance Issue

Hi BO Experts,

DIM_DATE.CAL_DATE is a database field (defined as Timestamp) which has a values

3/14/2003 12:00:00

3/15/2003 12:00:00, etc...

Below is the syntax for predefined condition:

DIM_DATE.CAL_DATE  BETWEEN  @Prompt('1.Select Start Date:','D',,mono,free) AND @Prompt('2.Select End Date:','D',,mono,free)

It throws me an error saying that Timestamp (DIM_DATE.CAL_DATE) cannot be compared with DATE ("D" (Calendar from WEBI)).

Many of us will suggest to do Trunc(), CAST() on DIM_DATE.CAL_DATE to remove Time part from DIM_DATE.CAL_DATE.

Then the code will become

CAST(DIM_DATE.CAL_DATE AS DATE)  BETWEEN  @Prompt('1.Select Start Date:','D',,mono,free) AND @Prompt('2.Select End Date:','D',,mono,free)

As per my knowledge it not a best practice to have SQL Functions on the left handside of the Operator (Between, Inlist, Equal,etc..).

Is there any other alternative?

-Thanks,

DEEPU

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Nov 19, 2010 at 04:08 PM

    Deepu,

    You are correct about the performance hit of a function nullifying an indexed column.

    Your best bet is to add an extra column as datetime format in your DIM_DATE table.

    Either that or you can use a deterministic function and create an index on it. Details are in Books On Line. This page may be of use if this is the route you take - http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/670fd1c5-5990-4ab4-96d6-fe72a33b7394/

    Regards,

    Mark

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 17, 2010 at 07:32 AM

    Hi,

    Try with SubString() and it may help you.

    Cheers,

    Suresh Aluri.

    Add comment
    10|10000 characters needed characters exceeded