SAP BODS SCRIPT - COUNT of Records from 2 HANA Tables and Calculate Difference

[RR]

I am trying to calculate difference between the count of records using the below script and receiving warning during validation. But No issue with output

$source_count = sql('DATASTORE','select count(*) from Source Table');<br>$filter1_count = sql('DATASTORE','select count(*) from Target Table' || 'where colmn1 in (\'0,00\',\'0\') ');<br>$diff_count = $source_count - $filter1_count ;<br>print( '# ESTIMATED TOTAL RECORDS AFTER CLEANING :' ||$diff_count);

BODS Warning

[Script:Script_Count] Warning: Expression <($$source_count - $filter1_count)> of type <REAL> will be converted to type <VARCHAR>. (BODI-1110432)

Tried adding cast($diff_count,'varchar(10)'); before printing the value. but no use of it. need suggestions to avoid warning.

Note: All are global variables - declared as VARCHAR - 10

[jmuiruri · Accepted solution]

Greetings grr,

The warning you are getting is as a result of your attempt to perform arithmetic operation on strings.

[Script:Script_Count] Warning: Expression <($$source_count - $filter1_count)> of type <REAL> will be converted to type <VARCHAR>. (BODI-1110432)

From the error message you can see the problematic expression is subtraction operation you are performing on VARCHAR data. To avoid warning message you need to cast the $source_count and $filter1_count to integer before performing the arithmetic operation.

$source_count = sql('BikeStores','select count(*) from sales.orders');

$filter1_count = sql('BikeStores','select count(*) from sales.stores ');

$diff_count =cast((cast( $source_count, 'INT') - cast( $filter1_count , 'INT')), 'varchar(255)');
print( '# ESTIMATED TOTAL RECORDS AFTER CLEANING :' ||$diff_count);

Bottom line, it would have been easier to work with global variables of type integers.

Best Regards,

Joseph

[RR · Accepted solution]

Thank you for the simple explanation and solution.