RE: AS/400 Report Writers -- BPCS-L

George,

Those are some great questions.  Here's what I would reccomend.

1) Create a logical join in a new library to join the GHH, GLH, GLA, and
GCR.  The SQL statement necessary to do this is:

CREATE VIEW BPCSF/GLHJ05
AS SELECT GLH.LHLDGR, GLH.LHBOOK, GLH.LHYEAR, GLH.LHPERD, 
GLH.LHJNEN, GLH.LHJNLN, GLH.LHIAN, GLH.LHDRAM, GLH.LHCRAM, 
GLH.LHCRAT, GLH.LHDRAT, GLH.LHLSTS, GLH.LHSTAT, GLH.LHLDES, 
GLH.LHJRF1, GLH.LHJRF2, GLH.LHDREF, GLH.LHDDAT, GLH.LHREAS, 
GLA.LALDGR, GLA.LABOOK, GLA.LAYEAR, GLA.LAPERD, GLA.LAJNEN, 
GLA.LAJNLN, GLA.LAANB1, GLA.LAANB2, GLA.LAANB3, GLA.LAANB4, 
GLA.LAANB5, GLA.LAANB6, GCR.CRIAN, GCR.CRSG01, GCR.CRSG02, 
GCR.CRSG03, GCR.CRSG04, GCR.CRSG05, GCR.CRSG06, GCR.CRSG07, 
GCR.CRDESC,
GHH.HHLDGR, GHH.HHBOOK, GHH.HHYEAR, GHH.HHPERD, GHH.HHJNEN, 
GHH.HHDATE, GHH.HHJDAT, GHH.HHTRNO, GHH.HHEVNT, GHH.HHUSER 
FROM BPCSF/GHH JOIN BPCSF/GLH
ON HHLDGR=LHLDGR AND HHBOOK=LHBOOK AND HHYEAR=LHYEAR AND
HHPERD=LHPERD AND HHJNEN=LHJNEN
LEFT OUTER JOIN BPCSF/GLA
ON LHLDGR = LALDGR AND LHBOOK = LABOOK AND LHYEAR = LAYEAR AND 
LHPERD = LAPERD AND LHJNEN = LAJNEN AND LHJNLN = LAJNLN
JOIN BPCSF/GCR ON LHIAN=CRIAN

Afterwards, you can point your query to GLHJ05.  That is probably 5 to 10
time faster than joining the files in MS Access.  The message you are
getting is definately related to the ODBC Timeout.  Simply set the Timeout
on your query to 0 and you should be fine.  You can't set the timeout on a
table though.  You must do it on the query.

2) To pass a parameter to a pass-through query, you use a "querydef" in
Visual Basic.  Don't be scared by the VB.  It is actually quite simple once
you've seen an example of it.  If you get a chance maybe you could attend
the MS Access for BPCS class and I'll walk you through it.

--Paul Holstein
iWork Software, L.L.C.

On Fri, 5 May 2000 00:11:09 -0600, BPCS-L@midrange.com wrote:

>  Paul,
>  
>  Thanks for the feedback.  Maybe you can help me lick this one. The issue
I'm
>  getting is when I join multiple large tables. The error I get isn't a
>  timeout. I can't remember the exact verbiage, but it implied that it
>  forecasted how long the query would take, and the forecast exceeded some
>  threshold. The message was returned immediately after submitting the
>  request. I get the same message in green screen SQL command line on the
400
>  sometimes. It happens when I do a monster query against the
>  GHH==>GLH==>GCR==>GSV. Pass throughs in this situation get the same
>  response.
>  
>  Oracle and Informix seem to be able to recognize that most of the records
>  are being filtered out of the primary and apply the filter to the parent
>  table before joining to the children. The 400 seems to do all the joins
>  first then filter on the conditions last. Am I writing the queries wrong?
>  
>  While we're on the subject of pass-through queries, do you know of a way
to
>  pass a where condition into the pass-through query like you do with a
>  regular one by putting your prompt text into square brackets on the
criteria
>  line? I know you can do it in VBA, but I'm wondering if there's something
>  I'm missing in the query builder.
>  
>  Also on your point about MS Abcess being robust and user friendly--I
agree,
>  but am scared to show users too much because of the damage they can do
with
>  update queries. I'm afraid of what the sysadmin would say if he saw the
big
>  hole we blow in security too. Any ideas on how to secure the ODBC service
to
>  limited users?
>  
>  Geo.
>  (801) 360-6360
>  
>  +---
>  | 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
>  +---





_______________________________________________________
Get 100% FREE Internet Access powered by Excite
Visit http://freelane.excite.com/freeisp

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