Re: Loading arrayDS from sql select into statement?

On 14-Sep-2015 11:08 -0600, Koester, Michael wrote:

I'm not real good at using SQL set-at-a-time processing, so I'm
hoping someone can give me some clues.

I'm okay with declaring cursors, and loading arrays with a multi-row
fetch, or from "fetch next" inside a for-loop. I'm trying to improve
my coding techniques, and understand the options.

I've seen mentions on this list of using "select into :ArrayDS", but
whenever I try to code up such a thing, the sql precompiler says I
can't - tells me my array is no good. Specifically, "SQL0312:
Position 16 Variable ArrayDS not defined or not usable."

I've tried all kinds of adjustments to simplify the host data
structure to appease the precompiler, but it is not in the mood.

Compiling with CRTSQLRPGI on v7.1 with mostly up-to-date PTFs (I'm
told). Could be missing one? Or could be that I don't know what I'm
doing. Or could be no one else can do this either?

Code that DOES NOT work:

DCL-DS ArrayDS QUALIFIED DIM(64);
userAcct CHAR(50);
END-DS ;

// dds-defined physical file SV02 has 3 records
// that meet "where" criteria.
// sv2eml is defined as character 50;
// sv2sts is defined as character 1.

exec sql
select sv2eml into :ArrayDS
from sv02
where sv2sts = '';

Is it just me ??? Thanks.

FWiW, the restriction can be overcome without using a cursor and FETCH, if one were so inclined; unlikely in most cases, to be viewed as an /improvement/ over just using FETCH:

I can neither verify the syntax nor verify the validity of the intended functionality for what I expect the following declaration and query should achieve. The query could be simplified by collapsing some of the Common Table Expressions (CTE); as written, the following [presented as successive CTE query results preceding the final select] expresses a means to allow the SELECT ... INTO syntax to be used to place the results obtained from a multiple row result-set into a dimensioned array, by placing the concatenated results of the rows into a [plain] Data Structure (DS) rather than into a dimensioned DS:

DCL-DS ResultSetStg CHAR(3204) ;
ArrayCnt int ;
Array likeDS(ArrayDS) ;
END-DS ;

exec sql
with
orig_query as
( select sv2eml
from sv02
where sv2sts = ''
fetch first 64 rows only
/* limited, per DIM(64) */
)
, olap_query as
( select
sv2eml
, ( row_number over () ) as rn
from orig_query
/* there is no ORDER here; add if needed */
)
, connect_by_query as
( select
level as rl
, sys_connect_by_path(char(sv2eml, 50),'') as cb_data
from olap_query
start with rn = 1
connect by prior rn = ( rn - 1 )
/* I have given past examples of such queries using
Recursive CTE (RCTE) instead; figured I'd
try to offer what might be an appropriate
alternative version with CONNECT BY syntax */
)
, array_query as
( select
cb_Data
, int(rl) as cb_Count
, ( select count(*) from orig_query ) as cb_Dbg
/* AIUI, rl=cb_Dbg in this query; if not, then
the below attempt using ORDER BY is incorrect */
from connect_by_query
order by rl desc
fetch first 1 row only
)
select cb_Data, cb_Count
into :Array, :ArrayCnt
from array_query
;