Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016