Re: Query400 question

from Al Macintyre 
I am not as good as Bill at this, but different explanations & approaches can 
help identify what works with this logic.

> From: cpapp@merck.com.ar (Claudia)
  
>  One more question: don't you know if there is a keyword in query that allow
>  me to convert an alphabetic field in a numeric ? (something opposite to the
>  keyword DIGITS).

I have not used all Query/400 possibilities but believe this to be true

Alpha_Result = DIGITS(numeric field type)
Alpha_Result = CHAR(date or time type of field)
Numbers_Again = DECIMAL(alpha field) returns decimal field
Integers_Instead = INT(alpha field)

> From: Andreas.KOPP@de.imp-group.com (Andreas)

>  Imagine the 17th of February is the shipping date of goods and we find this
>  date in the system. If we always calculate one day for carrying the goods
>  to the customer in this case the 18th of February will be the day he
>  receives the goods.

You are fortunate in having shipments that are always only one day in 
transit.  We have intercontinental trucking & international shipments that 
can get hung up in customs.

>  The date the customer receives the goods shall be displayed on the report.
>  
>  We already did the conversion from Format  lsdte = 20000217 to lsdted =
>  17/02/00 (as you stated in you previous mail). 

If this is a true IBM date (I have never worked with one with the slashes 
embedded or anything other than 8 digits), then date math should work ... if 
you add DD MM or YY fields, IBM will magically know where to add it with no 
fuss or bother.

>  Now I'm still not sure how to change the date to 18/02/00.

ONE_DAY = DAY(+1)
NEXT_DAY = LSDTED + ONE_DAY

this takes 17/02/00 to 18/02/00 because if in fact you are working with DATE 
MATH then the rules are not the same as DECIMAL DIGITS MATH ... the trick is 
to be using fields defined as dates times durations or pieces of them, so 
that IBM gets the numbers into the right parts & recognizes calendar 
roll-over.

>  In our recent queries we were used to do it the "quick and dirty" way and
>  just said 20000217 + 1 and of couse in this case you'll get the 18st of
>  February but in another case it's also possible to get the 30st of
>  February....

MM/DD/YY like we use, or DD/MM/YY like you use - neither of those are a legal 
IBM date format, but while the date is in the BPCS format CCYYMMDD, it is a 
candidate for IBM date math, so add the 1 day when it is in that condition, 
then convert it with the 20 stripped off 2000 & with DD & MM rearranged with 
respect to YY.

1. Get BPCS date digits to IBM date format.
IBM_DATE = DATE(LSDTE)
At this point both LSDTE & IBM_DATE contain 20000217 except LSDTE is digits & 
IBM_DATE is true date format so date math can be used.

2. Add 1 day to result.
Note that I do not want to add numeric + 1, 
I want to add ONE_DAY = DAY(+1)

CYMD_ONE = IBM_DATE + ONE_DAY

At this point the value is 20000218 defined as a date field

3. Get IBM date to break it up for rearange - it has to be character format 
before I can manipulate it

CYMD_ALFA = CHAR(CYMD_ONE)
DMY_ONE = SUBSTR(CYMD_ONE,7,2) || SUBSTR(CYMD_ONE,5,2) || SUBSTR(CYMD_ONE,3,2)

At this point the value is 180200 defined alpha ... notice I did not insert 
the slashes like Bill did, because I like to convert back to digits & put 
them in with an edit word

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
+---