Archives of the TeradataForum

Message Posted: Tue, 11 Jan 2005 @ 14:20:46 GMT

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

<-- Anonymously Posted: Tuesday, January 11, 2005 09:14 -->

Hi,

Don't know what ITEQ is, but when nesting CASE statements you have to enclose the nested statement in parenthesis.

       SELECT
             CASE
                 WHEN INDEX(TRIM(AnyCol), ' ') GT 1 THEN 0
             ELSE
                 (CASE
                       WHEN SUBSTR(AnyCol,1,1) IN ('','0','1','2','3','4','5','6','7','8','9')
                        AND SUBSTR(AnyCol,2,1) IN ('','0','1','2','3','4','5','6','7','8','9')
                        AND SUBSTR(AnyCol,3,1) IN ('','0','1','2','3','4','5','6','7','8','9')
                        AND SUBSTR(AnyCol,4,1) IN ('','0','1','2','3','4','5','6','7','8','9')
                            THEN CAST(AnyCol AS INTEGER)
                            ELSE 0
                  END)
             END
     FROM AnyTable;

As far as "SELECT CAST('1 234' AS INTEGER);", I would not expect it to work anywhere quite frankly! Having a space IS a bad character in the data format. It does not work in my version of Queryman, nor BTEQ.

Best Regards

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