Re: Viewing the resultant SQL in an RPG program

The thing I am after is a resulting SQL statement that I can read and, if necessary, submit interactively to check on my logic.

If I had an RPG program with variables and an SQL statement like so:

if employeeid <> 0;
ssn1 = %char(employeeid);
ssn2 = %char(employeeid);
else;
ssn1 = *LOVAL;
ssn2 = *HIVAL;
endif;
if userid <> *blanks;
usr1 = q + userid + q;
usr2 = q + userid + q;
else;
usr1 = *LOVAL;
usr2 = *HIVAL;
endif

select * from myfile where
myssn between :ssn1 and :ssn2 and
myuser between :usr1 and :usr2

Following Joe's (and others) approach, I am OK with the numeric values. *LOVAL and *HIVAL use -999999 and 999999 respectively in this case. But what about the character values? What do I use in that case? For example if I decide to submit the SQL with a 0 employeeid value and 'PETE' for the userid then I can assume that the resulting SQL would be something like:

select * from myfile where
myssn between -999999 and 999999 and
myuser between 'PETE' and 'PETE'

But if I submit the SQL with a value of 466 for the employeeid and *BLANK for the userid then the resulting SQL would be:

select * from myfile where
myssn between 466 and 466 and
myuser between ????????? and ????????? <---what values would be substituted here for *LOVAL and *HIVAL?

Or if I submitted the SQL with no values then the SQL would be something like:

select * from myfile where
myssn between -999999 and 999999 and
myuser between ???????? and ??????? <---what values would be substituted here for *LOVAL and *HIVAL?

I need to see what that would look like so I can submit the SQL interactively if possible and figure out what I am doing wrong.

That is what I am after. Thanks for the :X suffix trick though, I can see the hex, I just don't know what to do with it.

Pete


Douglas Handy wrote:

Pete,

When I view the *LOVAL and *HIVAL variables for the character fields in

debug I just see a highlighted empty block which I assume are special
characters.

Add the :X suffix in debug to see the fields in hex format, such as

eval usr1:x

The *Loval contents should be hex 00 and the *hival should be xFF.

Doug