RE: LOVAL /HIVAL in SQL

Pete,

One way I often handle is to move the logic into the SQL statement.

Exec SQL
select * from myfile
where :actcode = ' '
or myfield between :act1 and act2;
End-exec

Fi you have other conditions, you'll need some parentheses:

Exec SQL
select * from myfile
where key = :mykey
and ( :actcode = ' '
or myfield between :act1 and :act2 );
End-exec


HTH,
Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Wednesday, September 05, 2007 6:38 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: LOVAL /HIVAL in SQL

Hmmm. Looked through some more examples and it looks like I
can drop the apostrophes around the *HIVAL *LOVAL 's. I
thought they were constants but they are "magic" values it seems!

So it now looks like this and it works (snippets):

D q S 2A Varying
D Inz('''')

/free
if actcode<> *blanks;
act1 = q + actcode + q;
act2 = q + actcode + q;
else;
act1 = *LOVAL;
act2 = *HIVAL;
ENDIF;
exec sql
select * from myfile where
myfield between act1 and act2;

Subtle but important.

Thanks,

Pete


Michael Daly wrote:

Pete,

*LOVAL and *HIVAL are specific to RPG only. The represent

the lowest,

and highest, respectively, possible value for the field to

which they are applied.

To duplicate their function in SQL, "act1" would need to be

set equal

to the lowest value allowed for the type of field

"myfield". Likewise

with "act2", the highest value allowed.

Michael

I came across the trick of using the BETWEEN OPCODE in

embedded SQL

statements and it looks like a great way to handle

multiple variable

select statements. However, I can't get it to work, even with
interactive SQL. The interactive SQL looks like this:

select * from myfile where
myfield between '*LOVAL' and '*HIVAL'
myfield is a character field. If I don't use the single quotes, I
get a runtime error "Token * was not valid." If I do use

the quotes

it runs but I get no output.

In my RPG I have (snippets):

D q S 2A Varying
D Inz('''')
/free
if actcode<> *blanks;
act1 = q + actcode + q;
act2 = q + actcode + q;
else;
act1 = '*LOVAL';
act2 = '*HIVAL';
ENDIF;
exec sql
select * from myfile where
myfield between act1 and act2;
That statement, with *LOVAL/*HIVAL should return me all

the records,

but it returns nothing. What is the proper syntax? What have I
missed?

Pete Helgren

--
This is the RPG programming on the AS400 / iSeries (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.