Re: SQL and CLLE

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