Re: Is it possible to do a 'Select FIRST x - records' in SQL

Thierry,

Here's an example of what we use to do this.  We're not doing a SELECT 
FIRST, we're positioning the cursor (so you could just position it at the 
beginning of the recordset).  And just for grins, we're fetching this into 
a multiple-occurring data structure, then returning the MODS as a result 
set to a Net.Data macro.

     c/exec sql
     c+ declare C1 scroll cursor for
     c+
     c+ select digits(riot) as riot, digits(ripro) as ripro, snnum,
     c+ sndat8, riopen, fhpda8, fhbl#, fhpo
     c+ from frp001, rap002
     c+ inner join rap003
     c+ on ricust = sncust and riot = snot
     c+ and ripro = snpro
     c+ where ricust = :p_customer and
     c+ ristat = 'O'and riopen > 0 and
     c+ riot = fhot and ripro = fhpro
     c+ order by riot asc, ripro asc
     c+
     c+ for fetch only
     c/end-exec

     c/exec sql
     c+ open C1
     c/end-exec

     c/exec sql
     c+ fetch relative :start from C1 for :num_rows rows into 
:ds_returnData
     c/end-exec

     c/exec sql
     c+ set result sets array :ds_returnData for :num_rows rows
     c/end-exec

Mike Eovino
E-Commerce Manager
EDPS





"Corteville, Thierry" <Thierry.Corteville@xxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
08/10/2004 06:28 AM
Please respond to RPG programming on the AS400 / iSeries
 
        To:     "'rpg400-l@xxxxxxxxxxxx'" <rpg400-l@xxxxxxxxxxxx>
        cc: 
        Subject:        Is it possible to do a 'Select FIRST x - records' 
in SQL


I'm having a production order file wich contains Po-nbr and a week

Po-nbr      : nprod

Start-week : dplsw

Tool-nbr   : ntool

 

 

I want to retrieve  only the first PO-nbr to start.... but it is possible 
to
have 2 or more Po's starting the same date... in that case I only want the
first PO-nbr

 

What I did so far ...

with whpo (wntool, wdplsw) 

 as (select ntool, min(dplsw)  from pofile

     group by ntool) 

select nprod, dplsw, ntool, wntool, wdplsw 

from pofile left outer join whpo 

   on wntool=ntool and wdplsw=dplsw 

 

But the what I want to avoid 'duplicate records... what I would need in 
fact
is a select first....

 

Anyone good ideas ? 

 

 

Thierry 

 

--
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.