Re: SQL column size in CLLE

[Rob Berendt <robertowenberendt@xxxxxxxxx>]

Multiple ways to skin this cat.
One, learn how to use variable length character columns in CLLE.  Easier
said than done.
Two, 'cast' the variable length columns to character.
SELECT *char*(JOURNAL_RECEIVER_LIBRARY, *10*) as RCVLIB
                   ,*char*(JOURNAL_RECEIVER_NAME, *10*)    as RCVNAM
Three, stop using CL and use a stored procedure to process stuff like this
(create, followed by execution)
CREATE PROCEDURE Change_IFS_Owner
         (IN CURRENT_OWNER *VARCHAR*(*10*),
          IN NEW_OWNER *VARCHAR*(*10*),
          IN START_PATH *VARCHAR*(*50*) )
         LANGUAGE SQL MODIFIES SQL DATA
        SET OPTION DATFMT = *ISO
         P1: BEGIN
          DECLARE WORK_PATH_NAME *VARCHAR*(*500*);
          DECLARE COMMAND *CHAR*(*500*);
*-- Ensure this is large enough.*          DECLARE END_TABLE *INT* DEFAULT
*0*;
          DECLARE C1 CURSOR FOR
            select path_name

*--  'CHGOWN OBJ(''' concat path_name concat ''') NEWOWN(' concat
new_owner concat ')'*

*--, QCMDEXC('CHGOWN OBJ(''' concat path_name concat ''') NEWOWN('
concat new_owner concat ')')*
            FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME *=>*
 START_PATH,
                                                    SUBTREE_DIRECTORIES *=>*
 'YES'))
            WHERE object_owner=CURRENT_OWNER
            and path_name not like '%' *concat* x'7D' *concat* '%';

          DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET END_TABLE = *1*;

*--  DECLARE EXIT HANDLER FqsysOR SQLEXCEPTION*
*--    SET DEPT_SALARY = NULL;*          OPEN C1;
          FETCH C1 INTO WORK_PATH_NAME;
          WHILE END_TABLE = *0* DO
             SET COMMAND = 'CHGOWN OBJ(''' *concat* work_path_name *concat*
''') NEWOWN(' *concat* new_owner *concat* ')';
             CALL QSYS2.QCMDEXC(COMMAND);
             FETCH C1 INTO WORK_PATH_NAME;
           END WHILE;
           CLOSE C1;
         END P1
;
CALL ROB.Change_IFS_Owner('JREINKIN', 'TRACIL', '/Banking');


On Wed, May 28, 2025 at 2:19 PM Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>
wrote:

> I am running this SQL in a CLLE program:
>
>  Runsql ('CREATE TABLE QTEMP/LISTJRN AS (
>              SELECT JOURNAL_RECEIVER_LIBRARY as RCVLIB
>                    ,JOURNAL_RECEIVER_NAME    as RCVNAM
>                    ,DETACH_TIMESTAMP         as TIMESTMP
>       FROM QSYS2.JOURNAL_RECEIVER_INFO
>       WHERE DETACH_TIMESTAMP IS NOT NULL
>        AND SUBSTR(JOURNAL_RECEIVER_LIBRARY, 1,1) <> ''Q''
>        AND DETACH_TIMESTAMP < CURRENT_TIMESTAMP - 14 DAYS
>     ORDER BY DETACH_TIMESTAMP)
>     WITH DATA')
>       COMMIT(*NONE)
>
> In SQL runscrips, the SQL works great, until I go to use the columns
> RCVLIB and RCVNAM.  The SQL produces a file with varchar output which the
> CLLE does not like.
>
> How to I put a size on the two columns (ideally Char 10) so the CLLE
> compiler won’t complain?
> --
> Jim Oberholtzer
> Agile Technology Architects
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.