Re: Query400 question

From Al Macintyre on BPCS 405 CD

>  From:    Andreas.KOPP@de.imp-group.com (Andreas)
>  
>  I have a question concerning Query400.

I suggest you visit some 400 forums that are ERP-independent, for a variety 
of tips & techniques using 400 tools, such as:
http://news400.com/
http://www.midrangecomputing.com/scripts/webboard.dll/mainmenu
http://www.forums400.com
http://www.datamation.com/discussions/index.html

next 2 lines need to be re-connected
http://discussions.earthweb.com/cgi-bin/dnewsweb?cmd=article&group=earthweb.ap
pdev.general&item=10&utag=

>  I have a field in format date  (DD/MM/YY) and I would like to create a new
>  field containing the original date  plus 1 day.
>  
>  How can I add a day to date?
>  Is this possible?

Our BPCS data base fields containing dates are in the format CCYYMMDD which 
is generally a valid IBM ISO date, but not defined as a date type, because 
BPCS tolerates non-date data in those fields, such as all zeros or all nines, 
so your selection criteria needs to exclude such data from your query.  You 
may need to check the IBM Job Date format, since we have figured out how to 
have a different default date format in Query than in BPCS but I do not 
remember what we did.

If you want to rearrange contents of fields, as we often do, to print dates 
in format other than CCYYMMDD, we can only substring & concatenate fields 
that have been converted to alphameric format, such as

Alpha_Result-Field_Name = DIGITS(Numeric_Field_Name)

After the manipulation, we can get the data back to numeric format using INT 
to integer or DECIMAL to decimal, provided we do not have got non-numeric 
data in there inviting decimal data error.

Thus, you can deconstruct a date into pieces, add or subtract 1 day to a 
piece, but is the result a valid date or Feb the 30th or did you start with 
1st day of a month ... converting vs. your own estimated calendar can be 
messy - much cleaner to get it into IBM date type & learn the relevant 
terminology to let IBM/400 do the calendar math.

Today = CURRENT(DATE) ... this returns today's date in IBM data type of a date

Date_Result = DATE(BPCS field with date data in right format) ... this takes 
some BPCS field name & makes it a date from the perspective of IBM data types

Day_Result = DAY(BPCS date field) ... so for example if the date field is 
19991105 this returns the value 05 & considers it to be a DAY for purposes of 
IBM date math

Same deal with MONTH & YEAR ... this way you can extract pieces of a date 
instead of the sub-string concatenation route.

Once you have defined a date as a date in IBM terms you can do date math

Days_Result = DAYS(BPCS field representing a date) ... this gets you number 
of days since Jan 1 0001 for purpose of subtracting 2 such dates to get # 
days in between, or adding fixed numbers to this work value.

An example of this might be
Work_Days(DAY) = DAYS(TSDTE) - DAYS(TTDTE) in which the result field is 
expressed as a day for date math purposes & consists of the difference 
between 2 BPCS dates, then later you can add Work_Days to some other date & 
the result will be a correct calendar date - you need not line up what field 
layout or futz with number of days in which month.

We are a bit sloppy with our Queries, sometimes using things like
(YEAR1-YEAR2)*360 + (MM*30) - (DD*-1)

There is on-line help for Query/400 which I do not find to be very helpful.  
Instead, my bible is the IBM Query Users manual SC41-4210-00 & some stuff 
that we got from our ERP consultants ... we have a set of references that 
correlate BPCS file naming with standard query/400 functions ... you need to 
get this sort of thing directly from your ERP consultants since ours is 
intellectual property protected.

When we leave our dates in CCYYMMDD format, we generally use edit word '    - 
 -  ' because the edit codes drop off CC, for our version of OS/400.

Feel free to contact me off-BPCS_L to pursue this further ... I could 
real-mail you one of our query specifications which has date math similar to 
what you are looking for, so you could copy the technique directly.  No 
guarantees that we are working this correctly of course.

Al Macintyre  ©¿©
http://www.cen-elec.com MIS Manager Programmer & Computer Janitor
http://www.whma.org = our nitch industry has a large committee searching for 
ErPdMes products right for WHM without work-arounds, which for many members 
resemble a snipe hunt - if you are in this software business & want to fix 
your product so it will not be mistaken for what your competition has been 
doing, then you might want to join our great game.
+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---