×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Cursor Questions

thread404-339220

Cursor Questions

Cursor Questions

(OP)
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 = "REPORT PREPARE failed. SQLCODE=",
                    SQLCA.SQLCODE, " ISAM CODE=", 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,"Exit: rpt_run")
                RETURNING m_ignore
        RETURN
    END IF

    DECLARE c_primary CURSOR FOR f_psel_exec
    IF SQLCA.SQLCODE <> 0 THEN
        LET m_msg = "REPORT DECLARE failed. SQLCODE=",
                    SQLCA.SQLCODE, " ISAM CODE=", 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,"Exit: rpt_run")
                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 = "OPEN c_primary failed. SQLCODE=",
                    SQLCA.SQLCODE, " ISAM CODE=", 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,"Exit: rpt_run")
                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 = "Main FOREACH failed. SQLCODE=",
                        SQLCA.SQLCODE, " ISAM CODE=", 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,"Exit: rpt_run")
                    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)

RE: Cursor Questions

{
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 = "systables"
and colname in ("owner", "colname")

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

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

You need to prepare your "IN" 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 = "(\"owner\", \"colname\")"
let scratch = " select b.* from systables a, syscolumns b ",
"where a.tabid = b.tabid and tabname = ? ",
"and colname in ", return_value clipped

prepare sel_id from scratch
declare sys_ptr cursor for sel_id

let varname = "systables"
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close