Re: 1st RPG program with embedded SQL

Jim,

what is the problem with the sequencing? Maybe on the date field you want
descending order from most recent date to older dates? to do that you put
"desc" after the field name in the order by clause.

I find it helpful to qualify my file names with a, b, c, ... as in
select a.field1, a.field2, b.field3 from file1 a join file2 b on
a.key1 = b.key2. Saves me a lot of typing.

and using inner join always confuses me when reading the SQL someone else
has written. Can never remember what that means. I use either "join" or
"left outer join". Where join requires the join to row to exist. And left
outer join returns a row even when the join to row does not exist.

-Steve


On Tue, Aug 20, 2019 at 8:54 AM Jim Hawkins <jhawkins@xxxxxxxxxxxx> wrote:

After many years of coding RPG, I am working on my first embedded SQL
program. It is working, but not exactly the way I want it to.

(I finally found a resource that explained how to do embedded SQL that made
sense to me and I don't want to create a new set of logicals for just this
program.)



Anyway, this is a subfile program but my records are not appearing in the
specific sequence I want.



My declare is

Select field1, field2.field10

from mylib.file1

inner join mylib.file2 on file1.fielda = file2.fieldb

Where file1.fieldc = :pgmvar1 and

File1.fieldd < 'XX' and
//static value

File1.fielde >= :pgmvar2
//date in cyymmdd format

Order by file file1.fielde,

File1.fielde,

File2.fieldf;



Essentially I want the user to be able to enter the a date (pgmvar2) and
position to the first occurrence of that date and scroll from there, but it
is no working as expected.



If I had the logicals I would just have



Setll (pgmvar1:pgmvar2) jfile1;

Reade pgmvar1 jfile1;

Dou %eof(jfile1);

//do stuff

Reade pgmvar1 jfile1;

Enddo;



I have: declare, open, close, fetch first, fetch next, fetch relative (for
scrolling back) statements. The program is working except for the sequence
of the records.



I am sure I am missing something, just not sure what.





Regards,



Jim Hawkins

Programmer Analyst

Interkal LLC

Kalamazoo, MI



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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com