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: 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.