0

I have three fields in a table named table_family.

One is "relation" it has 3 kinds of values 1 for person, 2 for parents, 3 for son.

Other field is "age" and it contains the ages of all the relations in the first table.

The 3rd field is for the family number.

Now the relation could be repeated like four sons and two parents for the same family This is an example:

Family   Relation   Age

1         1          25

1         2          40

1         2          35

1         3          30

1         3          28

1         3          29

2         1          30

2         2          40

2         3          12

Now I want to build a report that displays an error which is: Whenever the difference between parent (relation=2) and son (relation=3) is less than 12, then this is an error.

I tried this code :

select family from table_family
group by family
having
SELECT  family

FROM table_FAMILY 

GROUP BY 

     family
HAVING
((
 MAX(DECODE ( relation,2,age ))  
-  MAX(DECODE ( relation,3,age ))
 ) 
 <12 )
 OR
 (
 (SELECT MIN(age) FROM table_FAMILY WHERE relation=2) -(SELECT MIN(age) FROM       table_FAMILY WHERE relation=3) < 12

 )

If I use this code then only the maximum and minimum values will be compared. The values in between will be ignored but I need them to be compared in the same way.

The output report will be something like:

family  difference between parent and son

1               10

1               12

1               11

1                5

1                7

1                6

This what I need in the final report

For family = 1 I subtracted parent age - son age =

40-30=10

40-28=12

40-29=11

35-30=5

35-28=7

35-29=6

But for the 2nd family

40-12= 28 >12 then should not be listed (not an error).

Any help please?

  • WITH p AS (SELECT fm, age parent_age FROM f WHERE rel = 2 ) , c AS (SELECT fm, rel, age child_age FROM f WHERE rel = 3) SELECT p.fm, p.parent_age,c.child_age, p.parent_age-c.child_age age_diff FROM p JOIN c ON (p.fm = c.fm) WHERE (p.parent_age-c.child_age) <=12 I am sure it can be simplified further – Raj Dec 17 '14 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.