RE: SQL question

[Buck Calabro <buck.calabro@xxxxxxxxxxxxxxxxx>]

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: david@midrange.com
+---