This discussion is archived
6 Replies Latest reply: Sep 6, 2013 10:25 AM by Srini VEERAVALLI RSS

Evaluate function

SYedurur Newbie
Currently Being Moderated

How can we achieve this below using  EVALUATE function in obiee. I can use cast to do this but i need to use EVALUATE for my purpose.Can someone please throw light on this.

 

to_char(date_column,'MM')

  • 1. Re: Evaluate function
    Srini VEERAVALLI Guru
    Currently Being Moderated

    This can be done in BI itself. Please do not degrade BI.

    month(date_column)

    based on the return value go for concat to prefix 0

     

    if helps mark

  • 2. Re: Evaluate function
    SrikanthGH Newbie
    Currently Being Moderated

    Not on the suggestion, but I am more awe on the way Srini emphasizing how awesome is BI

     

    Thanks for the effort to solve our issues

  • 3. Re: Evaluate function
    SYedurur Newbie
    Currently Being Moderated

    Srini,

     

    Here is my issue

     


    I want to implement below function in obiee.It  takes the month from the column and appends the current year .so basically if the column has the value 01/12/12.It becomes 01/12/13.P_year is a current year variable.I am having issues in writing the above code in obiee.I am using the below in obiee which is giving the below error.


    SQL FUNCTION:  to_date(to_char(Date_Column)||'-'||:current_year,'MM-YY') 


    OBIEE:CAST(CAST(MONTH("date_column") AS CHAR) ||'-'||CAST(VALUEOF("current") AS CHAR)as date)


    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 1843, message: ORA-01843: not a valid month at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000)


    Please help were i am doing wrong.This is the reaon i want to do this using EVALUATE function.

     


  • 4. Re: Evaluate function
    Srini VEERAVALLI Guru
    Currently Being Moderated

    Try this

    replace(cast(Time.Date as char),cast(year(Time.Date) as char),cast(year(current_date)  as char))

     

    ~ http://cool-bi.com

  • 5. Re: Evaluate function
    SYedurur Newbie
    Currently Being Moderated

    I tried using your Syntax,but somehow the year is not replaced.Below is  the example i took.When i use the cast function it is not replacing. Please correct me if i am doing wrong.

     

    replace(cast('04/01/2006' as char),cast('2006' as char),cast('2008'  as char))

  • 6. Re: Evaluate function
    Srini VEERAVALLI Guru
    Currently Being Moderated

    That works for me. I hope you are doing in Answers.

     

    Btw: There are many ways to get what you need, just tweak

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points