Topic
  • 4 replies
  • Latest Post - ‏2013-01-07T18:39:36Z by gjknet
gjknet
gjknet
3 Posts

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

    Re: ccsid's after V7R1 upgrade

    ‏2013-01-07T16:13:29Z  
    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

    Re: ccsid's after V7R1 upgrade

    ‏2013-01-07T16:26:18Z  
    • PWConner
    • ‏2013-01-07T16:13:29Z
    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?
    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

    Re: ccsid's after V7R1 upgrade

    ‏2013-01-07T18:28:04Z  
    • gjknet
    • ‏2013-01-07T16:26:18Z
    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.
    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

    Re: ccsid's after V7R1 upgrade

    ‏2013-01-07T18:39:36Z  
    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.