Archives of the TeradataForum

Message Posted: Tue, 18 Mar 2003 @ 14:51:32 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>
Subj:		Re: Alternate way to Cursor

From:		Matthew Winter
Hi,
One method is to use CASE statements, such as
SELECT
  (CASE WHEN UPPER(SUBSTRING(b FROM 1 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 1 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 2 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 2 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 3 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 3 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 4 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 4 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 5 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 5 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 6 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 6 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 7 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 7 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 8 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 8 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 9 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 9 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 10 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 10 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 11 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 11 FOR 1)
        ELSE ''
   END)
   ||
  (CASE WHEN UPPER(SUBSTRING(b FROM 12 FOR 1))
               IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'
                 , 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
                 , 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1'
                 , '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SUBSTRING(b FROM 12 FOR 1)
        ELSE ''
   END)
FROM q
Regards
Matthew Winter
Technical Architect
TPG IS, Design Authority
Teradata Certified Professional

	<Prev	Next>		<<First	<Prev	Next>	Last>>
Attachments
Library
Quick Reference