How to fix HUGE VARCHAR created in view for TDV

[Juan Rios 2]

View field definition is created with HUGE VARCHAR(9223372036854775807), even when all the fields are VARCHAR and using a SUBSTRING to redefine the field.

This mess with Cache at model level, next layers/views fails because want to use HUGE VARCHAR definition, SQL Sample:

SUBSTR(

CASE WHEN TO_CHAR(OPERATING_SYSTEM_CODE) IS NULL THEN

CASE WHEN INSTR(TO_CHAR(OPERATING_SYSTEM_NAME),' ') > 0

THEN SUBSTR(TO_CHAR(OPERATING_SYSTEM_NAME), 1, INSTR(TO_CHAR(OPERATING_SYSTEM_NAME),' ')-1 )

WHEN TO_CHAR(OPERATING_SYSTEM_NAME) = 'SunOS' THEN 'Solaris'

ELSE TO_CHAR(OPERATING_SYSTEM_NAME) END

ELSE TO_CHAR(OPERATING_SYSTEM_CODE)

END

, 1, 128) AS OPERATING_SYSTEM_TYPE,

Note:OPERATING_SYSTEM_TYPE field is in a subquery, at main query also have a SUBSTR and INITCAP. Even with this additional functions, the design/field definition keeps beeing a HUGE VARCHAR.

[Matt Lee]

Try explicitly casting your field to a VARCHAR with a specific length to ensure that TDV doesn't infer a length.

 

CAST(

SUBSTR(

 

CASE WHEN TO_CHAR(OPERATING_SYSTEM_CODE) IS NULL THEN

 

CASE WHEN INSTR(TO_CHAR(OPERATING_SYSTEM_NAME),' ') > 0

 

THEN SUBSTR(TO_CHAR(OPERATING_SYSTEM_NAME), 1, INSTR(TO_CHAR(OPERATING_SYSTEM_NAME),' ')-1 )

 

WHEN TO_CHAR(OPERATING_SYSTEM_NAME) = 'SunOS' THEN 'Solaris'

 

ELSE TO_CHAR(OPERATING_SYSTEM_NAME) END

 

ELSE TO_CHAR(OPERATING_SYSTEM_CODE)

 

END

 

, 1, 128)

AS VARCHAR(255)) AS OPERATING_SYSTEM_TYPE