6 Replies Latest reply: Sep 6, 2013 12:25 PM by Srini VEERAVALLI RSS

    Evaluate function

    SYedurur

      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

          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

            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

              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

                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

                  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

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

                     

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