Fwd: SQLRPG/400 Day of Week ?

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.

Cool. 

> From: Chanh_Le@countrywide.com
>  
>  How about try to use
>  
>  D today           S               d
>  
>  instead of
>  
>  C MOVEL *DATE TODAY 8.0

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

---

• Subject: Re: SQLRPG/400 Day of Week ?
• From: "Mike Cravitz" <mcravitz@xxxxxxxxxxxxxxxx>
• Date: Tue, 28 Nov 2000 13:57:38 -0800

Alister,

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))
     C/End-Exec

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)
     C/End-Exec

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
co-workers.
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.

C MOVEL *DATE TODAY 8.0
& I also defined WKDAY as 1.0

C/EXEC SQL
C+ SET :WKDAY = DayOfWeek(:TODAY)
C/END-EXEC

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

---