check duplicate value on six fields - if exists-not zero

Question

Post reply

check duplicate value on six fields - if exists-not zero

midan1

SSCertifiable

Points: 5100
More actions
March 16, 2007 at 6:21 pm

#117909

how to
check duplicate value on six fields - if exists- but not zero
on " fld1+fld2+fld3+fld4+fld5+fld6"
(not check zero)
like this
----------------
id        fld1   fld2     fld4    fld5    fld6
1       1         3        0          0       0                OK= pass
2       11       12      11        0        0                 not ok =duplicate value
3       56       67     98       0        0               OK= pass
4        54       0       0         65       54                 not ok =duplicate value
5       54      54      54         0       54                 not ok =duplicate value

--------------------------
TNX

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You forgot the 6th field in the sample data, but this will give you the right path to go down on :

USE SSC

GO

DECLARE @demo TABLE (id INT, f1 INT, f2 INT, f3 INT, f4 INT, f5 INT, Result VARCHAR(10))

INSERT INTO @demo (id, f1, f2, f3, f4, f5, Result)

SELECT 1, 1, 3, 0, 0, 0, 'PASS'

UNION ALL

SELECT 2, 11, 12, 11, 0, 0, 'FAIL'

UNION ALL

SELECT 3, 56, 67, 98, 0, 0, 'PASS'

UNION ALL

SELECT 4, 54, 0, 0, 65, 54, 'FAIL'

UNION ALL

SELECT 5, 54, 54, 54, 0, 54, 'FAIL'

SELECT 'Sample data' AS ResultName, * FROM @demo

SELECT 'FAILURES' AS ResultName, D.* FROM @demo D INNER JOIN

(

SELECT id, f FROM

(

SELECT id, f1 as f from @demo where F1 <> 0

UNION ALL

SELECT id, f2 from @demo where F2 <> 0

UNION ALL

SELECT id, f3 from @demo where F3 <> 0

UNION ALL

SELECT id, f4 from @demo where F4 <> 0

UNION ALL

SELECT id, f5 from @demo where F5 <> 0

&nbsp dtFs

GROUP BY ID, f

HAVING COUNT(*) > 1

) dtInvalids

ON D.id = dtInvalids.id

SELECT 'PASSES' AS ResultName, D.* FROM @demo D LEFT OUTER JOIN

(

SELECT id, f FROM

(

SELECT id, f1 as f from @demo where F1 <> 0

UNION ALL

SELECT id, f2 from @demo where F2 <> 0

UNION ALL

SELECT id, f3 from @demo where F3 <> 0

UNION ALL

SELECT id, f4 from @demo where F4 <> 0

UNION ALL

SELECT id, f5 from @demo where F5 <> 0

&nbsp dtFs

GROUP BY ID, f

HAVING COUNT(*) > 1

) dtInvalids

ON D.id = dtInvalids.id

WHERE dtInvalids.id IS NULL

Sergiy SSC Guru Points: 110208 More actions · Answer 2

midan1, you must perform such check on original set you posted there:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=352006,

before you spoil right table structure with pivoting.

_____________
Code for TallyGenerator

Ram Ram Ten Centuries Points: 1079 More actions · Answer 3

Midan1-

Simple way of getting the desired

DECLARE @demo TABLE (id INT, f1 INT, f2 INT, f3 INT, f4 INT, f5 INT)

INSERT INTO @demo (id, f1, f2, f3, f4, f5)

SELECT 1, 1, 3, 0, 0, 0

UNION ALL

SELECT 2, 11, 12, 11, 0, 0

UNION ALL

SELECT 3, 56, 67, 98, 0, 0

UNION ALL

SELECT 4, 54, 0, 0, 65, 54

UNION ALL

SELECT 5, 54, 54, 54, 0, 54

SELECT ID,F1,F2,F3,F4,F5,

CASE WHEN (SELECT COUNT(D1) FROM (SELECT F1 AS D1 UNION SELECT F2 AS D1 UNION SELECT F3 AS D1 UNION SELECT F4 AS D1 UNION SELECT F5 AS D1) AS CNT WHERE D1 <> 0)+

(SELECT COUNT(D1) FROM (SELECT F1 AS D1 UNION ALL SELECT F2 AS D1 UNION ALL SELECT F3 AS D1 UNION ALL SELECT F4 AS D1 UNION ALL SELECT F5 AS D1) AS CNT WHERE D1 = 0)= 5

THEN 'PASS' ELSE 'FAIL' END AS Remarks

FROM @DEMO

-Ram

midan1 SSCertifiable Points: 5100 More actions · Answer 4

is it possible to make all this as a "SQL Functions"

and use it inside select view

tnx for all

midan1 SSCertifiable Points: 5100 More actions · Answer 5

i test it it working

thank for all wonderful people here

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I strongly suggest you use Sriram's version as it outperforms mine by a mile. I would also strongly suggest that you change the table design so that you don't have to use that pivot at all.

To answer your question, yes it can be turned into a view (directly copy and paste). But this would be the very last solution I would use to solve that problem.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Ninja,

if you look at the link in my post you'll realise that this "6 column" table is a result of another pivot from properly designed table.

That's why I suggested not to do this at all but return to original table and start from there.

_____________
Code for TallyGenerator

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ninja's_RGR'us

SSC Guru

Points: 294069

March 18, 2007 at 6:13 am

#695605

Ya that makes more sens. Thanx for the hint .

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

-- Prepare sample data

DECLARE

@Sample TABLE (ID INT, Fld1 INT, Fld2 INT, Fld4 INT, Fld5 INT, Fld6 INT)

INSERT @Sample

SELECT 1, 1, 3, 0, 0, 0 UNION ALL

SELECT 2, 11, 12, 11, 0, 0 UNION ALL

SELECT 3, 56, 67, 98, 0, 0 UNION ALL

SELECT 4, 54, 0, 0, 65, 54 UNION ALL

SELECT 5, 54, 54, 54, 0, 54

SELECT ID,

Fld1,

Fld2,

Fld4,

Fld5,

Fld6,

CASE

WHEN (Fld1 > 0 AND Fld1 IN (Fld2, Fld4, Fld5, Fld6))

OR (Fld2 > 0 AND Fld2 IN (Fld1, Fld4, Fld5, Fld6))

OR (Fld4 > 0 AND Fld4 IN (Fld1, Fld2, Fld5, Fld6))

OR (Fld5 > 0 AND Fld5 IN (Fld1, Fld2, Fld4, Fld6))

OR (Fld6 > 0 AND Fld6 IN (Fld1, Fld2, Fld4, Fld5)) THEN 'not ok =duplicate value'

ELSE 'OK= pass'

END

FROM @Sample

N 56°04'39.16"
E 12°55'05.25"