Re: Using SQL TRANSLATE in embedded RPG

[Charles Wilt <charles.wilt@xxxxxxxxx>]

TRANSLATE() expects from-string to be
"The string must be any built-in numeric or string constant."

A variable is not supported.

Charles

On Wed, Oct 6, 2021 at 2:35 PM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> Hi everyone
> We are on V7r3
> Because we have users cut and paste information from e-mails etc. into a
> screen - this data is then stored into a file on our system
> When an attempt is made to display this information in a screen - we have
> a problem
> That problem is due to the fact that the data that was cut and pasted
> contains non-displayable characters
> We are attempting to rectify this situation by using SQL TRANSLATE
>
> When the following is used
> exec sql
> set :StringOut = TRANSLATE (:StringIn, ' ',
>          x'0001020304050607080910111213141516171819+
>            2021222324252627282930313233343536373839');
>
> It works
> Sringin contains the data with the non-displayable characters
> Stringout contains the data with the non-displayable characters removed
>
> However, when I change it the code to the following
>
> dcl-s NonDisplayableChars  char(40)
>         inz(x'0001020304050607080910111213141516171819+
>               2021222324252627282930313233343536373839');
>
> Exec sql
> set :StringOut = TRANSLATE (:StringIn, ' ', :NonDisplayableChars);
>
> This compiles okay - but the this does not work - STRINGOUT is spaces
> However
> SQLCODE = -171
> SQLSTATE = 42815
> This translates to
> THE DATA TYPE LENGTH OR VALUE OF ARGUMENT OF IS INVALID
> Can anyone see what is wrong with the second method, as I would MUCH
> prefer to use a variable (personal preference)
>
> Alan Shore
> Solutions Architect
> IT Supply Chain Execution
>
> [cid:image002.png@01D7BAD0.2AACCA90]
>
> 60 Orville Drive
> Bohemia, NY 11716
> Phone [O] : (631) 200-5019
> Phone [C] : (631) 880-8640
> E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx
>
> 'If you're going through hell, keep going.'
> Winston Churchill
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com