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