Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Modify Datawindow SQL

Status
Not open for further replies.

ultimategc3

Programmer
Oct 18, 2010
14
EG
Dear all,

I've tried to modify a datawindow sql with number array argument, it always return "Incorrect syntax near '('" . I've performed the next statement:

Long ll_cd2[]
String ls_sql, ls_ext
ls_sql = dw_1.describle('datawindow.table.select')
// this datawindows contains number array "slo_cd2[]" argument defined in the datawindow painter with syntax as follows:
// SELECT order_line.slo_cd2,
// order_line.slo_prc,
// order_line.slo_cur,
// FROM order_line,
// WHERE order_line.slo_cd2 in ( :slo_cd2 )
// dw_1.retrieve(ll_cd2)
// this statement works fine without modifying
ls_ext = ' and ' + ' order_line,slo_prc > ' + '"' + String(ddlb_1.text) + '"'
ls_sql = ls_sql + ls_ext
ls_sql = dw_1.modify('datawindow.table.select = "' + ls_sql + '"' )
dw_1.settransobject(sqlca)
dw_1.retrieve(ll_cd2)
// the new sql appears like:
// SELECT order_line.slo_cd2,
// order_line.slo_prc,
// order_line.slo_cur,
// FROM order_line,
// WHERE order_line.slo_cd2 in ( :slo_cd2 )
// AND order_line.slo_prc > 100
// returns incorrect syntax near '(' line 5

// how to modify a datawindow with array arguments

Thank you...
 
Dear Matt,

The problem is to include arrays argument inside the sql statement after modify:

Long ll_cd2[]
String ls_sql, ls_ext
ls_sql = dw_1.describle('datawindow.table.select')
// this datawindows contains number array "slo_cd2[]" argument defined in the datawindow painter with syntax as follows:
// SELECT order_line.slo_cd2,
// order_line.slo_prc,
// order_line.slo_cur
// FROM order_line
// WHERE order_line.slo_cd2 in ( :slo_cd2 )
// dw_1.retrieve(ll_cd2)
// this statement works fine without modifying
ls_ext = ' and ' + ' order_line,slo_prc > ' + '"' + String(ddlb_1.text) + '"'
ls_sql = ls_sql + ls_ext
ls_sql = dw_1.modify('datawindow.table.select = "' + ls_sql + '"' )
dw_1.settransobject(sqlca)
dw_1.retrieve(ll_cd2)
// the new sql appears like:
// SELECT order_line.slo_cd2,
// order_line.slo_prc,
// order_line.slo_cur
// FROM order_line
// WHERE order_line.slo_cd2 in ( :slo_cd2 )
// AND order_line.slo_prc > 100
// returns incorrect syntax near '(' line 5

// how to modify a datawindow with array arguments
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top