Archives of the TeradataForum

Message Posted: Wed, 21 Jul 2010 @ 13:52:07 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi all

I am getting the following errors on casting columns in views

SELECT Failed. 5355: The arguments of the CAST function must be of the same character data type.

The scenario is I have a view of a table, upon which I have another view to expose it to Cognos. The view on view situation is to address security scenarios. In my experience Cognos does not take too kindly to views which do not have specific data type definitions in them, especially if the first record is null. Therefore in each view I use a specific cast to make sure the data types are correct.

The DDL for the base table is

     CREATE SET TABLE DEV1_EDW_SEM.D_SRVC_PROD_M ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           Srvc_Prod_Uid INTEGER,
           Srvc_Prod_Id INTEGER TITLE 'Service Product Id',
           Srvc_Prod_Cd CHAR(64) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Code', b  -----
           Srvc_Prod_Row_Eff_Dttm TIMESTAMP(0),
           Srvc_Prod_Row_Exp_Dttm TIMESTAMP(0),
           Srvc_Prod_Class_Type CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Class Type',
           Srvc_Prod_Grp_Lvl_1_Cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Group Level 1 Code',
           Srvc_Prod_Grp_Lvl_1_Nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Group Level 1 Name',
           Srvc_Prod_Grp_Lvl_2_Cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Group Level 2 Code',
           Srvc_Prod_Grp_Lvl_2_Nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Group Level 2 Name',
           Srvc_Prod_Grp_Lvl_3_Cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Group Level 3 Code',
           Srvc_Prod_Grp_Lvl_3_Nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Group Level 3 Name',
           Srvc_Prod_Grp_Lvl_4_Cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Group Level 4 Code',
           Srvc_Prod_Grp_Lvl_4_Nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Group Level 4 Name',
           Srvc_Prod_Nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Name',
           Srvc_Prod_Type_Cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Type Code',
           Srvc_Prod_Type_Nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Product Type Name',
           Prcss_Crtn_Dttm TIMESTAMP(0) TITLE 'Process Creation Dttm',
           Prcss_Clsd_Dttm TIMESTAMP(0) TITLE 'Process Closed Dttm',
           Orgnat_Srvc_Prod_Ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Originating Service Product Indicator',
           Termnat_Srvc_Prod_Ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Terminating Service Product Indicator')
              UNIQUE PRIMARY INDEX XPKDIMENSION___SERVICE_PRODUCT ( Srvc_Prod_Uid );

The base view definition is

     REPLACE VIEW dev1_edw_sem.D_SRVC_TYPE AS

     SELECT
      srvc_prod_uid AS Srvc_Type_Uid,
      srvc_prod_cd AS Srvc_Type_Cd, b  -----
      srvc_prod_nm AS Srvc_Type_Nm
     FROM DEV1_EDW_SEM.D_SRVC_PROD_M
     WHERE srvc_prod_type_cd = 'ST'
     AND srvc_prod_row_exp_dttm = CAST('2050-12-31 23:59:59' AS TIMESTAMP(0))

     UNION ALL
     SELECT
     -1 AS srvc_type_uid,
      'NA' AS srvc_type_cd,
      'Not Applicable' AS srvc_type_nm
     FROM (sel null a) src;

The final view definition is

     replace view DEV2_EDW_SEM_VIEWS.D_SRVC_TYPE as

     select

     cast(Srvc_Type_Uid as INTEGER) as Srvc_Type_Uid , cast(Srvc_Type_Cd as CHAR(64)) as
     Srvc_Type_Cd b  ----- , cast(Srvc_Type_Nm as VARCHAR(100)) as Srvc_Type_Nm

     from DEV1_EDW_SEM.D_SRVC_TYPE

If I execute select * from dev2_edw_sem_views.d_srvc_type where srvc_type_cd = 'SERV'

It results in the above error but if I do a straight select * then the query works fine. It looks to me that there is some internal type conversion that isnb t working correctly. Even if I explicitly convert the base view to char(64) for the column in question it still fails. (I have highlighted the column with an arrow)

What am I missing?

Many thanks

Simon Bloomer

	<Prev	Next>		<<First	<Prev	Next>	Last>>