Guy,
Sorry to get in at the end of this thread but we currently use a product
called StarQuery from Symtrax.
Not sure if I've understood your request or not, but if it helps here
you are.
It provides you with a map designer that joins your tables on a database
(can be almost any database, including AS400/iseries), and then another
tool (Excel Add-on) that allows you to use SQL to retrieve the data from
the database directly into excel.
We've had this for over a year now and it's very easy to use.
Regards,
Paul
-----Original Message-----
From: Alan Campin [
mailto:Alan.Campin@xxxxxxxxxxxxxxxx]
Sent: 05 July 2007 09:06
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Query/SQL/Excel
<snip>
I've never used Query/400, but I am interested in this bit:
The other better solution to anything that accesses the AS/400 from the
PC is use a stored procedure. Encapsulate all the processing in the
stored procedure (RPG or SQL language) and just call if from Excel.
Excel just gets the results.
Can you explain how to do that (SQL language)?
</snip>
Here is a simple SQL stored procedure written in SQL. You will find more
information in IBM manuals. Let me know if there are questions.
The instructions in the top are my instructions used by my utility
COMPILE to create the object. iDate is my SQL function to convert a
iSeries type date to an SQL date data type. (Both available at
www.think400.dk/downloads)
I use VARCHAR below because our data base (Mapics) uses 65535 for CCSID
code so chars need to be converted to 037 CCSID. If your tables do not
use 65535, you do not need the VARCHAR's.
You can see the advantages to using stored procedures. I can convert
dates and change fields to VARCHAR and the CCSID, all inside the
procedure. SQL procedures can be as sophisticated as you want to make
them.
/* *_> CNLLSTSPLF SRCFILE(@2/@1) SRCMBR(@3)
*/
/* *_> RUNSQLSTM SRCFILE(@2/@1) SRCMBR(@3) +
*/
/* *_> DFTRDBCOL(*NONE) CLOSQLCSR(*ENDMOD) ERRLVL(20)
*/
Drop Procedure MAPDBA/QACOVERLETTER ;
Create Procedure MAPDBA/QACOVERLETTER
(In InCarNumber Varchar(50))
Dynamic Result Sets 1
Language SQL
Not Deterministic
Reads SQL Data
Set Option DbgView = *Source, DatFmt = *ISO, DatSep = '-'
Begin
Declare C1 Cursor With Return For
Select VarChar(a.CRNUMB,50,037) As CarNumber,
iDate(a.CRDATE) As CarDateISO,
VarChar(b.VNAME,25,037) As VendorName,
VarChar(b.VADD1,25,037) as VendorAddress1,
VarChar(b.VADD2,25,037) As VendorAddress2,
VarChar(b.VCITY,25,037) As VendorCity,
VarChar(b.VSTAC,2,037) As VendorState,
VarChar(b.VZIPC,10,037) As VendorZip
From CAR a
Inner Join VENNAM b
On a.CRVNUM = b.VNDNR
Where a.CRNUMB = InCarNumber;
Open C1 ;
End ;
Comment On Procedure MAPDBA/QACOVERLETTER
Is 'Get Vendor Data for Cover Letter.' ;
This e-mail and any attachment, is confidential. If you received it in error,
please delete it from your system, do not use or disclose the information in
any way, and notify me immediately. The contents of this message may contain
personal views which are not the views of Daiwa Sports Limited, unless
specifically stated.
SAVE PAPER - Please do not print this e-mail unless absolutely necessary
midrange.com
