RE: iNav Run SQL Scripts vs CCSID

Perhaps an option in Translation tab under Connection->JDBC Setup menu
option? Or Language tab?

Alternatively, you can run CL commands in Run SQL Scripts by prefixing them
with CL: prefix. Something like:

CL: CHGJOB LANGID(ENU) CNTRYID(US) CCSID(37);

Or the combination of JDBC options and job options?

I guess I am not sure what exactly is the issue...

Elvis

2007 System i Fall Technical Conference | Orlando | November 4-7
Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and OS/400

-----Original Message-----
Subject: iNav Run SQL Scripts vs CCSID

Ok, I've figured out how to change the CCSID when downloading files via
iSeries Access for File Transfer, or direct into Excel. Now, how do I do
it when running SQL scripts in iNav? Would be really sweet if I didn't
have to rely on an individuals set up options. For example, either put a
statement in there, like a SET OPTION LANGID=... to fix it, or use some
option in CAST to get this to work fine in run sql scripts
WITH T1 AS (
SELECT SUBSTR(SYSLOGFLD,11,10) AS JOBNAME,
SUBSTR(SYSLOGFLD,21,10) AS JOBUSER,
SUBSTR(SYSLOGFLD,31,6) AS JOBNBR,
SUBSTR(SYSLOGFLD,37,1) AS CENTURY,
SUBSTR(SYSLOGFLD,38,6) AS DATEYYMMDD,
SUBSTR(SYSLOGFLD,44,6) AS TIMEHHMMSS,
SUBSTR(SYSLOGFLD,50,7) AS MSGID
FROM QHST07274A
WHERE SUBSTR(SYSLOGFLD,50,7) IN('CPF1124','CPF1164')
)
SELECT A.*, B.DATEYYMMDD AS ENDDATE, B.TIMEHHMMSS AS ENDTIME
FROM T1 A LEFT OUTER JOIN T1 B USING(JOBNAME, JOBUSER, JOBNBR)
WHERE A.MSGID='CPF1124' AND B.MSGID='CPF1164'

Wait, I figured it out.
WITH T1 AS (
SELECT cast (SUBSTR(SYSLOGFLD,11,10) as char (10) ccsid 37) AS JOBNAME,

cast (SUBSTR(SYSLOGFLD,21,10) as char (10) ccsid 37) AS JOBUSER,

cast (SUBSTR(SYSLOGFLD,31,6) as char (6) ccsid 37) AS JOBNBR,
cast (SUBSTR(SYSLOGFLD,37,1) as char (1) ccsid 37) AS CENTURY,

cast (SUBSTR(SYSLOGFLD,38,6) as char (6) ccsid 37) AS DATEYYMMDD,

cast (SUBSTR(SYSLOGFLD,44,6) as char (6) ccsid 37) AS TIMEHHMMSS,

cast (SUBSTR(SYSLOGFLD,50,7) as char (7) ccsid 37) AS MSGID
FROM QSYS.QHST07274A
WHERE SUBSTR(SYSLOGFLD,50,7) IN('CPF1124','CPF1164')
)
SELECT A.*, B.DATEYYMMDD AS ENDDATE, B.TIMEHHMMSS AS ENDTIME
FROM T1 A LEFT OUTER JOIN T1 B USING(JOBNAME, JOBUSER, JOBNBR)
WHERE A.MSGID='CPF1124' AND B.MSGID='CPF1164';

Suggestions for improvement?

Rob Berendt