SQL test to check duplicates of a value in column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL test to check duplicates of a value in column

Author

Topic

ElFuego
Starting Member

1 Post

Posted - 2011-11-21 : 16:40:19

Hi All,

I have SQL DB consisting of 3 columns (Tuple, LCE_ID and GIC_CODE) and i would like to create SQL test which will check this DB and in case a value of GIC_CODE is found repeated with different LCE_ID values it should generate an error with the tuple value.

Example:
Tuple 4 (LCE_ID 579) and tuple 128 (LCE_ID 512) have same GIC group "80H".
Tuple 5 (LCE_ID 580) and tuple 129 (LCE_ID 513) have same GIC group "81H".

so it should generate an error as follows:
Test 1101 Error: duplicate GIC_CODE in tuples 4 and 128
Test 1101 Error: duplicate GIC_CODE in tuples 5 and 129

Initially i extracted the valid tuples as shown below and i need to run the test on this temp output (T_GIC).

//GET valid tuple FROM R_GIC_CODE
SELECT D_LCE_ID ,
D_GIC_CODE
FROM R_GIC_CODE
WHERE D_LCE_ID != 65535
AND raw_to_num(D_GIC_CODE) != 65535
INTO temp T_GIC;

Thanks in advance for your assistance

BR,
ElFuego

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 00:58:42

[code]
SELECT t.*
FROM R_GIC_CODE t
INNER JOIN (SELECT D_GIC_CODE
FROM R_GIC_CODE
GROUP BY D_GIC_CODE
HAVING COUNT(DISTINCT D_LCE_ID)>1
)t1
ON t1.D_GIC_CODE = t.D_GIC_CODE
ORDER BY t.Tuple,t.LCE_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources