HP/Tandem TACL How to use the % as a value in the SQL like clause

Question

In my TACL, I'm trying to create a variable used as input to an SQLCI command. I want to use a LIKE clause with a % as a wildcard. Every time it replaces the % with a ? causing the SQL statement to not return the desired results.

Code snippitz:

 ?TACL macro
 #Frame
 #Set #informat plain
 #Set #outformat pretty
 #Push stoprun DC fidata var1 mailmast sqlvol sqlsvol IsOpen EMLFile ans emlline
 #Push mailfile mfile likeit charaddr sqlin sqlout test
 [#Def True text |body|-1]
 [#Def False text |body|0]

Intervening code cut out to reduce length - the cutout code works

 == select <program name> from <full mailmast filename> where mm_file_prefix
 == like "<likename>%" for browse access;
 [#If Not [StopRun]
 |then|
   #Setv test "~%"
   #Set #trace -1
   #Set SqlIn select mm_program_name from [mailmast] where mm_file_prefix
   #appendv sqlin "like ""[LikeIt]~%"" for browse access;"
   SQLCI/Inv Sqlin,outv sqlout/
 ] == end of if

When I run the code, I display the variables, and it has replaced the % with ?

-TRACE- -19-st 1 v Invoking variable :MAILMAST.1 #Set SqlIn select mm_program_name from $DATA5.SQL2510.MAILMAST where mm_file_p refix -TRACE- -20- #appendv sqlin "like ""[LikeIt] ^ -TRACE- -20- Invoking variable :LIKEIT.1 #appendv sqlin "like ""ED?"" for browse access;" -TRACE- -20-d test ? -22-st 1 v SQLCI/Inv Sqlin,outv sqlout/ -TRACE- -23-d sqlin select mm_program_name from $DATA5.SQL2510.MAILMAST where mm_file_prefix like "ED?" for browse access; -24-

Since the % is not there as a wildcard, this SQL statement fails to bring up the proper record.

The question is, how do I put a % into a TACL variable and not get it changed to a ?

Edited to replace duplicate code with the start of the TACL Macro - MEH

score 1 · Answer 1 · answered Aug 30 '18 at 18:14

I really don't like answering my own question because that looks like I was just setting things up to make me look smart, but in continuing my research while waiting, I found the answer. See code snippitz below:

?TACL macro
 #Frame
 #Set #informat plain
 #Set #outformat pretty
 #Push stoprun DC fidata var1 mailmast sqlvol sqlsvol IsOpen EMLFile ans emlline
 #Push mailfile mfile likeit charaddr sqlin sqlout test
 [#Def True text |body|-1]
 [#Def False text |body|0]
 [#Def ascii struct
   Begin
     BYTE byt0 value 37;
     CHAR pcent REDEFINES byt0;
   End;
 ] == end of struct

Note the addition of the struct. This is to be able to assign a byte a decimal value of 37 (%) and redefine it as a character to be used in the like statement.

 == select <program name> from <full mailmast filename> where mm_file_prefix
 == like "<likename>%" for browse access;
 [#If Not [StopRun]
 |then|
   #Set test [ascii:pcent]
   #Set #trace -1
   #Set SqlIn select mm_program_name from [mailmast] where mm_file_prefix
   #append sqlin like "[LikeIt][ascii:pcent]" for browse access;
   SQLCI/Inv Sqlin,outv sqlout/
 ] == end of if

Note the use of the struct [ascii:pcent] and this does work.

Thanks to all who read my question.

HP/Tandem TACL How to use the % as a value in the SQL like clause

1 Answers1