Archives of the TeradataForum

Message Posted: Tue, 25 Sep 2007 @ 17:28:46 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Tue, 25 Sep 2007 06:15:27 -0700 -->

I like this example a lot, but bit doesn't QUITE work.

You mentioned it would output this:

     < CASE  expression >
     Smith, Scott S
     Simpson, Sam
     Carter, Mike K.

But I am getting this:

     < CASE  expression >
     Simpson, Sam
     ott S Smith, Scott S
     e K. Carter, Mike K.

Trying to decipher just what was done. I am thinking there is something to this, but I haven't got it working yet. Sounds like you had the answer though, based on your output. Can you resend your code so I can see what you did to get that output?

     create volatile table nametable
     (c1 VARCHAR(42))
     primary index (c1) on commit preserve rows; insert into nametable sel
     'Scott S Smith'; insert into nametable sel 'Mike K. Carter'; insert into
     nametable sel 'Sam Simpson';

     SELECT CASE WHEN INDEX(SUBSTR(c1, INDEX(c1, ' ')+1, CHAR(c1)),' ') <= 0
     THEN SUBSTR(c1, INDEX(c1, ' ')+1, CHAR(c1)) || ', ' ||
     SUBSTR(c1,0,INDEX(c1, ' ')) ELSE SUBSTR(c1, INDEX(SUBSTR(c1, INDEX(c1, '')+1,
     CHAR(c1)),' ')+INDEX(c1,'')+1,CHAR(c1)) ||', ' ||
     SUBSTR(c1, 0, INDEX(c1, ' ')+INDEX(SUBSTR(c1, INDEX(c1, ' ')+1,
     CHAR(c1)),' ') ) END FROM NAMETABLE;

	<Prev	Next>		<<First	<Prev	Next>	Last>>