All Forums General
cristi_1981 5 posts Joined 09/13
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.

cristi_1981 5 posts Joined 09/13
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.

yuvaevergreen 93 posts Joined 07/09
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

dnoeth 4628 posts Joined 11/04
05 Jun 2015

Can you show some sample data and expected result?

Dieter

You must sign in to leave a comment.