Hi Friends,

There is a table with two columns containing a long account number and a short account number. like this:
LongAcctNum ShortAcctNum
A00146000 A00146
A00146000 1000146000
Q78886001 Q78886
00351250246 351250246

Each long account number should be having one short account number. But some have two. If I have to pick out such long acct. numbers, what kind of query should I write? Can I use a self-join on this table, something like:
select LongAcctNum, ShortAcctNum from table1 t1 where t1.LongAcctNum = t1.LongAcctNum and t1.ShortAcctNum != t1.ShortAcctNum.

I tried this way; it doesn't work. Can my friends here please correct me?

Thanks!

An easier way is to let DB2 count them for you. Like this:

with temp1 (longAcctnum,quantity) as (
select longacctnum,count(*) from table1 group by longacctnum)
select tp1.longacctnum,t1.shortacctnum from temp1 as tp1 inner join table1 as t1 on (tp1.longacctnum = t1.longacctnum) where tp1.quantity > 1

HTH

Andy

Hi Andy,

Thanks for the reply. Can this be done without using temporary tables?

That is not a temporary table. It is a common-table-expression. I just named it temp1.

Andy

Oh, ok thanks. I am new to db2. I have earlier worked with Sybase and hadn't come accross common-table-expressions.
Thank You for your response.

This is no self join: you must specify the table twice in the "from" condition for it to be a self join, e.g.:

This is a CTE (common table expression). Not available in DB2 prior to version 8.
The same result can be obtained with

On LUW, it has been available atleast from V7 ... On ZOS, CTEs were not introduced till V8

Sathyaram