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.' ;