RE: Dynamic SQL

[Scott Mildenberger <Smildenber@xxxxxxxxxxxx>]

Bryan,

If I understand correctly, your fields are defined as numeric but you want
include them in the eval to sqlStm1.  If so, convert them to character with
%editc, I usually use 'X' as the edit code unless I care about the sign.
You may also need to remove the 'tick' around the numeric fields in the
statement.

Scott Mildenberger

> -----Original Message-----
> From: bdietz@3x.com [mailto:bdietz@3x.com]
> Sent: Thursday, November 07, 2002 8:53 AM
> To: rpg400-l@midrange.com
> Subject: Dynamic SQL
>
>
> I'm having a brain cramp trying to get the following SQL to
> work.  First a
> little history.....
>
> I am writing a program that outputs the results of the NETSTAT API
> (QtocLstNetCnn),  When this works I will make it available to
> the iSeries
> community.
> I am trying to output some data from a RPGLE program to a
> file(table) that
> is created in the program.  I have done this before but in
> this case the
> data types are both CHAR and NUMERIC.
>
> I don't  want to have the file created before hand as it is a
> varible to
> the program.  The biggest issue is how to construct the EVAL
> statement so
> that I can mix both CHAR and numeric data while keeping the
> varible file
> name.
>
> The error is     RNF7416 --- The types of the right and left
> hand side do
> not match in the EVAL operation.
>
> first the create table:  (this works)
>  eval      sqlStm = 'create table ' +
>               %trim(##lib) + '/' +
>               %trim(##File) + '('    +
>               ' LclAddr    CHAR(15)  NOT NULL,' +
>               ' RmtAddr    CHAR(15)  NOT NULL,' +
>               ' LclPort    DEC(5,0)  NOT NULL,' +
>               ' RmtPort    DEC(5,0)  NOT NULL,' +
>               ' BytesIn    DEC(10,0) NOT NULL,' +
>               ' BytesOut   DEC(10,0) NOT NULL,' +
>               ' IdleHour   DEC(3,0)  NOT NULL,' +
>               ' IdleMin    DEC(2,0)  NOT NULL,' +
>               ' IdelSec    DEC(2,0)  NOT NULL,' +
>               ' ConnStat   CHAR(10)  NOT NULL,' +
>               ' ConnType   CHAR(10)  NOT NULL,' +
>               ' OutPutTime TIMESTAMP +
>                 NOT NULL WITH DEFAULT ' +
>                ') '
> then the INSERT INTO  (which is where the problem is..)
>
>  eval      sqlStm1 = 'insert into ' +
>               %trim(##lib) + '/' +
>               %trim(##File) + '('    +
>               ' LclAddr,' +
>               ' RmtAddr,' +
>               ' LclPort,' +
>               ' RmtPort,' +
>               ' BytesIn,' +
>               ' BytesOut,' +
>               ' IdleHour,' +
>               ' IdleMin,' +
>               ' IdelSec,' +
>               ' ConnStat,'+
>               ' ConnType' +
>                ') Values('+ tick +
>               c1locadr + tick + ', ' + tick +
>               c1rmtadr + tick + ', ' + tick +
>               lclport  + tick + ', ' + tick +
>               rmtport  + tick + ', ' + tick +
>               bytein   + tick + ', ' + tick +
>              byteout  + tick + ', ' + tick +
>              idleh    + tick + ', ' + tick +
>              idlem    + tick + ', ' + tick +
>              idles    + tick + ', ' + tick +
>              fld006   + tick + ', ' + tick +
>              c1netcnntyp + tick + ')'
>
>
>