Re: SQL question -- MIDRANGE-L

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" <buck.calabro@aptissoftware.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:
david@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
+---