Archives of the TeradataForum

Message Posted: Fri, 11 Sep 2015 @ 11:03:51 GMT

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

<-- Anonymously Posted: Friday, September 11, 2015 07:03 -->

Hi All,

I am having some probs with the below and if anyone had any thought or advice that would be so appreciated

I am attempting the below logic (Query 1)but it fails with 3771 Illegal expression in WHEN clause of CASE statement, I think this failure maybe validly due to -perhaps - the rules of the WHEN Search conditions where I can have LIKE and ANY but not SELECT

So I tried to break it down into a select and subquery - 1 set for the LIKE ANY and 1 set for the NOT LIKE ANY

a) Query 2 from CASE statement 2 - Using NOT LIKE ANY - fails with 2620] The format or data contains a bad character when I use the SUM but runs when I don't include the SUM

b) Query 3 from CASE Statement 1 - Using LIKE ANY - runs for both using the SUM and with out using the SUM

Would anyone have any thoughts on how I could approach query 1 Or How to approach query 2 using SUM & NOT LIKE ANY

Thanks in advance

Query 1

     SELECT
     --CASE Statement 1
     SUM (CASE
       WHEN
       TEL_NO_CLEAN LIKE ANY (
       SELECT DISTINCT COLUMN1 || '%' AS AREA_CDE
       FROM PHONE_TABLE
      )
     THEN 1 ELSE 0
     END) AS TOTAL_PASS

     --CASE Statement 2
     ,SUM (CASE
       WHEN
       NOT(TEL_NO_CLEAN LIKE ANY (
       SELECT DISTINCT COLUMN1 || '%' AS AREA_CDE
       FROM PHONE_TABLE
      ))
     THEN 1 ELSE 0
     END) AS TOTAL_FAIL
     FROM CUST1
     WHERE
     CM_TEL_NO IS NOT NULL ;
     -- 3771 Illegal expression in WHEN clause of CASE statement

Query 2 from CASE statement 2 - Using NOT LIKE ANY --A With SUM - fails -- [2620] The format or data contains a bad character.

     SELECT
     SUM(TEL_NO_CLEAN)  FROM CUST1
       WHERE TEL_NO_CLEAN NOT LIKE ANY  (
       SELECT DISTINCT COLUMN1 || '%' AS AREA_CDE
       FROM PHONE_TABLE );

     --B no SUM - works
     SELECT
     CM_TEL_NO_CLEANSED  FROM CUST1
       WHERE CM_TEL_NO_CLEANSED NOT LIKE ANY  (
       SELECT DISTINCT COLUMN1 || '%' AS PH_AREA_CDE
       FROM PHONE_TABLE );

Query 3 from CASE Statement 1 - Using LIKE ANY --A no SUM - works SELECT TEL_NO_CLEAN FROM CUST1

     WHERE TEL_NO_CLEAN LIKE ANY  (
     SELECT DISTINCT COLUMN1 || '%' AS AREA_CDE
     FROM PHONE_TABLE ) ;

     --B with SUM - works
     SELECT
     SUM(TEL_NO_CLEAN)  FROM CUST1
       WHERE TEL_NO_CLEAN LIKE ANY  (
       SELECT DISTINCT COLUMN1 || '%' AS AREA_CDE
       FROM PHONE_TABLE )
     ;

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