Problems with the CASE structure

Database
Enthusiast

Problems with the CASE structure

Hi anybody out there can help??

I have a problem with the CASE structure.

Please see the attached SQL.

The problem is that for Str1, that is a varchar, for record nr 1, it is exiting correct trough the first WHERE in the CASE structure and are returned as a string consisting of 10 spaces, but for Str2, that is a char type, even the calculated length LT2 is 0 and therefore should have exited the first WHERE in the CASE structure, it is not returned as a string of 10 spaces?

I do not understand what is going on here? Why is the CASE structure not working correct when the source string is coming from a CHAR column, but working when it is coming from a VARCHAR column?

Br

Peter Schwennesen

--DROP TABLE ST;

CREATE MULTISET VOLATILE TABLE ST (Nr INTEGER, Str1 VARCHAR(10), Str2 CHAR(10)) PRIMARY INDEX (Nr) ON COMMIT PRESERVE ROWS;

DELETE FROM ST;
INSERT INTO ST VALUES ( 1,'          ','          ');
INSERT INTO ST VALUES ( 2,'0000000000','0000000000');
INSERT INTO ST VALUES ( 3,'  000000  ','  000000  ');
INSERT INTO ST VALUES ( 4,'    000000','    000000');
INSERT INTO ST VALUES ( 5,'000000    ','000000    ');
INSERT INTO ST VALUES ( 6,'0000001200','0000001200');
INSERT INTO ST VALUES ( 7,'  001200  ','  001200  ');
INSERT INTO ST VALUES ( 8,'    001200','    001200');
INSERT INTO ST VALUES ( 9,'001200    ','001200    ');
INSERT INTO ST VALUES (10,'000000A000','000000A000');
INSERT INTO ST VALUES (11,'  00A000  ','  00A000  ');
INSERT INTO ST VALUES (12,'    00A000','    00A000');
INSERT INTO ST VALUES (13,'00A000    ','00A000    ');
SELECT Nr,

       Str1,
       TRIM(TRAILING FROM Str1)   AS T1,
       TRIM(T1)                   AS TT1,
       TRIM(LEADING '0' FROM TT1) AS TL1,
       CHARACTER_LENGTH(T1)       AS LT1,
       CHARACTER_LENGTH(TL1)      AS LTL1,
       CHARACTER_LENGTH(TT1)      AS LTT1,
       CASE LT1
       WHEN 0
       THEN Str1
       ELSE CASE LTL1
            WHEN 0
            THEN '0'
            ELSE CASE
                 WHEN (LTL1 - LTT1) = 0
                 THEN Str1
                 ELSE TL1
                  END
             END
        END AS TStr1,
       CHARACTER_LENGTH(TStr1) AS LStr1,

       Str2,
       TRIM(TRAILING FROM Str2)   AS T2,
       TRIM(T2)                   AS TT2,
       TRIM(LEADING '0' FROM TT2) AS TL2,
       CHARACTER_LENGTH(T2)       AS LT2,
       CHARACTER_LENGTH(TL2)      AS LTL2,
       CHARACTER_LENGTH(TT2)      AS LTT2,
       CASE LT2
       WHEN 0                               -- << What is going on here???
       THEN Str2
       ELSE CASE LTL2
            WHEN 0
            THEN '0'
            ELSE CASE
                 WHEN (LTL2 - LTT2) = 0
                 THEN Str2
                 ELSE TL2
                  END
             END
        END AS TStr2,
       CHARACTER_LENGTH(TStr2) AS LStr2

  FROM ST
 ORDER BY Nr ASC;

    Nr Str1       T1         TT1        TL1  LT1 LTL1 LTT1 TStr1 LStr1 Str2       T2         TT2        TL2  LT2 LTL2 LTT2 TStr2 LStr2
1  1                 0  0  0         10                0  0  0   0
2  2  0000000000 0000000000 0000000000   10  0  10  0  1  0000000000 0000000000 0000000000   10  0  10  0  1
3  3   000000     000000  000000   8  0  6  0  1   000000     000000  000000   8  0  6  0  1
4  4      000000     000000  000000   10  0  6  0  1     000000     000000  000000   10  0  6  0  1
5  5  000000     000000  000000   6  0  6  0  1  000000     000000  000000   6  0  6  0  1
6  6  0000001200 0000001200 0000001200 1200 10  4  10  1200  4  0000001200 0000001200 0000001200 1200 10  4  10  1200  4
7  7    001200     001200  001200  1200  8  4  6  1200  4   001200     001200  001200  1200  8  4  6  1200  4
8  8      001200     001200  001200 1200 10  4  6  1200  4     001200     001200  001200 1200 10  4  6  1200  4
9  9  001200     001200  001200  1200  6  4  6  1200  4  001200     001200  001200  1200  6  4  6  1200  4
10 10  000000A000 000000A000 000000A000 A000 10  4  10  A000  4  000000A000 000000A000 000000A000 A000 10  4  10  A000  4
11 11   00A000     00A000  00A000  A000  8  4  6  A000  4   00A000     00A000  00A000  A000  8  4  6  A000  4
12 12      00A000     00A000  00A000 A000 10  4  6  A000  4     00A000     00A000  00A000 A000 10  4  6  A000  4
13 13  00A000     00A000  00A000  A000  6  4  6  A000  4  00A000     00A000  00A000  A000  6  4  6  A000  4
3 REPLIES
Senior Supporter

Re: Problems with the CASE structure

Hm, not 100% sure but

row one is stored as '          ' in the char field and '' in the varchar field to my understanding.

Given your code the case results in Str1 which is '          ' and char length is 10.

for varchar the case results in '' and has a length of 0.

check type(TRIM(T1) )   -> this returns varchar(10) and explains why you get 

CHARACTER_LENGTH(T1) = 0


Enthusiast

Re: Problems with the CASE structure

The problem is not concering the strings.

What I do not understand is, why for the 10 blanks row, row nr 1, there both LT1 and LT2 are calculated to be 0, LT1 in the CASE evaluates to 0 and exit the CASE with the result Str1 as I expected, but LT2 that also evaluates to 0, but here it do not exit the CASE with the Str2.

I realy do not understand what is going on? Something I have made wrong? Or are there a bug in the TERADATA CASE structure????

Br

Peter Schwennesen

Senior Supporter

Re: Problems with the CASE structure

Hi,

T1 = TRIM(TRAILING FROM Str1)

type(TRIM(TRAILING FROM Str1) ) states that this is a varchar(10) - so you put in a char(10) and convert it to a varchar(10).

-> LT1 = CHARACTER_LENGTH(T1) = 0

Now your case:

CASE LT1

       WHEN 0 -> TRUE -> Str1 -> CHAR(10) which is TStr1

 

 

-> CHARACTER_LENGTH(TStr1) = 10

 

so to me everything is as expected and no issue...

You chose the implicited casted varchar(10) for your condition but the original value for your furthe processing and these are different.