how to identify whether duplicate identity column values existing on a
table or not.

i have four tables and all the table have row_sk defined a identity
column, the start and end values are different acroos all the tables.

i am creating a union all view on all the tables

i am getting this error SQL0415N

On Oct 11, 1:44 pm, srikir...@gmail.com wrote:
> how to identify whether duplicate identity column values existing on a
> table or not.
>
> i have four tables and all the table have row_sk defined a identity
> column, the start and end values are different acroos all the tables.
>
> i am creating a union all view on all the tables
>
> i am getting this error SQL0415N

Not sure which issue you're actually struggling with, SQL0415N or
trying to find duplicates....

Anyway, in a UNION, all result sets must be union-compatible (same
number of attributes and same datatypes). Ensure that you're not
trying, e.g., to stack an integer column on top of a character one. As
to finding duplicate values, there are several well-known techniques,
so if you are, in fact, struggling with that, I can forward some
queries to you that will do the trick.

--Jeff

srikirthi@gmail.com wrote:
> how to identify whether duplicate identity column values existing on a
> table or not.
>
> i have four tables and all the table have row_sk defined a identity
> column, the start and end values are different acroos all the tables.
>
> i am creating a union all view on all the tables
>
> i am getting this error SQL0415N
>
This should spit out all the dups and where they live (untested)
SELECT tabname, id FROM
(SELECT COUNT(1) OVER(PARTITION BY id) AS numids, id
FROM (SELECT id, 'T1' FROM T1 UNION ALL SELECT id, T2 FROM T2 ....)
AS X(id, tabname)) AS Y
WHERE numids > 1

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:

> This should spit out all the dups and where they live (untested)
> SELECT tabname, id FROM
> (SELECT COUNT(1) OVER(PARTITION BY id) AS numids, id
> FROM (SELECT id, 'T1' FROM T1 UNION ALL SELECT id, T2 FROM T2 ....) AS
> X(id, tabname)) AS Y
> WHERE numids > 1
>

Will this be more efficient than the more traditional:

select id, count(*)
from (SELECT id, 'T1' FROM T1
UNION ALL
SELECT id, 'T2' FROM T2 ....) AS X(id, tabname)
group by id
having count(*) > 1


Thanks,



Ian

Ian wrote:
> Serge Rielau wrote:
>
>> This should spit out all the dups and where they live (untested)
>> SELECT tabname, id FROM
>> (SELECT COUNT(1) OVER(PARTITION BY id) AS numids, id
>> FROM (SELECT id, 'T1' FROM T1 UNION ALL SELECT id, T2 FROM T2 ....)
>> AS X(id, tabname)) AS Y
>> WHERE numids > 1
>>
>
> Will this be more efficient than the more traditional:
>
> select id, count(*)
> from (SELECT id, 'T1' FROM T1
> UNION ALL
> SELECT id, 'T2' FROM T2 ....) AS X(id, tabname)
> group by id
> having count(*) > 1
The "more traditional" won't give you the tables where the dups are... :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab