RE: Help crawling along the embedded SQL path

["Guy Henza" <guyhenza@xxxxxxxxxxx>]

Try looking on you own system...
FNDSTRPDM STRING(SQL) FILE(QRPGLESRC) MBR(*ALL) OPTION(*DSP)

I found;

C     Read_Rixf     Begsr
*  this procedure loads D_Product with data from RIXF and PRD200P
*
*  first  -- find number of records that will be read
*
C/EXEC SQL
C+ SELECT count(*) INTO :RECCNT FROM RIXF WHERE (XFHHFL = 'O' or
C+ XFHHFL = 'B') and (XFSTAT = 'A')
C/End-EXEC
*  get size of data ds
C                   Eval      D_Length = %Size(D_Product)
*  get total memory required
C                   Eval      D_Total_Size = D_Length * RECCNT
*  get memory
C                   Alloc     D_Total_Size  PTR1
.
.
.
or something more complex

C/exec sql declare c1 cursor for
C+    select inwhs, srname, incap, intype,
C+           inprod, insdsc, indesc, inpack,
C+           inuom, inqty, inus15, inus16
C+      from itin
C+      join srf111p on srrcap = incap
C+      join iopd    on indate between pdpbdt and pdpedt
C+     where inwhs   between :Fwh and :Twh
C+       and pdyear  = (:year)
C+       and pdper   between :Fp and :Tp
C+        or inwhs   between :Fwh and :Twh
C+       and pdyear  = (:Py)
C+       and pdper   = :Pp
C+       and intype  = 'P'
C+     order by srname, incap, inwhs, inprod,
C+           pdyear, inus16, intype, inus15
C/end-exec

C/exec sql
C+  open c1
C/end-exec

C     sqlcod        doueq     100
C/exec sql
C+ FETCH NEXT FROM C1 INTO
C+           :inwhs, :srname, :incap, :intype,
C+           :inprod, :insdsc, :indesc, :inpack,
C+           :inuom, :inqty, :inus15, :inus16
C/end-exec
C                   if        sqlcod <> 100
...
C/exec sql
C+  close c1
C/end-exec

one more example...

C/exec sql declare C4 cursor for
C+    select csal rep, srname name, ssrptm period,
C+           sum(ssneta) sales,
C+           sum(ifnull(icscst, 0) * ssqty) cost1,
C+           sum(ssmatl) + sum(sslabr) + sum(ssovhd) cost2
C+      from sad270p
C+      join brcm on sschn# = rdt1 and sssub# = rnum04
C+      join rcm  on rcusno = ccust
C+      join mgf300l on srrcap = csal
C+      left outer join iwm  on lwhs   = cwhse
C+      left outer join cic  on icprod = ssprd#
C+       and icfac  = wmfac
C+     where racsrc = 'PR'
C+       and ssrpty = 1976
C+       and ssappc not in ('CS', 'IC', 'OE', 'SS', 'SD')
C+     group by csal, srname, ssrptm
C+     order by csal, ssrptm
C/end-exec

C/exec sql
C+  open C4
C/end-exec

C/exec sql
C+   fetch C4 into :rep, :rname, :period, :sales, :ciccst, :sadcst
C/end-exec

C/exec sql
C+  close C4
C/end-exec

HTH

Guy
------------------------------

date: Mon, 11 Jul 2005 14:07:11 -0700
from: "Douglas W. Palme" <dpalme@xxxxxxxxxxx>
subject: Help crawling along the embedded SQL path

I have printed out (I hate reading PDF's online) the Embedded SQL manual and
have spent the better portion of my day reading through it, but personally I
think it leaves a lot to be desired.

Would someone mind giving me some helpful hints on using embedded sql ?

What I looking for, is the basics, or if you have a link or URL that I can
reference that would be great too.  Tutorials, samples, anything that will
get me started would be appreciated.

Douglas


If you bought, it was hauled by a truck - somewhere, sometime.



------------------------------