Re: SQL and CLLE -- MIDRANGE-L

Birgitta

I like the idea of using a view like this - makes better sense than IBM's suggestion.

I have been unhappy, since it first came out, that IBM's RUNSQL did not allow a SELECT - especially when all the variants that people wrote, based either on query management or on the method Mark pointed out with those articles, they all did. Of course, they did not allow for consuming that data with DCLF, either.

I'm kind of surprised that we did not hear the "view" approach as a recommendation from IBM - at least, I don't remember hearing that.

Nice!! Ausgezeichnet!

Vern

On 3/10/2021 1:06 AM, Birgitta Hauser wrote:

Vern,

instead of creating a table based on a SELECT statement, I'd create an SQL view and then consume this view in the CLLE program.
An SQL View can declared and accessed with CL(LE) like any table or logical file.

A SELECT-Statement is not allowed in composition with RUNSQL.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vern Hamberg
Sent: Dienstag, 9. März 2021 19:05
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: 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

--
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.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com