Re: JSON_OBJECT and Writing to IFS

On Wed, Jan 22, 2020 at 9:59 AM Stephen Piland
<Stephen@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I believe I figured out why the SQL below does not do what I'd like for it to do. Limit 100 and Offset 100 are related to the creation of the JSON, by starting at row 100 and limiting to 100 rows. However, by virtue of the outer json_object() placing everything into 1 and only 1 row. Therefore, there is no row 100 for it to ever build.

My intent is to limit / offset to be tied to the SELECT from the tables and not to JSON_OBJECT. Any thoughts on how to do that? I thought I had it correct but obviously not. Thanks.

I suppose there is some value as an academic exercise to really work
out how to get SQL to handle as much of this task as possible.

However, I am a huge proponent of leveraging the host language where
it makes sense to, and where it is comfortable to do so. In your case,
if you've got the proper SQL for generating one item (or the entire
set of items), and you're struggling to break down the result into
100-item chunks using SQL, then I see no reason why you shouldn't just
do some looping in RPG, and maybe some string processing in RPG if
need be.

John Y.