On 31-Aug-2016 09:57 -0600, (WalzCraft) Jerry Forss wrote:
On 31-Aug-2016 08:25 -0600, (WalzCraft) Jerry Forss wrote:
I have an array that contains Item Classes I want to include
items. I have tried using as array or text field (below) but not
getting results.
GRPCLASSTEXT =
....5...10...15...20...25...30...35...40...45...50...55...60
1 'PLY MDF '
61 ' '
Here is my code. What am I doing wrong?
//?Declare Sql Statement
Exec SQL Declare C2 Cursor For
Select IM.ItNbr
, IM.ItDsc
, SL.LLocn
, SL.LBhNo
From ItemAsApL1 IM
Inner Join
SlQnty SL
On IM.ItNbr = SL.ItNbr
Where Im.ItCls <> ''
And Sl.LLocn <> ''
And Sl.LQnty <> 0
And Im.ItCls In :GrpClassText
Order By Sl.LLocn, Sl.ItNbr;
[…]
The current data is in string format in a file.
If shared, an example may be able to be composed with an actual means
that would inline the capability to effect the desired selection, rather
than an example that might be inaccurate\inappropriate for the specific
case; e.g. as with what I provide in this post to the topic, which may
or may not apply directly:
The IN predicate could be coded instead, referencing a subselect of a
query for which the table-reference is the invocation of code that
generates a table from the list of values in the text string; e.g.:
And Im.ItCls In
( select token_vc
from table( SplitLine( :GrpClassText /* , 5 ) -- or: */
, length(Im.ItCls) /* if CHAR() */
)
) as SL
)
Then just encapsulate new code [less likely able to use existing
code] in a TABLE User Defined Function (UDF) [aka User Defined Table
Function (UDTF)] to effect that work to provide each item as a row-element.
Note: The above is an example of to what Anoop alluded in a prior
reply [
http://archive.midrange.com/rpg400-l/201609/msg00000.html], but
not showing the example using the variable that was given in the OP;
i.e. assuming\alluding a delimiter is present vs use of fixed-length
values, though the example in the OP could have a blank-as-delimiter,
but that and the size of each effective array-element was unstated. The
text from the OP is included quoted above, but given that was archived
in a prior month than the post from Anoop, here is the link to the
thread of the OP:
[
http://archive.midrange.com/rpg400-l/201608/threads.html#00314]
The IN predicate could be coded instead as an invocation of code that
performs the required search; the first if returning an effective
true\false, or the second if returning a position where token is found:
And isClassInGrpTextString( :GrpClassText, Im.ItCls)=1
And findClassInGrpTextString(:GrpClassText, Im.ItCls)>0
Then just encapsulate existing or new logic to effect that /find/
work in a scalar User Defined Function (UDF).
Inferring from the given string that there are a list of undelimited
5-char values:
Then a UDTF might be defined as [this example culled and modified
from an ancient one I had working back in v5; and SQL vs RPG, but the
topic is not really specific to RPG either, and this is likely easier
and sufficiently well performing]:
DROP SPECIFIC FUNCTION SPLITLINE_UDTF_FIXED
;
CREATE FUNCTION SPLITLINE
( UND_STR VARCHAR(5000) /* Undelimited elements */
, ELM_LEN INTEGER /* Element length (fixed) */
) RETURNS TABLE
( token_vc varchar(50)
, token_nbr int
) LANGUAGE SQL
SPECIFIC SPLITLINE_UDTF_FIXED /* overloaded SPLITLINEs */
NO EXTERNAL ACTION READS SQL DATA
DETERMINISTIC ALLOW PARALLEL
cardinality 18
SET OPTION SRTSEQ=*HEX
, DECMPT=*PERIOD
, DBGVIEW=*SOURCE
return
with
manyline ( nextsplit ,thisline ,nextline ) as
( values(
cast( ELM_LEN + 1 as INT ) /* nextsplit */
,substr( UND_STR , 1 , ELM_LEN ) /* thisline */
,substr( UND_STR , ELM_LEN + 1 ) /* nextline */
)
union all
select
cast( nextsplit + ELM_LEN as INT ) as nextsplit
,substr( nextline, 1 , ELM_LEN ) as thisline
,substr( nextline, ELM_LEN + 1 ) as nextline
from manyline
where nextline <> ''
)
select rtrim(thisline)
, ( nextsplit - 1 ) / ELM_LEN
from manyline
order by nextsplit
A scalar UDF might be defined as either of the following; the first
depends on the above UDTF, the second does not; in each case however,
the search-value passed as arg2 must be sized according to the
element-size of the /array/ data, because there is no argument for the
element-size like was coded in the above UDTF:
DROP FUNCTION isClassInGrpTextString
;
CREATE FUNCTION isClassInGrpTextString
( UND_STR VARCHAR(5000) /* Undelimited elements */
, FND_STR VARCHAR( 50) /* Value to find in list */
) RETURNS INTEGER /* one if found, else zero*/
LANGUAGE SQL
SPECIFIC isClassInGrpTextString
NO EXTERNAL ACTION READS SQL DATA
DETERMINISTIC ALLOW PARALLEL
SET OPTION SRTSEQ=*HEX
, DECMPT=*PERIOD
, DBGVIEW=*SOURCE
return
coalesce(
( select 1
from sysibm.sysdummy1
where FND_STR in
( select token_vc
from table( SplitLine( UND_STR
, length(FND_STR)
)
) as SL
) /* end-IN predicate subquery */
) /* end-arg1 of coalesce( */
, 0
) /* end-coalesce( */
_or_
DROP FUNCTION findClassInGrpTextString
;
CREATE FUNCTION findClassInGrpTextString
( UND_STR VARCHAR(5000) /* Undelimited elements */
, FND_STR VARCHAR( 50) /* Value to find in list */
) RETURNS INTEGER /* Position found, or zero*/
LANGUAGE SQL
SPECIFIC findClassInGrpTextString
NO EXTERNAL ACTION READS SQL DATA
DETERMINISTIC ALLOW PARALLEL
SET OPTION SRTSEQ=*HEX
, DECMPT=*PERIOD
, DBGVIEW=*SOURCE
begin
declare str_pos int default 1 ; /* start position */
declare loc_pos int default 0 ; /* result of LOCATE */
declare str_len int ; /* input string length */
declare tkn_len int ; /* input search token length */
set tkn_len = length( fnd_str ) ;
set str_len = length( und_str ) ; /* ¿minus tkn_len?*/
Scanning:
while str_pos < str_len do
set loc_pos = locate( fnd_str, und_str , str_pos ) ;
if loc_pos > 0 then /* a possible match is found */
if mod( loc_pos, tkn_len ) = 1 then /* aligned */
leave Scanning ; /* match found on-alignment */
end if ;
else /* No match, irrespective of alignment */
leave Scanning ;
end if ;
set str_pos = loc_pos + 1 ;
end while ;
return loc_pos ;
end
With a change to the process I will have them as separate fields
in a file.
Separate *rows*, hopefully; i.e. conforming to what SQL is good at,
processing sets of data in rows, and in this case, one field\column. And
in that case, the change to the predicate could be as simple as the
following, much like Luis suggested
[
http://archive.midrange.com/rpg400-l/201608/msg00320.html], but still
using the IN predicate [for which the likely implementation is JOIN]
rather than explicitly using the join syntax as Luis alluded:
And Im.ItCls In (select Cls from GrpClassFile)
midrange.com
