All Forums Database
08 Nov 2013
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

 

ulrich 816 posts Joined 09/09
08 Nov 2013

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

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

13 Nov 2013

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

ulrich 816 posts Joined 09/09
14 Nov 2013

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.

 

 

 

 

 
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.