RE: Query/400 Trainee

["Warren, Rick" <RWarren@xxxxxxxxx>]

I have used SQL to take today's date and look into the quote file for quotes
that will be active 30 days from today (curdate).  this has worked very well
for us using SQL.  you should be able to use the same functions (date &
substr) within Query400 to get what you need.

An alternative would be to create a SQL view (Logical) that would do this
for you and then have your users query against the logical.

Below is the SQL code to create a view against the Quote file in BPCS 6.02
that shows quotes active 30 days from today.
HQEFF is the quote file effective date and HQDIS is the quote file
discontinue date.  We used the IIM to filter only primary vendors into our
SQL view.


CREATE VIEW QUOTEL01 AS 
        SELECT HQPROD, HQPR1, HQEFF, HQDIS 
        FROM    BPCSF/HQT, BPCSF/IIM 
        WHERE 
                IPROD=HQPROD AND IVEND=HQVEND AND 
                (CURDATE()+ 30 DAYS)BETWEEN 
                DATE((SUBSTR(DIGITS(HQEFF),5,2))||'/'||
(SUBSTR(DIGITS(HQEFF),7,2))||'/'|| (SUBSTR(DIGITS(HQEFF),2,2))) AND 
                DATE((SUBSTR(DIGITS(HQDIS),5,2))||'/'||
(SUBSTR(DIGITS(HQDIS),7,2))||'/'|| (SUBSTR(DIGITS(HQDIS),2,2)))



Rick Warren
Burlington Chemical Co., Inc



-----Original Message-----
From: MacWheel99@aol.com [mailto:MacWheel99@aol.com]
Sent: Tuesday, June 27, 2000 11:07 PM
To: BPCS-L@midrange.com
Subject: Query/400 Trainee


Thanks to past posts here we've got date math to work on BPCS dates.  For 
example:

Today       = Current (Date)
DA      = Digits (BPCS Date Field)
DB      = Date (Substr (DA,5,2)||'/'||
              Substr (DA,7,2)||'/'||
              Substr (DA,3,2))
Days_Ago    = Days (Today) - Days (DB)

Query Selection

Days_Ago    LE  7   and other stuff

Now I want to figure another direction: add lead time days to current date, 
to get how far out is safe to accept customer order changes without factory 
disruption.

We have populated a file with cumulative lead time to manufacture end item, 
including ordering raw materials and how long to manufacture in house, 
assuming got enuf raw.  Now we want to use this in queries.  The lead time 
days fields are 3 long with zero decimal places.

Plan_Date   = Current (Date) + Lead

This is not allowed
Date, Time, or Tmestamp expression is not correct
"Lead" is what is highlighted as problem point

In_Lead = Days (Digits (Lead))

It accepts this as a valid line

Plan_Date   = Current (Date) + In_Lead

It is not accepting In_Lead either as valid days to add to current date

Is it obvious to anyone where I'm not connecting with Query/400 date math 
logic?

I can write an RPG to do this.  What I want is an AL GO RHYTHM that I can 
supply to my query users, so that they can use the cumulative lead time in
my 
new file.

Thanks

Al Macintyre  ©¿©
http://www.cen-elec.com MIS Manager Programmer & Computer Janitor
+---
| 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
+---
+---
| 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
+---