Using RPG in SQL

[MCWebsite.Staff]

** This thread discusses the article: Using RPG in SQL **
** This thread discusses the Content article: Using RPG in SQL **
0

[frfancha]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
What about: date(substr(digits(input), 1, 4) || '-' || substr(digits(input), 5, 2) || '-' || substr(digits(input), 6, 7) ) ?

[P.Tuohy]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
Of course standard SQL will work :-) The point was to demonstrate how to use an existing RPG subprocedure in SQL and I thought the date conversion one was one everyone could relate to. But I do think that converttodate(birth) is a bit easier to read as opposed to date(substr(digits(birth), 1, 4) || '-' || substr(digits(birth), 5, 2) || '-' || substr(digits(birth), 6, 7) ). And then, of course, you could just as easily create an SQL function Paul

[Guest.Visitor]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
I saw the need, yes indeed for a line-sequence number from interactive SQL, to return with each record like Query/400. So I wrote and RPG-pgm and created an external SQL user-function and use it in "Select" stmts like this: SELECT SLN() AS SEQNBR, A.* FROM ANYFILE A Which conveniently provides that numbering per each record returned in the "select" stmt. Works well with "group" indications as well because it (the function) only gets called when a "record" is returned to the result-set. p.s. If anyone is interested...I'll check it over real good to see if it is worthy to post (Comments-wise in the source...I get carried away sometimes.)

[Guest.Visitor]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
yup, post the code! code-thief, and proud of it :-) HAGD

[scatterload]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
ye be not a code thief, matey! ye be a code PIRATE! courtesy of International Talk Like A Pirate Day! http://www.talklikeapirate.com/ -Sarrrrge

[Guest.Visitor]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
I do bear a slight resemblance to that moniker... thx for the smile :-)

[Guest.Visitor]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
I've been a hoe'er in my garden a long time...But go easy, because I'm a virgin code-post'er until I hit "Post My Message". p.s. You can find the /Copybook SQLUDF in a library named QSYSINC in QRPGLESRC if your administrator installed it when you went to V?R?

---

Code

[AlexKr]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
I am trying to use a UDF to get the number of business days between two dates entered in "JD Edwards Julian" format (i.e., 107090 for April 1, 2007, etc.). It is supposed to invoke an RPGLE procedure in the background using a work calendar file to get the result (no SQL involved). It takes two numeric parms (6,0 both) and returns an integer (number of days). I created an export definition using binder language and added my own signature to it. Then I created a UDF via Navigator defining the two parms as decimal 6,0. I have my SQL stored as a Query Manager Query. When I run the query, it stops advising me that ' in type *SRVPGM not found'. Looking at the log, it appears that the first error message returned is CPF426A 'User-defined function cannot be invoked' for reason code 2 'Either the service program is not found or the program entry point does not exist'. The service program does in fact exist in the library specified and has only one exported procedure per export definition. I have only two thoughts at this point. One is that maybe parameter definitions do not match between the procedure interface and UDF parameter definition. Secondarily, I am not exactly clear about the role of export definition with a signature here since UDF, unlike 'normal' calling program does not apparently store the signature anywhere. I will appreciate any guidance.

[Greybeard400]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
It's been a while since I have done JDE work, but I thought this info is available in the JDE database. I may be confusing this with a 3rd party product which produces dates in various formats with corresponding keys. If you haven't checked with support, I suggest you do so. This function may already be available.

[AlexKr]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
Not sure what it is. We are running World 7.3. There is no readily available function to calculate the number of BUSINESS days in JD Edwards and moreover, my goal is to produce a program free solution so that a function would be available from a query. In fact, I was able to get somewhat further in my quest. After I changed the name of the procedure in parenthesis for external program definition to all caps, the query would start recognizing the service program/procedure (BTW, does anybody know why everything is greyed out in the 'wizard' window since the function has been defined, so I have no choice but scratch it all and re-start from scratch?) But now I am apparently experiencing misunderstanding between the SQL and the service program. I tried 'zoned' scalar function in SQL matched by 'numeric' data type in Parameters tab and zoned parms in procedural interface in RPG as well as 'decimal'/decimal/packed combination but either one would end up in decimal data error at the first RPG statement referring to the parms when I run the query. To add to the trouble, I could not get the srvpgm to stop at a breakpoint when invoking the strqmqry in debug mode. Neither could I produce a meaningful RPG dump being advised that 'Variable data is not available in a secondary thread'. Again, any thoughts? Mr. Tuohy?

