Re: SQLSTATE 42703 in SQLRPGLE program

Hi Kerwin,

As Brian already pointed out, there seems to be a : (colon) missing in front of SqlStmt1 at the PREPARE. 

If this is OK in the original source, it might be, that SqlStmt1 is shorter than SELECT_ActPlan - and since some time, you don't need that extra variable - you can prepare directly from a constant.

And - never quote a ? marker - SQL handles every datatype correctly. 

HTH
Daniel



> Am 19.01.2026 um 20:48 schrieb K Crawford <[email protected]>:
> 
> Below is part of my SQLRPGLE program.
> I am getting SQLSTATE 42703 on the PREPARE statement.
> What could it be?
> The two ? (dynamic selections) SSAN is numeric and PLANID is char.  I have
> tried putting quotes around the ? for PLANID, but did not work.
> I have copied this SQL into ACS-RSS and it runs fine.
> 
>      Dcl-C SELECT_ActPlan CONST(' Select e.cobret, +
>      e.qbnum, e.cobrarnum, e.ssan, +
>      p.cvglevl, p.cvgtype, p.status, p.planid, +
>      p.planstrt, p.evntdate3, +
>      p.fdoc, p.ldoc, +
>      p.cbrstrt, p.cbrstop, +
>      p.termdate +
>      from lib1/cobraE e +
>      join lib1/cobraP p +
>      on e.qbnum = p.idnum +
>      where +
>          (e.ssan = ? ) +
>      and (p.planid = ? ) +
>      and (p.status in ( ''E '', ''ED'', ''E4'', ''TE'')) +
>      and (to_char(current date,''YYYYMMDD'') between p.PlanStrt +
>       and (case when p.EvntDAte3 = 0  then 20991231 else p.EvntDate3 end)
> +
>          ) +
>      and (to_char(current date,''YYYYMMDD'') between  p.fdoc and p.ldoc) +
>      AND (to_char(current date,''YYYYMMDD'') between  +
>           p.cbrstrt and p.cbrstop) +
>      AND ((to_char(current date,''YYYYMMDD'') between +
>            p.cbrstrt and p.TERMDATE) +
>           or (p.termdate = 0))');
> 
> Dcl-DS ActPlanDS Qualified;
>        cobret char(1);
>        qbnum zoned(7:0);
>        cobrarnum zoned(7:0);
>        ssan zoned(9:0);
>        cvglevl Char(4);
>        cvgtype Char(9);
>        status Char(2);
>        planid Char(40);
>        planstrt zoned(8:0);
>        planend zoned(8:0);
>        fdoc zoned(8:0);
>        ldoc zoned(8:0);
>        cbrstrt zoned(8:0);
>        cbrstop zoned(8:0);
>        termdate zoned(8:0);
>      End-DS;
> 
>        SqlStmt1 = SELECT_ActPlan;
>        // Prepare the statement
>        exec sql
>          prepare dynStmt1 from SqlStmt1;
>        // Declare a cursor for the prepared statement
>        Exec SQL
>          DECLARE dynCursor1 CURSOR FOR dynStmt1;
>        // Open the cursor with parameters
>        Exec SQL
>          OPEN dynCursor1 USING :w_ssan
>                               ,:w_planid;
>        // Fetch the data into your data structure
>        Exec SQL
>          FETCH dynCursor1
>            INTO :ActPlanDS;
>        // Check if successful
>        If SQLSTATE = '00000';
>          // Process your data
> 
> 
> 
> --
> Kerwin Crawford
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.