• Subject: Fwd: SQLRPG/400 Day of Week ?
  • From: MacWheel99@xxxxxxx
  • Date: Thu, 30 Nov 2000 12:20:14 EST

Thanks for the info.
I have been accustomed to using SQL SELECT in which 100% of the fields 
referred to in the SQL statement are defined somewhere else in the RPG 
program & I am not yet sufficiently familiar with other SQL functions to know 
if that is universally true.

SQL DayOfWeek scalar function ONLY accepts native date type as its function.
RPG/400 does not support the "D" type.
However Mike Cravitz has a solution to this via sub-stringing within the SQL 
Set Statement.
I think that instead of using field TODAY which I populated using *DATE which 
is an RPG/400 copy of today date in the format 8.0 (*UDATE is today in 6.0), 
I might put this in a generic work date field populated immediately before 
calling a subroutine that would have this, so that by feeding any date to 
work date then calling subroutine, it would return value 1-7 day of week, 
that would then be used as the index to an array at the end of the program 
populated ** Monday Tuesday etc.


> From: Chanh_Le@countrywide.com
>  How about try to use
>  D today           S               d
>  instead of

MacWheel99@aol.com (Alister Wm Macintyre) (Al Mac)


If you look closely at the ABCs of SQL article, you'll see that field
NtvDate is defined to be of type D. This means it is a date data type (not
numeric, not character, but date). The SQL scalar function DayOfWeek _only_
accepts native date data types as its parameter. Unfortunately, RPG/400 does
not support native date data types. However, there is a way around this
problem. I'm assuming your 8,0 date is in the format mmddyyyy. You can
replace the SQL Set statement in Figure 1 with the following.

     C/Exec SQL
     C+ Set :WkDay = DayOfWeek( Date( Subst( Digits( :Today ), 5, 4))
     C+                                                           || '-'
     C+                                                           ||
Subst( Digits( :Today), 1, 2)
     C+                                                           || '-'
     C+                                                           ||
Subst( Digits( :Today ), 3, 2))

Note, if your date is in yyyymmdd format this changes slightly as follows...

     C/Exec SQL
     C+ Set :WkDay = DayOfWeek( Date( Subst( Digits( :Today ), 1, 4)
     C+                                                           || '-'
     C+                                                           ||
Subst( Digits( :Today), 5, 2)
     C+                                                           || '-'
     C+                                                           ||
Subst( Digits( :Today ), 7, 2)

Good luck.

Mike Cravitz
Does SQL's SET DayofWeek Scalar Function work in RPG/400?
If so, what am I doing wrong?

I have been working with RPG/400 but still a bit of a newcomer to SQL.
This week I got the December 2000 News/400 & decided to try out some of the
tips & those that worked for me were GREAT!  I passed on some of the
Query/400 tips (like uses for zero width fields) to non-programmer
On page 67 there is an article by Mike Cravitz on using SQL's Set Statement.
Well I have several SQLRPG/400 programs which are 90% RPG, but have embedded
SQL SELECT to get at data base stuff, instead of using chain read etc. which
I think is real cool - you can get at selected info from many files in one
SQL input, and just the info you need, not all the other baggage of a file
record fields not being used.

I selected a program that has lots of white space in front of printing date
11/27/00 thinking that I would first put Monday in front of that, then in
some later program go after what day of week is associated with historical
dates like when some shipment was made.

& I also defined WKDAY as 1.0


Error message from SEU
Token : was not valid.  Valid Tokens: OPTION RESULT CONNECTION TRANSACTION

I put cursor on error message & F1
A syntax error was detected
Token : is not a valid token
Syntax seems correct up to that point

Well I use that exact symbol in my SELECT lines that compile & in fact I got
this code by cut & paste from such a program then keyed over top to make the
code look like the ILE example in the article, so if colon is invalid it is
invalid with SET or I have a spacing problem

Assuming this should work in SQLRPG/400, I figure that I may have a
punctuation error, but not being an SQL guru, I cannot see it.

MacWheel99@aol.com (Alister Wm Macintyre) (Al Mac)
AS/400 Data Manager & Programmer for BPCS 405 CD Rel-02 mixed mode (twinax
interactive & batch) @ http://www.cen-elec.com Central Industries of
Indiana--->Quality manufacturer of wire harnesses and electrical
sub-assemblies - fax # 812-424-6838

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.