CASE is a solution?

General
Teradata Employee

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 (3)
3 REPLIES
Teradata Employee

Re: CASE is a solution?

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.

Enthusiast

Re: CASE is a solution?

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

Junior Contributor

Re: CASE is a solution?

Can you show some sample data and expected result?