 General Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums General 04 Jun 2015 CASE is a solution? Hello,   I have a difficult task (or at least this is how I see it) to implement using SQL. Here is the case: - there are two tables: A and E each one having a numeric column. Each of the two tables contain checkpoints(numbers) for a specific region. - one table T contains a numeric column with checkpoint(numeric). I want to verify if each number from T is either in A or in E. Is it possible that one checkpoint from table T can be found both in A (possible several times-duplicates) or in E (possible several times-duplicates). By removing duplicates from each table finding a solution would be more simple, but I do also need to know the number of occurences from the two tables. The result I want to achieve is to display using SQL if each number from table T is in either table A or table E.   For example: If number 1234 was found twice in table A and once in table E, I need to have the result 'AAE' If number 1234 was found three times in table A and was not found in table E, I need to have the result 'AAA' If number 1234 was not found neither in table A nore in table E, the result should be 'N'   Can you share any ideas how to solve it? Thank you. Tags: 04 Jun 2015 I have previously tried the below approach   LOCK ROW FOR ACCESS SELECT c.numb, SUM(c.results), CASE WHEN SUM(c.results) MOD 10 = '0' THEN   'Found in A but could be duplicate' WHEN SUM(c.results) MOD 13 = '0' THEN   'Found in E but could be duplicate' WHEN SUM(c.results) = '0' THEN   'Not found' END FROM (           SELECT    a.numb,                     CASE                               WHEN TRIM(b.event_cd) IS NOT NULL                               OR        TRIM(b.event_cd)<>'' THEN '10'                               ELSE '0'                     END AS results           FROM      T           LEFT JOIN A           ON        T.numb = A.numb           AND       A.event_cd = 'OK'           UNION ALL           SELECT    a.numb,                     CASE                               WHEN TRIM(b.event_cd) IS NOT NULL                               OR        TRIM(b.event_cd)<>'' THEN '13'                               ELSE '0'                     END AS results           FROM      T           LEFT JOIN E           ON        T.numb = E.numb           AND       E.event_cd = 'OK' ) c GROUP BY 1;   but this way I was not able to identify the occurences in each of the two tables A and E. 05 Jun 2015 SEL E1.COL1, SUM(ACOUNT) AS ACOUNT, SUM(BCOUNT) AS BCOUNT  FROM E1 LEFT OUTER JOIN (SEL COL1,COUNT(*) AS ACOUNT FROM A1 GROUP BY 1) A1 ON A1.COL1=E1.COL1 LEFT OUTER JOIN (SEL COL1,COUNT(*) AS BCOUNT FROM B1 GROUP BY 1) B1 ON B1.COL1=E1.COL1 GROUP BY 1 05 Jun 2015 Can you show some sample data and expected result? Dieter You must sign in to leave a comment.