[P.Tuohy]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
Hi Alex, Apologies for the delay in replying - 5 hour time difference and a weekend to myself :-) I am not familiar with the JDE Dates but I wrote a little procedure to test the concept - code is below along with the instruction to create the Function. At a guess it is the SIMPLE CALL on the create function you were missing. Also, to debug a function you have to do it from a different job - much as you debug a batch job i.e. strsrvjob, strdbg srvpgm(dates)... HTH Paul Tuohy BTW, I prefer Paul to Mr. Tuohy.

---

Code

[Guest.Visitor]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
AlexKr wrote: > I am trying to use a UDF to get the number of business days between > two dates entered in "JD Edwards Julian" format (i.e., 107090 for > April 1, 2007, etc.). Alex, Since I'm not sure where exactly your problems are, I thought I'd just give you an example to work from. Here's something I wrote named getPeriod : 000100060828 H NoMain Option(*SrcStmt : *NoDebugIO) Ccsid(*UCS2 : 13488) 000200000000 000300060828 * 000400060828 * BR01 28Aug06 Transition to UCS2 data required change to logic. 000500060828 * 000600060828 * 000700060828 000800000000 FF0008_1 if e k disk UsrOpn 000900000000 001000000000 D getPeriod pr 001100000000 D gpdDtPtrn 1 Varying 001200000000 D gpdJdeDt 6s 0 001300000000 D gpdPdOut 2s 0 001400000000 D sqlNull1 5i 0 001500000000 D sqlNull2 5i 0 001600000000 D sqlOutNull 5i 0 001700000000 D sqlState 5 001800000000 D sqlFuncNm 517 Varying 001900000000 D sqlSpecName 128 Varying 002000000000 D sqlMsgTxt 70 Varying 002100000000 D sqlScrPad 20 002200000000 D sqlFinalFlg 5i 0 002300000000 002400000000 P* 002500000000 P* getPeriod : Feed in the Calendar Type and the jde Date and return 002600000000 P* the period number. 002700000000 P* 002800000000 P* To Compile: CrtRpgMod module(produsr/getPeriod) srcfile(produsr/qrpglesrc) dbgview(*list) 002900000000 P* CrtSrvPgm srvpgm(produsr/getPeriod) export(*all) 003000000000 P* 003100000000 P* CREATE FUNCTION PRODUSR/GETPERIOD (VARCHAR (1), NUMERIC(6,0)) 003200000000 P* RETURNS NUMERIC (2,0) 003300000000 P* LANGUAGE RPGLE 003400000000 P* DETERMINISTIC 003500000000 P* NO SQL 003600000000 P* RETURNS NULL ON NULL INPUT 003700000000 P* NO EXTERNAL ACTION 003800000000 P* ALLOW PARALLEL 003900000000 P* FINAL CALL 004000000000 P* SCRATCHPAD 20 004100000000 P* PARAMETER STYLE DB2SQL 004200000000 P* EXTERNAL NAME 'PRODUSR/GETPERIOD(GETPERIOD)' 004300000000 P* 004400000000 P* 004500000000 P* 004600000000 P getPeriod b Export 004700000000 004800000000 D getPeriod pi 004900000000 D gpdDtPtrn 1 Varying 005000000000 D gpdJdeDt 6s 0 005100000000 D gpdPdOut 2s 0 005200000000 D sqlNull1 5i 0 005300000000 D sqlNull2 5i 0 005400000000 D sqlOutNull 5i 0 005500000000 D sqlState 5 005600000000 D sqlFuncNm 517 Varying 005700000000 D sqlSpecName 128 Varying 005800000000 D sqlMsgTxt 70 Varying 005900000000 D sqlScrPad 20 006000000000 D sqlFinalFlg 5i 0 006100000000 006200000000 * Work Fields 006300000000 006400060828BR01 D grDtPtrn s Like(cddtpn) 006500000000 006600000000 /Free 006700000000 006800000000 // The SQL Final Flag is a signal specifying whether this is the 006900000000 // first, continuing, or last call to the procedure. 007000000000 007100000000 If sqlFinalFlg = 1 ; 007200000000 If %Open(F0008_1) ; 007300000000 Close F0008_1 ; 007400000000 EndIf ; 007500000000 *InLR = *On ; 007600000000 Return ; 007700000000 EndIf ; 007800000000 007900000000 If sqlFinalFlg = -1 ; 008000000000 If Not %Open(F0008_1) ; 008100000000 Open F0008_1 ; 008200000000 EndIf ; 008300000000 EndIf ; 008400000000 008500000000 gpdPdOut = 0 ; 008600060828 grDtPtrn = %UCS2(gpdDtPtrn) ; // BR01a 008700000000 008800060828 Setgt (grDtPtrn:gpdJdeDt) F0008_1 ; // BR01 008900060828 Readpe (grDtPtrn) F0008_1 ; // BR01 009000000000 Dow Not (%EOF(F0008_1)) ; 009100000000 If gpdJdeDt <= cdd13j ; 009200000000 Select ; 009300000000 When gpdJdeDt <= cdd01j ; 009400000000 gpdPdOut = 1 ; 009500000000 When gpdJdeDt <= cdd02j ; 009600000000 gpdPdOut = 2 ; 009700000000 When gpdJdeDt <= cdd03j ; 009800000000 gpdPdOut = 3 ; 009900000000 When gpdJdeDt <= cdd04j ; 010000000000 gpdPdOut = 4 ; 010100000000 When gpdJdeDt <= cdd05j ; 010200000000 gpdPdOut = 5 ; 010300000000 When gpdJdeDt <= cdd06j ; 010400000000 gpdPdOut = 6 ; 010500000000 When gpdJdeDt <= cdd07j ; 010600000000 gpdPdOut = 7 ; 010700000000 When gpdJdeDt <= cdd08j ; 010800000000 gpdPdOut = 8 ; 010900000000 When gpdJdeDt <= cdd09j ; 011000000000 gpdPdOut = 9 ; 011100000000 When gpdJdeDt <= cdd10j ; 011200000000 gpdPdOut = 10 ; 011300000000 When gpdJdeDt <= cdd11j ; 011400000000 gpdPdOut = 11 ; 011500000000 When gpdJdeDt <= cdd12j ; 011600000000 gpdPdOut = 12 ; 011700000000 When gpdJdeDt <= cdd13j ; 011800000000 gpdPdOut = 13 ; 011900000000 EndSl; 012000000000 Leave ; 012100000000 EndIf ; 012200000000 012300060828 Readpe (grDtPtrn) F0008_1 ; // BR01 012400000000 EndDo ; 012500000000 012600000000 Return ; 012700000000 012800000000 /End-Free 012900000000 013000000000 P getPeriod e

[AlexKr]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
No problem Paul. I am running out of time right now (the case of Monday :-)) but will try your suggestion later and definitely let you and the forum know of the result. Bill, unfortunately I am hardly able to read your code in this fashion. You may want to try and convert your source into PDF and post it as an attachment. I'll post mine as soon as I make it work as a UDF. (I know it does per se since I've tested it as a bound service program.)

[BillR]

Using RPG in SQL

** This thread discusses the article: Using RPG in SQL **
Okay. I like this idea. But when I tried to compile it (ILE) the "UDF_FIRST_CALL" is not defined(7030 ERROR). What have I missed?