Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
4 replies Latest Post - ‏2013-01-07T18:39:36Z by gjknet
gjknet
gjknet
3 Posts
ACCEPTED ANSWER

Pinned topic ccsid's after V7R1 upgrade

‏2013-01-07T15:57:48Z |
I have been using qsys2/syscolumns but find two columns there have been changed to graphic (ccsid 1200) and don't convert to character data when I use embedded sql to retrieve them. If I use i navigator, they do convert.

What am I missing here.

here is my select statement, it works in i navigator but not in green screen:
with My_Columns as ( SELECT table_schema, table_name, COLUMN_NAME
,SYSTEM_COLUMN_NAME , DATA_TYPE, LENGTH, NUMERIC_SCALE,
IS_NULLABLE, COLUMN_HEADING, Column_text,1 as depth, colno FROM
qsys2/syscolumns WHERE TABLE_NAME = 'ORDBASP' AND
TABLE_SCHEMA = 'WVLF' )
Select
substr(DbName,1,1) || Lower(substr(DbName,2,127)) as DbName,
substr(TbName,1,1) || Lower(substr(TbName,2,127)) as TbName,
substr(Name,1,1) || Lower(substr(Name,2,127)) as Name,
substr(Sys_CName,1,1) ||
Lower(substr(Sys_CName,2,127)) as Sys_CName,
ColType, Length, Scale, Nulls, Label, Column_Text,
Depth, ColNo
from My_Columns
order by Depth, ColNo

Thanks,
Gene Snipes
GJK Consulting, Inc.
Updated on 2013-01-07T18:39:36Z at 2013-01-07T18:39:36Z by gjknet
  • PWConner
    PWConner
    42 Posts
    ACCEPTED ANSWER

    Re: ccsid's after V7R1 upgrade

    ‏2013-01-07T16:13:29Z  in response to gjknet
    I copied your sql statement and pasted it into a green screen (STRSQL). I changed the table_name and table_schema name. The statement ran and I received results, but perhaps I'm not correctly following your issue. Should the table_name I use contain graphic fields?
    • gjknet
      gjknet
      3 Posts
      ACCEPTED ANSWER

      Re: ccsid's after V7R1 upgrade

      ‏2013-01-07T16:26:18Z  in response to PWConner
      There are two columns in qsys2/syscolumns that are graphic, ccsid 1200.
      1. LABEL GRAPHIC 60 122 4418 Input COLUMN_HEADING
      2. LABELTEXT GRAPHIC 50 102 8700 Input COLUMN_TEXT

      They both return graphic data and not character data in a green screen sql session.

      Gene
      phone is 317 694 5577 if you wish.
      • PWConner
        PWConner
        42 Posts
        ACCEPTED ANSWER

        Re: ccsid's after V7R1 upgrade

        ‏2013-01-07T18:28:04Z  in response to gjknet
        I am not getting the same results. When I run the select statement you posted in an interactive sql session (STRSQL) I get data (character data) in the label and labeltext columns. I'm at V7R1.
  • gjknet
    gjknet
    3 Posts
    ACCEPTED ANSWER

    Re: ccsid's after V7R1 upgrade

    ‏2013-01-07T18:39:36Z  in response to gjknet
    Thanks for the prompt response. I've bypassed the issue by using the CAST() function in SQL. I'm sure it has to do with the ccsid settings.