Hi Buck, I was reading the manual at IBM's Info Center and it seems to imply that doing SELECT * FROM xxx WHERE LENGTH(RTRIM(expression)) = 13 won't work: "The length attribute of the result is the same as the length attribute of expression. The actual length of the result is the length of the expression minus the number of bytes removed. If all characters are removed, the result is an empty string." Yet when I tried LENGTH(RTRIM(name)) on a file, it shows the truncated length. I guess what they mean by "length attribute" is the defined or maximum length, and the "actual length" is what is reported by the LENGTH function. Hmm. In any case, it appears that LENGTH(RTRIM(expression)) gives the same result as your UDF, so you could test your UDF by comparing results. HTH, Peter Dow Dow Software Services, Inc. 909 425-0194 voice 909 425-0196 fax ----- Original Message ----- From: "Buck Calabro" <email@example.com> To: <MIDRANGE-L@midrange.com> Sent: Friday, January 19, 2001 2:35 PM Subject: RE: SQL question > Bart Verweijen wrote: > > > I need a SQL that copies all this data to a new type where the eancode > > length = 13. I don't know what function I > >can use to get those ean-codes with length = 13. > > I mis-understood your original question. If I understand rightly, you need > a "length" function so you can select * from xxx where len(EANCODE)=13. > Since CHARLEN and LENGTH work best with variable length string fields, I > wrote a UDF (User Defined Function) that does an RPG CHECKR to find the > length of the data within the field. This *seems* to work, but you might > want to do a lot of testing on it to be sure. It's my first UDF. I was > unable to look at it in debug - it runs in another thread and won't break > for me. I may yet be doing something wrong. > > Anyway, here's the code. I owe David Morris thanks for pointing me to the > April 2000 article in Midrange Computing. > http://www.midrangecomputing.com/mc/article.cfm?titleid=a289&md=20004 > > h debug dftactgrp(*no) actgrp(*caller) > * dbgview(*list) > > * Test the length of an SQL string User Defined Function. > * to register: > > * CREATE FUNCTION STRLEN (VARCHAR(256)) > * RETURNS INTEGER > * RETURNS NULL ON NULL INPUT > * LANGUAGE RPGLE > * EXTERNAL NAME 'BUCK/SQLUDFLEN' > * DETERMINISTIC > * NO SQL > * NO EXTERNAL ACTION > * PARAMETER STYLE SQL > * DISALLOW PARALLEL > > d inpChar s 256 varying > d outLen s 10i 0 > d inpInd s 5i 0 > d outInd s 5i 0 > d outSQLState s 5 > d inpFuncName s 139 varying > d inpSpecName s 128 varying > d outDiagTxt s 70 varying > > c *entry plist > c parm inpChar > c parm outLen > c parm inpInd > c parm outInd > c parm outSQLState > c parm inpFuncName > c parm inpSpecName > c parm outDiagTxt > > c ' ' checkr inpChar outLen > > c eval *inlr = *on > > Buck Calabro > Aptis; Albany, NY > "Nothing is so firmly believed as > that which we least know" -- Michel Montaigne > Visit the Midrange archives at http://www.midrange.com > +--- > | This is the Midrange System Mailing List! > | To submit a new message, send your mail to MIDRANGE-L@midrange.com. > | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. > | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. > | Questions should be directed to the list owner/operator: firstname.lastname@example.org > +--- +--- | This is the Midrange System Mailing List! | To submit a new message, send your mail to MIDRANGE-L@midrange.com. | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: email@example.com +---
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.