Cursor Questions

[phxcoyotes097]

I'm having a problem with a "cursor". An sql is being set into a variable, then that is being set into a cursor. I want to be able to display the cursor with a "DISPLAY", but the program is given me errors when I try to do that. Do you know how to display a cursor? I have attached the section of code in question.
What I want to get to is to see the sql stmt after all of the variables have been inserted into the stmt.

LET f_psel_stmt =
"SELECT iplas.loc_id, iplas.lcus_id, ",
"iplas.prod_id, iprod.description, ",
"iinvd.lot_no, iinvd.cde_dt, iplas.prdd_id, ",
"iprdd.prod_sz, iprdd.unit_ship_cse, iprdd.cse_hgt, ",
"iprdd.prod_hgt, iprdd.pal_hgt, iprdd.nest_hgt, iprdd.stor_ti, ",
"iprdd.stor_hi, SUM(iinvd.prod_qty/iprdd.unit_ship_cse), ",
"iloc.llts_id, iloc.lhnd_id, ",
"iloc.lcat_id, swhse.wtyp_id ",
"FROM iprod, iprdd, iinvd, iplas, iloc, swhse ",
"WHERE iloc.loc_id BETWEEN ? AND ? ",
f_lcat_where CLIPPED,
"AND iloc.dc_id = ? ",
"AND iloc.whse_id = ? ",
"AND iplas.prod_id BETWEEN ? AND ? ",
"AND iprod.commodity_type IN (?) ",
"AND iplas.plas_id = iinvd.plas_id ",
"AND iloc.dc_id = iplas.dc_id ",
"AND iloc.whse_id = iplas.whse_id ",
"AND iloc.loc_id = iplas.loc_id ",
"AND iprdd.dc_id = iplas.dc_id ",
"AND iprdd.prod_id = iplas.prod_id ",
"AND iprdd.prdd_id = iplas.prdd_id ",
"AND iprod.dc_id = iprdd.dc_id ",
"AND iprod.prod_id = iprdd.prod_id ",
"AND swhse.dc_id = iplas.dc_id ",
"AND swhse.whse_id = iplas.whse_id ",
"GROUP BY ",
"iplas.loc_id, iinvd.lot_no, iplas.lcus_id, ",
"iplas.prod_id, iprod.description, iplas.prdd_id, ",
"iprdd.prod_sz, iprdd.unit_ship_cse, iprdd.cse_hgt, ",
"iprdd.prod_hgt, iprdd.pal_hgt, iprdd.nest_hgt, iprdd.stor_ti, ",
"iprdd.stor_hi, iloc.llts_id, iloc.lhnd_id, iloc.lcat_id, swhse.whse
"iprdd.stor_hi, iloc.llts_id, iloc.lhnd_id, iloc.lcat_id, swhse.whse
_id, swhse.wtyp_id, iinvd.cde_dt "

IF m_order_by = 1
THEN
LET f_psel_stmt = f_psel_stmt CLIPPED, " ORDER BY 1, 3"
LET m_Order_Desc = m_By_Loc
ELSE
LET f_psel_stmt = f_psel_stmt CLIPPED, " ORDER BY 3, 1"
LET m_Order_Desc = m_By_Prod
END IF

PREPARE f_psel_exec FROM f_psel_stmt
IF SQLCA.SQLCODE <> 0 THEN
LET m_msg = &quot;REPORT PREPARE failed. SQLCODE=&quot;,
SQLCA.SQLCODE, &quot; ISAM CODE=&quot;, SQLCA.SQLERRD[2]
CALL sh_elog_4gl(m_module_name,3,m_msg)
RETURNING m_ignore
CALL sh_elog_4gl(m_module_name,3,f_psel_stmt)
RETURNING m_ignore
CALL sh_mlog_4gl(3,m_module_name,4,&quot;Exit: rpt_run&quot
RETURNING m_ignore
RETURN
END IF

DECLARE c_primary CURSOR FOR f_psel_exec
IF SQLCA.SQLCODE <> 0 THEN
LET m_msg = &quot;REPORT DECLARE failed. SQLCODE=&quot;,
SQLCA.SQLCODE, &quot; ISAM CODE=&quot;, SQLCA.SQLERRD[2]
CALL sh_elog_4gl(m_module_name,3,m_msg)
RETURNING m_ignore
CALL sh_mlog_4gl(3,m_module_name,4,&quot;Exit: rpt_run&quot
RETURNING m_ignore
RETURN
END IF

START REPORT prod_loc TO m_file_name

OPEN c_primary USING m_bloc_id, m_eloc_id, m_dc_id, m_whse_id,
m_bprod_id, m_eprod_id, m_prodclass_id

IF SQLCA.SQLCODE < 0 THEN
LET m_msg = &quot;OPEN c_primary failed. SQLCODE=&quot;,
SQLCA.SQLCODE, &quot; ISAM CODE=&quot;, SQLCA.SQLERRD[2]
CALL sh_elog_4gl(m_module_name,6,m_msg)
RETURNING m_ignore
CALL sh_mlog_4gl(3,m_module_name,7,&quot;Exit: rpt_run&quot
RETURNING m_ignore
RETURN
END IF

LET f_count = 0
WHILE TRUE

FETCH c_primary INTO f_header.*
IF SQLCA.SQLCODE < 0 THEN
LET m_msg = &quot;Main FOREACH failed. SQLCODE=&quot;,
SQLCA.SQLCODE, &quot; ISAM CODE=&quot;, SQLCA.SQLERRD[2]
CALL sh_elog_4gl(m_module_name,6,m_msg)
RETURNING m_ignore
CALL sh_mlog_4gl(3,m_module_name,7,&quot;Exit: rpt_run&quot
RETURNING m_ignore
RETURN
END IF

IF SQLCA.SQLCODE = 100
THEN
IF f_count = 0
THEN
LET m_printed = FALSE
END IF
EXIT WHILE
END IF

LET f_count = f_count + 1

LET f_loaded_hgt = calc_height(m_meas_sys, f_header.lhnd_id,
f_header.cse_hgt, f_header.pal_hgt, f_header.stor_hi,
f_header.prod_hgt, f_header.nest_hgt)

 

[olded]

{
ken:

You can't do this:

iprod.commodity_type IN (?)

consider this select example:

select b.* from systables a, syscolumns b
where a.tabid = b.tabid and tabname = &quot;systables&quot;
and colname in (&quot;owner&quot;, &quot;colname&quot

Unfortunately, you can't treat the &quot;in&quot; keyword as a host variable.
this won't work:

let scratch = &quot; select b.* from systables a, syscolumns b &quot;,
&quot;where a.tabid = b.tabid and tabname = ? &quot;,
&quot;and colname in (?)&quot;

You need to prepare your &quot;IN&quot; clause first; make it part of the select
string, and, finally, prepare, and declare it. See the example below.

}
database testdb

main
define
scratch char(180),
sysrec record like syscolumns.*,
varname char(15),
return_value char(80)

let return_value = &quot;(\&quot;owner\&quot;, \&quot;colname\&quot&quot;
let scratch = &quot; select b.* from systables a, syscolumns b &quot;,
&quot;where a.tabid = b.tabid and tabname = ? &quot;,
&quot;and colname in &quot;, return_value clipped

prepare sel_id from scratch
declare sys_ptr cursor for sel_id

let varname = &quot;systables&quot;
open sys_ptr using varname
while true
fetch sys_ptr into sysrec.*
if sqlca.sqlcode != 0
then
exit while
end if
end while

end main