× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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)


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.