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


This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].