Re: SQL Casting/Exponential Notation

On 17 May 2012 08:34, Michael Ryan wrote:

Helping an associate...has an RPG program that does this:

exec sql
create table qtemp/Work2 as
(select cast (it.cprod# as char(15) ccsid 37) as cprod#
, cast (it.cloctn as varchar(8) ccsid 37) as cloctn
, cast (it.cboxst as varchar(3) ccsid 37) as cboxst
from qtemp/prdmstwght as it
order by 1)
with data;

Of interest would be the: DSPFFD QTEMP/prdmstwght

And then uses a CPYTOIMPF to go to an IFS file.

Of interest would be DSPF of the stream file records generated.

Column it.cloctn has a value of 5E72, but it's being converted to
5.00E+72. The column is of type character in the table. I think SQL
is looking at the value and deciding it's a number in exponential
notation. How can I do this and keep the character value of
it.cloctn?

What application shows the latter? I suspect that value is presented in a client application, and the intermediate output was not reviewed.?

If the SQL is changing a character string value to a number and then to VARCHAR in that casting, then there is a huge problem; that would make no sense. There is surely something else going on.

The output from each intermediate steps should be closely reviewed; i.e. the two "Of interest" added inline to the quoted message, above.

Regards, Chuck