RE: SQLRPGLE question

Guy,

What you want is a user defined function. The only ones I have done have been based on a service program. I don't think you can create a function from a OPM program. What you could do though, is to wrap the OPM program with an ILE front-end as a module or service program and use that to create your function.

HTH,

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of GUY_HENZA@xxxxxxxxxxxxxx
Sent: Monday, October 06, 2008 2:38 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQLRPGLE question

I have a SQLRPGLE program that I need to add a JDE file to with a JDE
Julian date. How do I call a RPG III date conversion routine inside a SQL
JOIN statement? Is it possible?

Here's my working SQL

C/EXEC SQL
C+
C+ INSERT INTO FA203WA
C+ SELECT a.dprfno, a.dcusno, b.inpinv, b.inninv, b.invamt,
C+ a.prefno, a.cusno, c.inpinv, c.inninv, c.invamt,
C+ sum(a.price*a.shdqy)
C+ FROM hdcinvpr a
C+ join sc308wa b on a.dinvno = b.inpinv
C+ and a.dcusno = b.incuno
C+ join sc308wa c on a.invno = c.inpinv
C+ and a.cusno = c.incuno
C+ where b.inacod in (select plant from sc005pr)
C+ and c.inacod in (select plant from sc005pr)
C+ group by a.prefno, a.CUSNO, b.inpinv, b.inninv, b.invamt,
C+ a.dprfno, a.dcusno, c.inpinv, c.inninv, c.invamt
C+ order by a.prefno, a.CUSNO, b.inpinv, b.inninv, b.invamt,
C+ a.dprfno, a.dcusno, c.inpinv, c.inninv, c.invamt
C+
C/END-EXEC

can I add this join?
C/EXEC SQL
C+
C+ INSERT INTO FA203WA
C+ SELECT a.dprfno, a.dcusno, b.inpinv, b.inninv, d.rpag,
C+ a.prefno, a.cusno, c.inpinv, c.inninv, e.rpag,
C+ sum(a.price*a.shdqy)
C+ FROM hdcinvpr a
C+ join sc308wa b on a.dinvno = b.inpinv
C+ and a.dcusno = b.incuno
C+ join F0311 d on a.dinvno = d.rpdoc
C+ and d.rpdctm = ' '
C+ and (call 'x0028' %sst(a.dprfno:5:10),
C+ parm2,
C+ parm3,
C+ parm4,
C+ parm5,
C+ parm6,
C+ parm7)
C+ join sc308wa c on a.invno = c.inpinv
C+ and a.cusno = c.incuno
C+ join F0311 e on a.invno = d.rpdoc
C+ and d.rpdctm = ' '
C+ and (call 'x0028' %sst(a.prefno:5:10),
C+ parm2,
C+ parm3,
C+ parm4,
C+ parm5,
C+ parm6,
C+ parm7)
C+ where b.inacod in (select plant from sc005pr)
C+ and c.inacod in (select plant from sc005pr)
C+ group by a.prefno, a.CUSNO, b.inpinv, b.inninv, b.invamt,
C+ a.dprfno, a.dcusno, c.inpinv, c.inninv, c.invamt
C+ order by a.prefno, a.CUSNO, b.inpinv, b.inninv, b.invamt,
C+ a.dprfno, a.dcusno, c.inpinv, c.inninv, c.invamt
C+
C/END-EXEC

V5R4

Regards,

Guy

Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.