Duplicate record count - SQL Server Forums

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

Duplicate record count

Author

Topic

NikishaDevi
Starting Member

4 Posts

Posted - 2014-07-16 : 06:53:49

I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working please help

SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0

IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-16 : 07:44:06

Well, for starters, you can't combine assigning a variable with retrieving columns. You're probably getting this error:

quote:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Why do you need the variable? Why not something like:


IF EXISTS (
    SELECT L1.AcctNo
         , L1.ReceivedFileID
    FROM  Legacy L1
        , Legacy L2
        , ReceivedFiles 
    WHERE L1.ReceivedFileID = ReceivedFiles.ReceivedFileID 
      AND L1.AcctNo=L2.AcctNo 
    GROUP BY L1.AcctNo,L1.ReceivedFileID 
    HAVING COUNT(*)> 1
)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END

NikishaDevi
Starting Member

4 Posts

Posted - 2014-07-16 : 07:59:31

how to check the @status value in SP in console in order to see the result.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-07-16 : 08:18:58

You can use SELECT statement to return the result

SELECT @status

Madhivanan

Failing to plan is Planning to fail

NikishaDevi
Starting Member

4 Posts

Posted - 2014-07-17 : 03:25:16

quote:
Originally posted by NikishaDevi

how to check the @status value in SP in console in order to see the result.

IF EXISTS (
SELECT L1.AcctNo
, L1.ReceivedFileID
FROM Legacy L1
, Legacy L2
, ReceivedFiles
WHERE L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
AND L1.AcctNo=L2.AcctNo
GROUP BY L1.AcctNo,L1.ReceivedFileID
HAVING COUNT(*)> 1
)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END

I have tried with above query but no luck .i am getting null result eventhough have duplicate records in table
i need to put each duplicate record count in a variable.i need to check whether that variable consists of duplicate records or not.

As i new to SP,Please help me

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-17 : 08:08:52

Why do you need the duplicate count in a variable? What will you do with the variable after it is assigned? Also, how do you define duplicates in your case?

For a simple example:


declare @t table (a int, b int, c int)

insert into @t values 
	(1,2,3),
	(1,2,3)

select a, b, count(*) as duplicates from @t
group by a, b
having count(*) > 1

produces:


a	b	duplicates
1	2	2

and with the same table variable


if exists (
	select a, b from @t
	group by a, b
	having count(*) > 1
	)

	print 'duplicates exist'

produces:


(1 row(s) affected)
duplicates exist

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources