In beas Script it's simple to work with SQL
More about the Database, fields, procedures and so on see Database
Use the keyword sqlca for all sql commands
setvar=ls_itemcode=RM
sqlca.select "ItemName","OnHand" from "OITM" where "ItemCode"=<ls_itemcode,dbstring>
This return the result in sqlca.result.[number]
messagebox=ItemName: <sqlca.result.1>, Stock: <sqlca.result.2>
Alternative you can work with the keyword Into
sqlca.select "ItemName","OnHand" into ls_itemname,lc_stock from "OITM" where "ItemCode"=<ls_itemcode,dbstring>
messagebox=ItemName: <ls_itemname>, Stock: <lc_stock>
Write all SQL commands always in HANA Syntax.
More see internal SQL Runtime Converter
Good scripts checking always the return results for errors. See sqlca error handling
If you need more then one result line, then you can use the datastore object
instance datastore d
dec lc_total
// execite select command
d.select "OnHand" from "OITM" where "OnHand" > 0
// for next loop throw the datastore
for ll_row=1 to <d.rowcount>
d.setrow=<ll_row>
// Note: in the result all field names lower case.
// Wrong: d.OnHand, correct: d.onhand
// we use decimal values, don't forget to convert to international format with <..,num(6)>
lc_total=<lc_total> + <d.onhand,num(6)>
next
// Display result
messagebox=total stock all items: <lc_total>
// destroy datastore (important)
destroy d
See