Re: How do I do a SQL select into a data structure when specifying fields?

[Darren Strong <darren@xxxxxxxxx>]

I disagree.  You can use the qualified with embedded SQL. I use it all the 
time. 

#2 and #3 are good.  Here is an example.  This was written before free for 
D specs were available:

     D C1_t            DS                  qualified template
     D  Year                               like(GLH_t.LHYEAR)
     D  Acct                               like(GCR_t.CRSG03)
     D  AcctDesc                           like(GSV_t.SVLDES)
     D  CostCenter                         like(GCR_t.CRSG02)
     D  Balance                            like(GLH_t.LHDRAM)
     D  Part                         35
     D  Cust                         16

     D GetRows         S              5u 0 inz(RcdBfr)
     D C1a             DS                  likeds(C1_t) dim(RcdBfr) 


       exec sql declare C1 cursor for
         select Year, Acct,
                ifnull((select SVLDES from GSVL01
                          where SVSGMN='ACCOUNT'
                            and SVSGVL=S4.Acct
                          fetch first row only),'') as AcctDesc,
                CostCenter,
                sum(Balance),
                Part, Cust
           from UR5008D3 S4
           group by Year, CostCenter, Acct, Part, Cust
           order by Year, CostCenter, Acct, Part, Cust;

       exec sql open C1;
       RtnInd1=$FetchC1(C1);


     P
**********************************************************************
     P $FetchC1        B
     P*
     P
**********************************************************************
     D $FetchC1        PI              N
     D  C1                                 likeds(C1_t)
     D
     D RcdBfr          C                   100
     D idx1            S              5u 0 static
     D RtnCnt          S              5u 0 static
     D GetRows         S              5u 0 inz(RcdBfr)
     D C1a             DS                  likeds(C1_t) dim(RcdBfr) static
     D

       if idx1>RtnCnt or idx1=0;
         exec sql fetch C1 for :GetRows rows into :C1a;
         RtnCnt=SQLER3;
         if RtnCnt=0;
           clear idx1;
           return *off;
         else;
           idx1=1;
         endif;
       endif;

       eval C1=C1a(idx1);
       idx1+=1;

       return *on;
     P $FetchC1        E
___________________________________
Darren Strong
Dekko





From:   Paul Therrien <paultherrien@xxxxxxxxxxxxxxxxxx>
To:     "RPG programming on the IBM i (AS/400 and iSeries)" 
<rpg400-l@xxxxxxxxxxxx>
Date:   07/05/2017 03:30 PM
Subject:        Re: How do I do a SQL select into a data structure when 
specifying      fields?
Sent by:        "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>



This should get you started... 

1. Remove the 'qualified' (sql doens' work with that) 

2. Use a cursor and 

3. Fetch cursor for 100 rows. 

Paul 

On 2017-07-05 14:58, DFreinkel wrote:

> I am reading data from a file into a data structure.
>
> T_data ds qualified dim(100)
> Fielda like(fielda)
> Fieldb like(fieldb)
> Fieldc like(fieldc)
>
> Select fielda, fieldb, fieldc into :T_data
> From myTable;
>
> Compilers tells me SL0312 T_data is not defined or usable.
>
> What am I doing wrong or how would I do this?
>
> I do not want to read 1 record at a time.
>
> Thanks
>
> Darryl Freinkel 
>
> Darryl Freinkel
> iPad