I am not completely sure how to solve this problem.
My data looks like this:
ENT ACC L_TYPE K_TYPE 0100 123 660 S 0100 123 673 S
And I would like to see whether L_TYPE 673 has no correct correspondent with L_TYPE = 660.
I want to find out whether the column L_TYPE with the value 673 has also its correspondent 660. All the other values do not change.
However, I would need the SQL as general as possible.
The only thing that I was capable of writing was this. So, I was able to find out that at least many rows with different values are in L_TYPE.
SELECT ENT, ACC, COUNT(*) FROM TABLE WHERE K_TYPE = 'S' GROUP BY ENT, ACC HAVING COUNT(*)>1;
I thought about using a cursor but I am not quite sure whether this solves my problem.
Maybe somebody knows how to solve this problem? I use DB2 as a database.
Advertisement
Answer
To get the rows for which every ENT, ACC
combination there are L_TYPE of both 600 and 673.
SELECT ENT, ACC, COUNT(*) FROM TABLE WHERE K_TYPE = 'S' and l_type in (660,673) GROUP BY ENT, ACC HAVING COUNT(*)>1;