SQLRPG/400 Day of Week

[MacWheel99@xxxxxxx]

The SQL scalar function DayOfWeek _only_accepts native date data types as its 
parameter. 
Unfortunately, RPG/400 does not support native date data types. 
My 8.0 date is in the format mmddyyyy because I got it from *DATE.

I keyed this into my RPG/400 program, as suggested by Mike Cravitz, but it 
did not work for me  In all probability El Typo is missing some little 
ingredient that is non-obvious to me.

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

It told me that Token : was not valid 
valid tokens being OPTION RESULT CONNECTION TRANSACTION
which is what I got before doing ths Substringing

I my experience, the SEU editor, when complaining about how I have done SQL 
statement, has zero knowlege of how host variables are defined by host 
program, so the problem here is with SET :WKDAY not the rest of the statement.

I saved my WRKMBRPDM effort anyway & tried to compile it & got SQL0104 
position 13 Token : was not valid ... but when I counted out what was 
position 13 that was the equal sign after :WKDAY

It is possible that in RPG/400, SET is only valid for updating a file, such 
as in this example from the IBM class in SQL/400

EXEC SQL
UPDATE EMPLOYEE
SET SALARY=:SALARY
WHERE EMPNO=:EMPNO
END SQL

There was the question whether SET was valid for host variables in RPG/400, 
suggesting that I use the SQL special register CURRENT DATE or CURRENT_DATE 
instead of :TODAY.  

Rob Berent had suggested that TODAY is a reserved word & should remove the 
colon from in front of it ... how then does the DATE field get populated ... 
in my initial tries with this I had wanted to use TODAY DATE but later 
explorations get at historical dates.

Then I tried what Pete Hall had suggested.  

     C/Exec SQL
     C+ Values
     C+                     DayOfWeek( Date( Subst( Digits( :Today ), 5, 4))
     C+                                                           || '-'
     C+                                                           ||
     C+                                                  Subst( Digits( 
:Today), 1, 2)
     C+                                                           || '-'
     C+                                                           ||
     C+                                                  Subst( Digits( 
:Today ), 3, 2))
     C+               INTO :WKDAY
     C/End-Exec

and I got the error message
Keyword VALUES not expected
Valid tokens  END SET CALL DROP LOCK OPEN ALTER CLOSE FETCH partial list
This tells me that if we use VALUES, something has to be in front of it like 
perhaps SET VALUES ?

So I returned my code to

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

to check out this notion that SET cannot work with a host variable & wherever 
the data goes needs to go to some field only in the SQL statement, then I 
would need to figure out if I could put the array (Sunday Monday Tuesday 
Wednesday Thursday Friday Saturday) in the same statement & extract which 
element I need using the element defined by the SET.

But of course I got
Token WKDAY was not valid 
valid tokens being OPTION RESULT CONNECTION TRANSACTION
so I am still at a cute idea that I have not yet learned how to implement

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

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---