Skip to Content
Author's profile photo Feddy George

BODS functions with output

Thought it would be helpful if there is a post about the BODS functions that depicts the right syntax, format and output of the same.

I have tried to cover most of them, hope it is useful.

Also, please provide your rating and likes for the article if you are happy with it…

BODS functions with output:

to_decimal(‘10.235’, ‘.’, ‘,’, 3)                     >> 10.235

to_decimal_ext(10.2365, ‘.’, ‘,’, 3, 4)          >>10.237

encrypt_aes(‘The trick, William Potter, is not minding that it hurts’, ‘patchme’, 192) >>

1YjT1+H17lYbCh5UetT5C4BJQsRo6NKc2HOsVuTQutAuj9pe82akeWyKjF1ba/ZpHdwH7KrWzzkoM8jbg0YlRPQUyF1+6lE

decrypt_aes(‘cn0XanIAHEzgY9QkrLee0jpybOR5iHq2dCQgLYrHEPNqjX9bH9S7r+nW2B3xxp3+qkj+r99ztQMlwwJLwjRJi865KlxBxGM’, ‘patchme’, 192) >> The trick, William Potter, is not minding that it hurts

julian_to_date(2455322)                           >> 2010.05.05 00:00:00

add_months(‘2010.12.31’, 5)                     >> 2011.05.31 00:00:00

concat_date_time(‘2013-12-31′, ’20:11:15’) >> 2013.12.31 00:00:00

date_diff(‘2010.10.10’, ‘2010.10.01’, ‘D’)      >> 9

date_part(‘2010-12-13’, ‘YY’)                     >> 2010

day_in_month(‘2010.05.05’)                      >> 5

day_in_week(‘2010.05.05’)                        >> 3

day_in_year(‘2010.05.05’)                         >> 125

isweekend(‘2010.05.05’)                           >> 0

julian(‘2010.05.05’)                                   >> 2455322

last_date(‘2010.05.05’)                             >> 2010.05.31 00:00:00

month(‘2010.05.05’)                                 >> 5

quarter(‘2010.05.05’)                                >> 2

Sysdate()                                               >> 2013.04.23 06:19:30

systime()                                                >> 1900.01.01 06:19:30

Week_in_month(‘2010.05.16’)                   >> 3

week_in_year(‘2010.05.05’, ‘WW’)             >> 18

year(‘2010.05.05’)                                    >> 2010

ERROR FILE NAME                                >> d:/Program Files (x86)/SAP BusinessObjects/Data Services/log/JSBatch/in00sqp003_bobjds_dev05_/error_04_23_2013_06_19_26_3168__8c1b3c56_dbf7_4230_970b_50de0845c746.txt

MONITOR FILE NAME                            >> d:/Program Files (x86)/SAP BusinessObjects/Data Services/log/JSBatch/in00sqp003_bobjds_dev05_/monitor_04_23_2013_06_19_26_3168__8c1b3c56_dbf7_4230_970b_50de0845c746.txt

MONITOR FILE NAME                            >> d:/Program Files (x86)/SAP BusinessObjects/Data Services/log/JSBatch/in00sqp003_bobjds_dev05_/trace_04_23_2013_06_19_26_3168__8c1b3c56_dbf7_4230_970b_50de0845c746.txt

get_env(‘TMP’)                                           >> C:\Users\SAPSER~1\AppData\Local\Temp

set_env(‘TMP’,’C:\TEMP’) >> get_env(‘TMP’) >> C:\TEMP

abs(-200)                                                  >> 200

ceil(20102.3265489)                                   >> 20103.000000

floor(20130.235654)                                    >> 20130.000000

ln(10)                                                        >> 2.302585

log(1)                                                        >> 0.000000

mod(7, 2)                                                  >> 1

power(2, 3)                                                >> 8.000000

rand()                                                        >> 0.194100

rand_ext(4)                                                >> 0.055463

round(215.23156, 3)                                    >> 215.232000

sqrt(4)                                                       >> 2.000000

trunc(165.216813896, 5)                             >> 165.216810

Current_configuration(‘DC_CCAS0NEDB’)    >> DEV

datastore_field_value(‘DC_CCAS0NEDB’,’server_codepage’) >> utf-8

db_database_name(‘DC_CCAS0NEDB’)      >> ccas0nedb

db_owner(‘DC_CCAS0NEDB’, ‘ALIA’)           >> ‘DBO’

db_type(‘DC_CCAS0NEDB’)                       >> Microsoft_SQL_Server

db_version(‘DC_CCAS0NEDB’)                   >> Microsoft SQL Server 2008

