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!

Register Log in
  • 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!

Cursor Questions

Status
Not open for further replies.
phxcoyotes097

phxcoyotes097

Programmer
Feb 19, 2003
16
US
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)

 
Sort by date Sort by votes
{
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

jrichemont
  • Locked
  • Question
Cursor hangs on 64 bit linux. Works for 32
Replies
0
Views
39
jrichemont
jrichemont
aft5425
  • Locked
  • Question
Declaring cursors in Informix 4gl
Replies
1
Views
82
vpshriyan
vpshriyan
JGKWORK
  • Locked
  • Question
Newbie - Free SQL builder and SQL dialect questions?
2
Replies
21
Views
189
JGKWORK
JGKWORK
gollumullog
  • Locked
  • Question
Informix ODBC Setup Questions
Replies
3
Views
102
mberni
mberni
WiccaChic
  • Locked
  • Question
wait hint with cursor?
Replies
1
Views
21
vpshriyan
vpshriyan

Part and Inventory Search

Sponsor

Back
Top