General

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-04-2015
01:42 AM

06-04-2015
01:42 AM

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.

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-04-2015
02:20 AM

06-04-2015
02:20 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-05-2015
02:09 AM

06-05-2015
02:09 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-05-2015
04:25 AM

06-05-2015
04:25 AM

Can you show some sample data and expected result?

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.