embedding sql to rpgle

130 pts.
Tags:
AS/400
Embedded SQL
RPGLE
SQL Server
couldd someone pls help me wid generating week number of a year in rpgle. i guess its done thru embedding sql into rpgle pls help



Software/Hardware used:
as400, rpgle
ASKED: May 6, 2010  11:47 AM
UPDATED: May 11, 2010  1:20 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

This is actually more of a math function.

Get 2 fields defined as date format. From date is Jan 1st of the year desired. Thru date is your target (maybe today’s date ?)

$TDATE Subdur $FDATE $DDIFF:*W

I think it allows *W for Weeks. If not, use *D for Days then divide by 7 to get Weeks. You can round up or off or just truncate depending on your needs (e.g. for “full weeks” you’d truncate but for “current week” you’d round up).

Mike L.

===============================================================

It depends on what “week” number you want. The SQL WEEK() function will return the week number where weeks begin on Sunday and January 1 is in Week 1. SQL WEEK_ISO() returns week numbers where weeks begin on Monday and January 1 is in Week 1.

Here’s a REXX procedure that demonstrates the difference, using CURRENT DATE as the basis for the functions:<pre>
/* REXX to show SQL WEEK() and WEEK_ISO()… */

signal on error name command_error
signal on failure name command_error
signal on syntax name syntax_error

WEEKCUR = ’00′

‘execsql ‘,
‘SET OPTION DATFMT = *ISO ‘

sql_Stmt = ,
‘select WEEK(CURRENT DATE) ‘,
‘ from sysibm/sysdummy1 ‘

‘execsql ‘,
‘PREPARE S1 FROM :sql_Stmt ‘
‘execsql ‘,
‘DECLARE C1 CURSOR FOR S1′
‘execsql ‘,
‘OPEN C1 ‘

‘execsql ‘,
‘FETCH C1 INTO :WEEKCUR’

‘execsql ‘,
‘CLOSE C1′

say RC ‘<’WEEKCUR’>’

WEEKISO = ’00′

sql_Stmt = ,
‘select WEEK_ISO(CURRENT DATE) ‘,
‘ from sysibm/sysdummy1 ‘

‘execsql ‘,
‘PREPARE S2 FROM :sql_Stmt ‘
‘execsql ‘,
‘DECLARE C2 CURSOR FOR S2′
‘execsql ‘,
‘OPEN C2 ‘

‘execsql ‘,
‘FETCH C2 INTO :WEEKISO’

‘execsql ‘,
‘CLOSE C2′

say RC ‘<’WEEKISO’>’

exit

/* —————————————————————- */
command_error:

trace off

parse source system start srcmbr srcfile srclib
say ‘Unexpected error at line ‘sigl’ of REXX program ‘,
srcmbr’ in ‘srclib’/'srcfile’. The exception ID is ‘rc’.’

exit(right(rc,4))
/* —————————————————————- */
syntax_error:

trace off

parse source system start srcmbr srcfile srclib
say ‘Syntax error at line ‘sigl’ of REXX program ‘,
srcmbr’ in ‘srclib’/'srcfile’. The error code is ‘rc’.',
‘ The description is :’
say errortext(rc)

exit(rc)</pre>
Run the procedure with:<pre>
STRREXPRC SRCMBR( WEEKCALC )
SRCFILE( mylib/QREXSRC )
CMDENV( *EXECSQL )</pre>
Choose which “week” type you want.

If you code the SQL statements in RPG, rather than a SELECT with CURSOR, you could simply use SQL VALUES INTO. (REXX won’t handle VALUES INTO no matter what the documentation claims.)

Tom

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following