Re: 1st RPG program with embedded SQL

[Steve Richter <stephenrichter@xxxxxxxxx>]

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