Re: SQL issue with CCSID 65535

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

The only way to fix this is to have true job CCSID that is not 65535 - 
that CCSID means "no conversion".

So you CAN, in your program, do a CHGJOB - maybe with QCMDEXC in the 
RPG. Get the current CCSID using a Retrieve Job Info API and restore it 
after you've done your thing in the RPG programs.

HTH
Vern

On 12/13/2019 9:09 PM, smith5646midrange@xxxxxxxxx wrote:
> I am working on an RPGLE program that is using the SQL
> QSYS2.PARSE_STATEMENT.  My job's CCSID is 65535 which is causing the below
> SQL to fail with "Character conversion between CCSID 65535 and CCSID 1200
> not valid."  Unfortunately, the error does not tell me which field it is
> unhappy with.  If I change my job's CCSID from 65535 to 37, it works.
> However, I need to find a way to correct this without changing profiles or
> the system value.  This will be running on other company's computers and I
> can't make them change these settings, mainly because I don't know if it
> would impact any of their existing software.  As you can see, I have even
> tried to cast each of the result field to CCSID 37 but that didn't help.
> Can someone point me in the right direction?  Is there a workaround?
>
>   
>
> with data as
>
> ( select cast(coalesce(name_type,' ') as char(10) ccsid 37) as name_type,
>
>
>           cast(coalesce(name,' ') as char(10) ccsid 37) as name,
>
>           cast(coalesce(schema,' ') as char(10) ccsid 37) as schema,
>
>           cast(coalesce(column_name,' ') as char(10) ccsid 37) as
> column_name,
>
>           name_start_position
>
>      from table(qsys2.parse_statement('select DS1.MONTHNO
>
>                                          from DIVSALES DS1
>
>                                         where DS1.MONTHNO = 1
>
>                                         order by DS1.DIVNO',
>
>                                      '*SYS', '*PERIOD', '*APOSTSQL'))
>
> )
>
> select name,
>
>         schema,
>
>         column_name,
>
>         name_start_position
>
>    from data
>
>   where name_start_position < (select min(name_start_position)
>
>                                  from data
>
>                                 where name_type = 'TABLE')
>
>   order by name_start_position
>
>