QUERY-problem

[Hubert Steidl]

Hello,
I´m working on V4R3 and I have the following problem in query:

I have a numeric field (name=STREP) with 8/0 ZONED a I will convert it in a
character-field. For this, I create in query a new field with the operator
DIGITS(STREP) and I do an output in a new databasefile.
If I transfer this file to Excel, the new field has now an HEX-format.
What is wrong?
How I can convert a ZONED-field to an CHAR-field with query?

thanks for help
Hubert Steidl
IT-Manager
Schneider Electric Austria
e-mail: hubert...@at.schneider-electric.com

[Jeff West]

I,m also using the same version of operating system on the AS/400 V4R3 and
the problems you mention sound like there's a problem with the EBCDIC to
ASCII translation depending on what kind of software your using to send the
file to the PC. We use Rumba's 'Arpeggio' or 'File Transfer' and send the
data as a comma delimited Text file so Excel can easily break the data
apart. If your data looks ok on the 400 side chances are that your problem
is the translation.I hope this info helps you in some way.

Jeff

Hubert Steidl wrote in message <896080$e7c$1...@rohrpostix.uta4you.at>...

[Gary Guthrie]

When Query/400 creates a character result field in this way, it uses the
default CCSID of 65535 (binary) for the field. This is why your field is
transferring as it is.

To "fix" the problem, rather than letting Query/400 create the outfile,
code DDS for the outfile and compile it. Your new field will then use
the same default as other fields. Have Query/400 write to this file
you've created, transfer it, and you'll be fine.

Gary Guthrie
Technical Editor, NEWS/400 magazine

[The TEAMIBM Network]

DIGITS uses the job CCSID as the result CCSID. You can either change your job
so you are not running with a job ccsid of *HEX (65535). Or use the CAST
function.

SELECT CAST(STREP as CHAR(8) CCSID 37) FROM file

Carol Ramler, DB2 UDB for AS/400 Development

111...@netnews.vnet wrote:

> 400MISC NETNEWS appended at 13:36:48 on 00/02/25 GMT (by 111245 at NETNEWS)
>
> From: <"Hubert Steidl" <hubert...@utanet.at>>
> Organization: UTANET Newsserver
> Message-ID: <896080$e7c$1...@rohrpostix.uta4you.at>
>
> Hello,
> IXm working on V4R3 and I have the following problem in query:

[Hubert Steidl]

Sorry, but DIGITS is not using the Job-CCSID, it is using the SYSVAL QCCSID.
My Job-CCSID is 273 (german), but my QCCSID is 65535. Thats why the new
field is in HEX-format.
I have done a test and have changed the Sysval QCCSID to 273, and it works
now correctly.

Can anyone tell me if there is a risk if I cange the QCCSID to 273 ?

Thanks to all for replys.
Hubert

The TEAMIBM Network schrieb in Nachricht
<20000225225838.400MI...@ibm.com>...

[STolo...@kkb.kz]

Hi!
Change everything to 273.
I use 1025(Russian) and have no problem.
But If you'd like that everything works perfect you need change on client
site as well.
And don't forget change your profile and CCSID for PF
Sort sequence . . . . . . . . . *LANGIDSHR
Language ID . . . . . . . . . . RUS
Coded character set ID . . . . . CCSID 1025
change to deutsche

[Robert A. Twarowski]

>
> Can anyone tell me if there is a risk if I cange the QCCSID to 273 ?
>

One example: I've changed QCCSID from 65535 to 870 (polish) on machine with
JDEdwards. After that JDE menu driver have problems with polish special
characters. (All screen texts in JDE are "soft coded" and stored in the
database). I've changed the system value back and solved the problem.

Regards
Robert A. Twarowski

[Charles R. Pence]

Hubert Steidl wrote:
> Sorry, but DIGITS is not using the Job-CCSID, it is using the SYSVAL
> QCCSID. My Job-CCSID is 273 (german), but my QCCSID is 65535. Thats
> why the new field is in HEX-format.

> I have done a test and have changed the Sysval QCCSID to 273, and it
> works now correctly.
>

> Can anyone tell me if there is a risk if I cange the QCCSID to 273 ?

I suspect possibly you are using 8=Submit or 3=Submit, and that your
user profile does not have a CCSID set? The default job CCSID was
supposed to eliminate such problems... but for some reason... But
anyway WRKJOB OPTION(*DFNA) on the job running the query should have
a CCSID <ie. not Job Default CCSID> of something other than 65535
<aka *HEX>. The best option IMO is to ?CHGPRF to set the CCSID.
If your Job CCSID is set, but your user profile CCSID is not, and
you are using a submit option w/in WRKQRY, then post again....
and I will look up the solution in the APARs.

Regards, Chuck
All comments provided "as is" with no warranties of any kind whatsoever.

[Karl Hanson]

"Charles R. Pence" wrote:
>
> Hubert Steidl wrote:
> > Sorry, but DIGITS is not using the Job-CCSID, it is using the SYSVAL
> > QCCSID. My Job-CCSID is 273 (german), but my QCCSID is 65535. Thats
> > why the new field is in HEX-format.
> > I have done a test and have changed the Sysval QCCSID to 273, and it
> > works now correctly.
> >
> > Can anyone tell me if there is a risk if I cange the QCCSID to 273 ?
>
> I suspect possibly you are using 8=Submit or 3=Submit, and that your
> user profile does not have a CCSID set? The default job CCSID was
> supposed to eliminate such problems... but for some reason... But
> anyway WRKJOB OPTION(*DFNA) on the job running the query should have
> a CCSID <ie. not Job Default CCSID> of something other than 65535
> <aka *HEX>. The best option IMO is to ?CHGPRF to set the CCSID.

<snip>

Yes, and setting the user profile CCSID also enables database access
from other remote SQL clients, such as another AS/400 or products like
DB2 Connect (via DRDA).

--

Karl Hanson