Novice needs help with available AS400 functions/procedures

[Guest.Visitor]

We are trying to create a report from a query that we can run off of a menu item. We need to enter an input date each time we run it. We also need to trim leading spaces in a field which we can't do in the WRKQRY screen using the LTRIM function. Does anyone have any ideas of how we can do this? Would a stored procedure work? We want the user to be able to run it from a menu option, otherwise our other alternative would be to bring it down to Access, run a query and print off a report. Any help would be appreciated.

[Guest.Visitor]

Check out the CRTQMQRY, STRQMQRY, CRTQMFORM, STRQMPRC, WRKQMFORM, WRKQMQRY commands. You can create and compile a query with Query Manager, then call it with a parameter from a CL.

[Guest.Visitor]

I must say, your solution for doing this in Access isn't a bad one. I've been learning and using ODBC with Access and I'm really psyched about it. You can create a database that will present them with the data that's on the AS/400 in any kind of query, report, form you want. And you don't have to waste a 5250 license (since the ODBC stuff is free, unlike PC5250) or paper to print and distribute. My own experience is that file transfers are too awkward for this sort of thing. But ODBC linked tables and Data Sources! Now that's good stuff. Then you would have all the functionality of Access and its many, many great built in functions (plus any you'd care to write). Definitely LTrim(). No need to give them an AS/400 menu option, give them a shortcut to the database, which can reside on your network. If the file's too big, or you want a lot of the work to be done on the AS/400, you can use a QRY/400 query to create a summary output file, and then link that to your Access database, instead. If you schedule that QRY/400 to run periodically, then you don't have to involve users in creating that file on the AS/400, meaning they still don't need a 5250 session to make this work. If you have to do it on the AS/400, there's not much I can recommend to get around the LTrim lack, except to say that users don't seem to mind very much when data is in columns, even names, etc. And, it is possible to pass parameters to a simple QRY/400. Not simple, really, unless you're used to doing some aspects of programming. I create an externally described file that has a field for all the parameters I'd like to pass in. In the query, under Files, I make that a secondary file. It is not necessary to join this file in any way to any of the others, but IT SHOULD ONLY EVER HAVE 1 RECORD IN IT. Otherwise you'll get too many records in your final output, since it's not joined. In the selection statement for the query, you can use these fields from your 1-record parameter file to compare to their corresponding fields in the input file. For example, if it's just a date, then you compare that date field in the parameter file to the date field you want to test in one or more of the rest of the files in the query. You can even include it in the output for your query, so you can tell what the heck the parameter was for that query. You can put lots of goodies in there (the current user, for instance, to be printed on the query report; the date value you passed in as a parameter). Now, put your parameter values into the file, and run your query. You'll probably need at least a CL program to do that, and you'd be better off with a command for the front end of the CL program to take the parameters passed in and get them into the parameter file. I can post sample code, and I'm sure others would have some to offer, if you're interested and in a position to take these steps. You'd have to have the development environment (DDS, PDM, SEU, CL compiler, etc). Again, I'd say, use ODBC and Access. I can do it both ways, and my preference is fast becoming the Access solution, for what that's worth. I have 10 years on the AS/400 and rely very heavily on QRY/400, even though I am an experienced RPG programmer. There are lot of good reasons that I don't have to get into here.

[Guest.Visitor]

I can't help you with your LTRIM problem, but here is a solutin for passing parameters to a query. The following is a direct quote from Per Nelson: IBM har written a nice little paper about the subject "Passing Parameters to a query" Click the link below http://as400service.rochester.ibm.com/s_dir/slkbase.NSF/$searchform?Open&vie w=wall In the search field write : Passing Parameters to a Query/400 Query The basic idea of the solution is in the select statement to write in the Select part: DATE GE :FROMDAT // Yes the : has to be there Save the query as normal i.e. a QRYDFN object. Run the Query with STRQMQRY and ALWQRYDFN(*YES) and set the variables in SETVAR I.e. in the above case: StrQmQry QmQRy(yourlib/yourqry) AlwQryDfn(*YES) QmForm(*QMQRY) SetVar((FROMDAT 19991122)) HTH Per Nielsen

[Guest.Visitor]

If you're going the ODBC route, I would suggest a look at CRYSTAL REPORTS.