gen_uuid()                                                >> aa9fdec2ac0711e2aa23e36c1b489984

greatest(‘a’,’b’,’c’)                                       >> c

host_name( )                                            >> BODS12

job_name()                                               >> EU_CN_TEST_RUN

job_run_id()                                              >> 133

least(‘a’,’b’,’c’)                                            >> a

repository_name()                                     >> MICROSOFT_SQL_SERVER.IN00SQP003.BOBJDS_DEV

system_user_name()                                 >> SAPServiceDBS

table_attribute(‘DC_CCAS0NEDB.DBO.FD_TEST_21′,’Number_Of_Inserts’) >> 113

$TS = ‘Testing for string functions’

ascii(‘BODS’)                       >> 66

chr(65)                                >> A

double_metaphone($TS)       >> TSTNKFRSTRNKFNKXNS

index($TS,’st’,1)                   >> 3

length(*)                              >> 29

lpad(‘hello’,18,’World’)           >> WorldWorldWorhello

lpad_ext(‘hello’,13,’World’)     >> WorldWorhello

rpad(‘hello’,23,’World’)           >> helloWorldWorldWorldWor

lpad_ext(‘hello’,23,’World’)     >> helloWorldWorldWorldWor

lpad(‘!abcd$’,10,0)                >> 0000!abcd$

init_Cap($TS)                      >>Testing For String Functions

lower($TS)                          >> testing for string functions

upper($TS)                          >> TESTING FOR STRING FUNCTIONS

ltrim(‘XXXYYYZZZ’,’XY’)         >> ZZZ

ltrim(‘XXYXYYXYZZZ’,’YX’)     >> XXYYZZZ

ltrim_blanks(‘    with space’)  >> with space

match_Simple(‘string’,’string’) >> 1

replace_substr(‘ABCDEABCDE’,’ABC’,’abc’) >> abcDEabcDE

replace_substr_ext(‘ABCDEABCDEABCDEABCDE’,’ABC’,’xyz’,2,2) >> ABCDExyzDExyzDEABCDE

soundex($TS)                      >> T235

substr($TS,6,6)                    >> ng fo

word($TS,3)                        >> string

word_ext(‘abc.def.ghi’,2,’.’)    >> def

Thanks & Regards,

Feddy George

Assigned tags

      18 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Asgar Ali MK
      Asgar Ali MK

      Usefull informations...

      But chr(66) is B not A..

      Regards

      Asgar

      Author's profile photo Feddy George
      Feddy George
      Blog Post Author

      Thanks for letting me know... it has been rectified

      Author's profile photo abhi a
      abhi a

      Very useful info... Thanx Feddy.

      Regards,

      Abhi

      Author's profile photo Feddy George
      Feddy George
      Blog Post Author

      Glad to know that it is useful Abhi

      Thanks,
      Feddy

      Author's profile photo Former Member
      Former Member

      Very Useful Information..  Thanks Feddy

      Regards,

      Kris

      Author's profile photo Former Member
      Former Member

      Very Useful information

      Thanks

      Bharath

      Author's profile photo Feddy George
      Feddy George
      Blog Post Author

      Glad to know Bharath, I would appreciate if you could rate the article as well.. 🙂

      Author's profile photo Former Member
      Former Member

      Good One Feddy.....

      Author's profile photo Former Member
      Former Member

      a very helpful Page...Thanks for posting it.

      Author's profile photo Deepak Chodha
      Deepak Chodha

      systime() >> 1900.01.01 06:19:30 is mentioned wrongly.

      It should be HH:MM:SS.

      Author's profile photo Feddy George
      Feddy George
      Blog Post Author

      But the post is actually the trace log of the job in which the functions have been used(I din type it 😛 ).

      Thats what the systime() ouptput when used in a print function

      I am using BODS 3.2, not sure about higher versions.

      Thanks for the reply anyway Deepak

      Author's profile photo Deepak Chodha
      Deepak Chodha

      Good to know. It will be great if you can mention the version for which trace holds correct for more our clarity. Since we are currently on DS4.2.

      regards,Deepak

      Author's profile photo Former Member
      Former Member

      How to use db_database_name on Oracle database. As db_database_name does not work for Oracle and Sybase databases.

      Author's profile photo Former Member
      Former Member

      I wish there was a way to get the table name from a data flow...

      Author's profile photo Feddy George
      Feddy George
      Blog Post Author
      Author's profile photo Former Member
      Former Member

      Very Usefull Feddy

      Author's profile photo Former Member
      Former Member

      Thank you for sharing useful information in one window..:D 

      Author's profile photo Former Member
      Former Member

      is there a list of all environment variable?