Embedded SQL SQLSTT 42703 and 42618 errors...

Great news, found out yesterday that my contract is extended to the end of May. The company has decided to keep me on to help with implementation of the MIDRetail.

I ran into a problem with my daily sales XML output. I added date selection as a host variable to the Where clause in my SQL Select statements. I ran the program and no output, WTF!

I ran the program in debug and found SQLSTT is throwing an error 42703. After browsing Google I determined that the host variable is the Where clause is suspect. I used sub-procedures to make the SQL as generic as possible. I have a couple of SQL statements in the code and am changing the statements by evaluating a temporary field SQLV as needed.

* Standalone Variables
    d sqlV            s          32000a   varying                              varying as needed
    d sql1            s          32000a   varying                              Store/style/color
    d sql2            s          32000a   varying                              Update sales type
    d sql3            s          32000a   varying                              Store/style/color/sz   


begsr CrtSQLSR;
        // Daily Sales summerized by store, style, color = MAINDS
        sql1 = ('Select stat, vstr, vdte, sum(vqty), vcls, vven, +
             vsty, vclr, vstp +
             from MidSalesPF +
             where vdte = :yesterday and 1 = 1 +
             group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr +
             order by vstr, vdte, vdte, vstp, vcls, vven, vsty, vclr' );

        // Daily Sales summerized by store, style, color, size = MAIN1DS
        sql3 = ('Select stat, vstr, vdte, sum(vqty), vcls, vven, +
             vsty, vclr, vsiz, vstp +
             from MidSalesPF +
             where vdte = :yesterday and 1 = 1 +
             group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr, +
             vsiz +
             order by vstr, vdte, vstp, vcls, vven, vsty, vclr, vsiz' );

       endsr;                                                      
-------------------------------------------------
begsr Day_SscsizSR;

      clear sqlV; // Clear temp field
      sqlV = sql3; // SQL statement to run
      first = 'Y';                              // First pass flag
      declare();                                // SQL declare
      openCursor();                             // SQL open cursor
      Exec SQL fetch next from mainCursor       //Load data Structure
            into :main1ds;                 
blah,blah,blah.......

------------------------------------------------

p declare         b
    d declare         pi

    c/exec sql
    c+ declare mainCursor cursor
    c+     for mainStatement
    c/end-exec
    c/exec sql
    c+ prepare mainStatement
    c+    from :sqlV My problem child
    c/end-exec

    p declare         e
     *-------------------------------------------------------------------
    p openCursor      b
    d openCursor      pi

    c/exec sql
    c+ open mainCursor
    c/end-exec

    p openCursor      e
     *-------------------------------------------------------------------
    p closeCursor     b
    d closeCursor     pi

    c/exec sql
    c+ close mainCursor
    c/end-exec

    p closeCursor     e             
------------------------------------------------------------------------------------------------------

From what I can tell SQL does not like a variable inside a variable. The Where clause :yesterday inside :SQLV.

So I took it all apart and got rid of the sub-procedures.

begsr Day_SscsizSR;

      Exec SQL declare mainCursor2 cursor
         for Select stat, vstr, vdte, sum(vqty), vcls, vven, +
             vsty, vclr, vsiz, vstp
             from MidSalesPF
             where vdte = :yesterday
             group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr, vsiz
             order by vstr, vdte, vdte, vstp, vcls, vven, vsty, vclr, vsiz;

      Exec SQL open mainCursor2;                             // SQL open cursor

      Exec SQL fetch next from mainCursor2       //Load data Structure
            into :main1ds;                        

blah,blah,blah........

So I am on the hunt for why my XML is no longer being written. I must have cut something out by mistake while getting rid of the sub-procedures.

~Richard