Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

How to fix HUGE VARCHAR created in view for TDV


Recommended Posts

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.

Link to comment
Share on other sites

  • 1 month later...

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...