BTEQ Problem when reporting column that has a UDF that returns varchar(80)

Tools & Utilities

BTEQ Problem when reporting column that has a UDF that returns varchar(80)

The following bteq script has two versions of the select statement.  When I run the first one I get an error "A column or character expression is larger than the max size"

The function CleanDesc returns varchar(80) and is below.  There is no problem if I return CHAR(80)

.LOGON xx/xx,xx

.SET Titledashes OFF                        ;

.SET Quiet OFF                              ;

.SET Retry OFF                              ;

.SET format OFF                             ;

.SET recordmode OFF                         ;

.SET sidetitles OFF                         ;

.SET SEPARATOR '            '                       ;

.EXPORT REPORT FILE = ProductHierarchy.txt  ;

.SET Width 5000                             ;

SELECT    ‘doggy’,           cleandesc('"GEN2,Products"',56)

SELECT    ‘doggy’, cast(cleandesc('"GEN2,Products"',56) as varchar(80))

;

.EXPORT RESET

.LOGOFF

.QUIT

.LOGON xx/xx,xx

.SET Titledashes OFF                        ;

.SET Quiet OFF                              ;

.SET Retry OFF                              ;

.SET format OFF                             ;

.SET recordmode OFF                         ;

.SET sidetitles OFF                         ;

.SET SEPARATOR '            '                       ;

.EXPORT REPORT FILE = ProductHierarchy.txt  ;

.SET Width 5000                             ;

SELECT    ‘doggy’,           cleandesc('"GEN2,Products"',56)

SELECT    ‘doggy’, cast(cleandesc('"GEN2,Products"',56) as varchar(80))

;

.EXPORT RESET

.LOGOFF

.QUIT

CREATE FUNCTION CleanDesc ( desc_to_be_cleaned VARCHAR(200), max_length INTEGER )

     RETURNS VARCHAR(80)

     LANGUAGE SQL

     DETERMINISTIC

     CONTAINS SQL

     CALLED ON NULL INPUT

     SQL SECURITY DEFINER

     COLLATION INVOKER

     INLINE TYPE 1

     RETURN SUBSTR(      CASE WHEN desc_to_be_cleaned IS NULL

                              THEN ''

                              WHEN     REGEXP_INSTR( SUBSTR( LTRIM( desc_to_be_cleaned ), 1, 1 ),'[\w/;:>?\[\]]' ) = 0

                              THEN 'X '

                              ELSE ''

                          END

                      || REGEXP_REPLACE(   REGEXP_REPLACE(   TRIM( desc_to_be_cleaned )

                                                           , '["]'

                                                           , ''''''

                                                           , 1

                                                           , 0

                                                           , 'i'

                                                         )

                                         , '[^[\w''()+,-./:;<=>?@/[/]_`| ]'

                                         , 'X'

                                         , 1

                                         , 0

                                         , 'i'

                                       )

                   , 1

                   , CASE WHEN    max_length IS NULL

                               OR max_length < 1

                               OR max_length > 80

                          THEN 80

                          ELSE max_length

                      END

                  )

--

--

--

--     legal at beginning   /;:>?[]

--

--     legal middleORend    '()+,-./:;<=>?@[]_`|

--

--

--     not legal at begining '()+,-.<=@\_|

--

;

1 REPLY
Enthusiast

Re: BTEQ Problem when reporting column that has a UDF that returns varchar(80)

I believe the regex functions converts the data to max size varchar in unicode, you will need to cast your result back to latin if you want to use varchar.  It works with char because char is fixed width.