MVSFORUMS.com

[Catherine]

Hi,

I have got 2 tables

Table Emp: ID Primary key

[Nic Clouston]

1) What is TRNS_TY which is not shown on your table
2) What is the result that you ARE getting?
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

[Catherine]

Nic,

Sorry thats B.TRNS instead of B.TRNS_TY

SELECT A.ID, A.NAME, B.TRNS
FROM EMP A, TRANSACTION B
WHERE A.ID = B.ID
GROUP BY A.ID, A.NAME, B.TRNS
HAVING COUNT(B.TRNS) = 1

Result

1 A CR
1 A DB
2 B CR
3 C CR
3 C DB
4 D CR

[Nic Clouston]

well - I can see that b.trns has only 1 'cr' and/or only 1 'dr' for each id so count will always be 1 - try count(b.id) = 1
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

[Catherine]

Nic,

I get the same result

[vkphani]

Try this untested SQL:

[kolusu]

catherine,

The reason your sql did not work is that you are grouping by 3 columns (id,name & trns) By grouping like that you will have more unique rows. If your intention is to get single rows from transaction table then you need the group by on just the ID column.

Try this

[kolusu]

[Catherine]

Kolusu,

I just want to confirm this

The output of this Query
SELECT A.ID, A.NAME, B.TRNS
FROM EMP A, TRANSACTION B
WHERE A.ID = B.ID
GROUP BY A.ID, A.NAME, B.TRNS

will be

1 A CR
1 A DB
2 B CR
3 C CR
3 C DB
4 D CR

When i add a a Having clause
HAVING COUNT(TRNS) = 1, it gives all the records as above because
TRNS has 2 unique values CR & DB

But if i give HAVING COUNT(ID) = 1,

SELECT A.ID, A.NAME, B.TRNS
FROM EMP A, TRANSACTION B
WHERE A.ID = B.ID
GROUP BY A.ID, A.NAME, B.TRNS
HAVING COUNT(ID) = 1

I should have got the rows where ID IS UNIQUE ie

2 B CR
4 D CR


Does the DB2 consider the TRNS in the Group by clause, even though i give only count(ID)?

[kolusu]

Catherine,

The HAVING clause is derived from the GROUP by clause, so even though you mentioned the COUNT(ID) it is going to check based on the Group by clause

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu