Re: SQL and CLLE

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Steve

The usual approach recommended by IBM is to use RUNSQL (IBM's, not the 
one in the excellent articles Mark pointed to) to execute a CREATE TABLE 
filled with the results of the SELECT statement. You will have declared 
the file with DCLF, so then you can read the results.

So that's just another way to try, right?

Cheers
Vern

On 3/9/2021 9:39 AM, Steve M via MIDRANGE-L wrote:
> Certainly, Rob, you may ask anything.  😊
>
>   
>
> We are creating a data queue, based upon the layout of the table, and in the interest of minimizing hard-coding of values, should the table ever be altered in the future and the data queue need to be rebuilt to support the new size, I was trying to have the CLLE with the CRTDTAQ command retrieve the current size of the table and use that for the size/length of the data queue (versus hard-coding the size in the CLLE and trusting a maintenance programmer in the future is attentive enough to update that value should the table layout change).
>
>   
>
> Maybe I am trying to be "too soft coding" at this point and should just hard-code the size in the CRTDTAQ command and let them worry about it down the road, but it was at least worthy of a question - in my little mind.
>
>   
>
> Steve
>
>   
>
>   
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
> Sent: Tuesday, March 9, 2021 09:31
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: RE: SQL and CLLE
>
>   
>
> May I ask why you would need row length?  Is it so you can do something like this?
>
> SELECT SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, system_table_member,
>
>      AVGROWSIZE, NUMBER_DELETED_ROWS,
>
>      AVGROWSIZE * NUMBER_DELETED_ROWS as Deleted_Space
>
>      FROM QSYS2.SYSPARTITIONSTAT
>
>      WHERE SYSTEM_TABLE_SCHEMA = 'ROUTINES';
>
>   
>
> Rob Berendt
>
> --
>
> IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to:  7310 Innovation Blvd, Suite 104
>
>            Ft. Wayne, IN 46818
>
> Ship to:  7310 Innovation Blvd, Dock 9C
>
>            Ft. Wayne, IN 46818
>
>   <http://www.dekko.com> http://www.dekko.com
>
>   
>
>   
>
> -----Original Message-----
>
> From: MIDRANGE-L < <mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx> midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Steve M via MIDRANGE-L
>
> Sent: Tuesday, March 9, 2021 10:14 AM
>
> To: 'Midrange Systems Technical Discussion' < <mailto:midrange-l@xxxxxxxxxxxxxxxxxx> midrange-l@xxxxxxxxxxxxxxxxxx>
>
> Cc: Steve M < <mailto:txpenguin1@xxxxxxxxxxx> txpenguin1@xxxxxxxxxxx>
>
> Subject: SQL and CLLE
>
>   
>
> CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
>
>   
>
>   
>
> I guessed at which forum upon which to post - if this is not correct, please
>
> let me know and I shall repost.
>
>   
>
> I know we can use RUNSQL within a CLLE to execute, typically, an Insert or
>
> Delete or such.  But I would like to receive a value back from my SQL.
>
>   
>
> Here is what I wish to execute:  Select row_length from qsys2.systables
>
> where table_schema = 'A' and table_name = 'B'.
>
>   
>
> I want to use the value returned from row_length inside my CLLE program.
>
> Just like we can do inside of SQLRPGLE.
>
>   
>
> Is this possible to do? I find no examples, anywhere, of how to return a
>
> value from an SQL within CLLE.
>
>   
>
> As always, thank you all for your time.
>
>   
>
> Steve Meisinger
>
>   
>
> --
>
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>
> To post a message email:  <mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> MIDRANGE-L@xxxxxxxxxxxxxxxxxx
>
> To subscribe, unsubscribe, or change list options,
>
> visit:  <https://lists.midrange.com/mailman/listinfo/midrange-l> https://lists.midrange.com/mailman/listinfo/midrange-l
>
> or email:  <mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx> MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
>
> Before posting, please take a moment to review the archives
>
> at  <https://archive.midrange.com/midrange-l> https://archive.midrange.com/midrange-l.
>
>   
>
> Please contact  <mailto:support@xxxxxxxxxxxxxxxxxxxx> support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
>
>   
>
> Help support midrange.com by shopping at amazon.com with our affiliate link:  <https://amazon.midrange.com> https://amazon.midrange.com