Forum Stats

  • 3,853,528 Users
  • 2,264,231 Discussions
  • 7,905,381 Comments

Discussions

Calling Stored Procedure for Date Conversion

Hi All,

I have a small problem that probably is a simple solution but i am new to OBI and can't seem to figure it out.

All the dates in my DB are stored in binary. In order to 'see' the correct date, i need to do a call to "dbo.udf_pdmtime_to_datetime(order_date)" which converts and returns the field in a date time format.

Now my questions is....where do i do this call to the procedure? In the Administration Tool? In Answers?

How do i go about doing this?

Thanks

Answers

  • Hi...

    I don't know about the function you have written but...
    Procedure will be created like following
    Take an example: i want to create a procedure to take only first letter from the city of City column.
    For this...
    1). Create a physical table manually under the schema folder.
    2). Give the name and select Table Type as "Stored Proc".
    3). In "Default Initialization String" field, you should write the query saying "select substr(city,1,1) as Col1 from Regions". In query Col1 is the alias name i have given for the new column. (Click OK)
    4). So, now this column should be part of the table newly created. So create a physical column under this newly created table with "Col1" name.
    5). Double-click the column and specify the data type of that particular column. (This should be done otherwise you can't move it to BMM layer)
    6). Now you can add this table to BMM layer by performing necessary joins w/o any errors or warnings. (This would be depending on your business requirement, for this some times you need to add keys also to the procedure to perform the joins and all)
    7). By adding it to the presentation layer, in answers you can watch the results..

    NOTE:
    1). While writing the function names in Stored procedures, DB you are using is important. Because, functions will vary between the DB's.
    2). Column name of newly created should match with the name you specified in the procedure as alias.
    3). If you are retrieving 'n' no. of columns from the procedure then you need to create 'n' no. of columns manually.
    4). For physical columns created manually, you need to specify the data type according to the type of data it's retrieving.

    It may helpful to you in achieving your requirement. If not sorry... but i am sure it's helpful to you...

    mark as helpful if it's helpful else mark as correct if correct ;)

    Thanks & Regards
    Kishore Guggilla
  • Madan Thota
    Madan Thota Member Posts: 839
    In Business Model layer with that date column, Use Evaluate function to call the stored procedure. This is the easy way to convert every row date binary value to a valid date.

    thanks
  • empyre
    empyre Member Posts: 89
    Would this also be applicable to database functions?
  • Hi..

    The purpose of evaluate is to use DB functions only...

    Khanks & Regards
    Kishore Guggilla
This discussion has been closed.