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 -0800Alister, 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
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.