How to store a value in variable from SQLQuery using Scripts

[Former Member]

Hi Experts,

I need to fetch the value from database table and store it in variable to do further process.

Using Scripts and SQL query option in SAP BODS.

Script used:

$L_Output = sql('Datastore_Name','select sno from xx_table where c_xx = \'21-JUN-16\'');

This results NULL as output. Can you please suggest.

Thanks,

Pradheep S

[former_member187605 · Accepted solution]

Data types?!

Are $L_Output and sno of same type?

Is c_xx of type varchar? And is the date stored in exactly that format?

[Former Member · Accepted solution]

Yes, $L_Output and sno are of same data type - INT.

c_xx is date field, yes date stored in database exactly in same format.

The query returns output in database SQL developer.

Thanks,

[former_member187605 · Accepted solution]

Two observations:

1/. Result of SQL function is varchar. Something may go wrong in the implicit conversion from varchar result to int variable. Try print(sql('Datastore_Name','select sno from xx_table where c_xx = \'21-JUN-16\''));

2/. $L_Output is a local variable and you're referring to it from within a different scope. Try with a global variable.