check duplicate value on six fields - if exists-not zero
-
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
-
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
 
dtFsGROUP 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
 
dtFsGROUP BY ID, f
HAVING COUNT(*) > 1
) dtInvalids
ON D.id = dtInvalids.id
WHERE dtInvalids.id IS NULL
-
midan1, you must perform such check on original set you posted there:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=352006,
before you spoil right table structure with pivoting.
_____________
Code for TallyGenerator -
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
-
is it possible to make all this as a "SQL Functions"
and use it inside select view
tnx for all
-
i test it it working
thank for all wonderful people here
-
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.
-
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 -
Ya that makes more sens. Thanx for the hint
. -
-- 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"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply