Query bringing back too many records

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

Query bringing back too many records

Author

Topic

Damian39
Starting Member

33 Posts

Posted - 2015-05-01 : 13:28:31

Hello all,
I'm written a query to pull adjustments made on customer accounts, and I am attempting to pull in the payment associated with the adjustments. I'm using a single account to test my query and somewhere in my final output select statement I'm doing something that is bringing me back more records than I want. The code I have written is:


IF OBJECT_ID('TEMPDB..#TMP1O') IS NOT NULL DROP TABLE #TMP1O

select a.batch
,a.acctcorp
,a.house
,a.cust
--,RIGHT (REPLICATE ('0',5) + CONVERT(VARCHAR(5),A.acctcorp),5) + 
--RIGHT (REPLICATE ('0',6) + CONVERT(VARCHAR(6),A.House),6) + 
--RIGHT (REPLICATE ('0',2) + CONVERT(VARCHAR(2),A.Cust),2) as Account_Number
--,a.batch as payment_batch
,a.source_update_date
,a.batype
,a.amount 
,a.DOCSEQ
,a.pmtkind
,a.adjrsn

into #tmp1o

from bsddp.hst.Idst_batch_detail a with (nolock)


--where a.SOURCE_UPDATE_DATE < = b.SOURCE_UPDATE_DATE 
where a.SOURCE_UPDATE_DATE > = '01-01-2015'
and a.BATYPE = 'a'
and a.ADJRSN in ('PU')--,'XP')
--and a.PMTKIND not in ('1','3')
and a.ACCTCORP = '1624'
and a.HOUSE = '914034'
and a.CUST = '4'
and a.ACCTCORP in (1105,1624,1636,1638,1641,1643,1710,1719,1733,9587,15515,19204)

group by  a.batch
,a.acctcorp
,a.house
,a.cust
,a.source_update_date 
,a.batype 
,a.amount 
,a.DOCSEQ
,a.pmtkind
,a.adjrsn

select distinct b.batch as payment_batch
,a.batch as adjustment_batch
,b.acctcorp
,b.house
,b.cust
--,a.batch as payment_batch
,a.source_update_date as adjustment_date 
,b.SOURCE_UPDATE_DATE as payment_date
,b.batype as transaction_type
--,case when b.batype = 'P' then 'Payment' else 'Adjustment'
--end as 'batype'
,a.amount as adjustment_amount
,max(b.amount) as payment_amount
,a.DOCSEQ
,b.pmtkind
,a.adjrsn

from #tmp1o as a (nolock) inner join bsddp.hst.Idst_batch_detail as b (nolock)
on		a.acctcorp	=	b.ACCTCORP
and		a.house		=	b.HOUSE
and		a.cust		=	b.CUST

where b.source_update_date <= a.SOURCE_UPDATE_DATE
and b.SOURCE_UPDATE_DATE >= '04-01-2014'
--and b.BATYPE = 'p'

group by  (b.batch)
,a.batch
,b.acctcorp
,b.house
,b.cust
,a.source_update_date 
,b.SOURCE_UPDATE_DATE
,b.batype
,a.amount 
,b.amount 
,a.DOCSEQ
,b.pmtkind
,a.adjrsn

order by b.ACCTCORP, b.HOUSE, b.CUST, b.SOURCE_UPDATE_DATE,a.DOCSEQ

I've tried making changes within my where statement and parameters, but so far I've come up emtpy. I was hoping someone may be able to take a look at this code and let me know if I am missing something or have something in the wrong place. I appreciate any assistance anyone can offer. Thanks!

Damian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-01 : 13:30:01

Please read through this, which will show you how to post your question so that we can help you figure the issue out: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-01 : 13:45:59

For starters, remove 'WITH (NOLOCK)'. Since your query concerns payments, the LAST thing you want is dirty reads.

Damian39
Starting Member

33 Posts

Posted - 2015-05-01 : 14:06:29

Hello Tara,
Thank you for the link, but since I have been unable to get the output I'm looking for from my query, I cannot provide a visual example of what I am looking for. I'm also unfamiliar with all of the capabilities of this forum, so I am unsure as to how I would show you what I am trying to achieve rather than explaining it within the body of the post.

quote:
Originally posted by tkizer

Please read through this, which will show you how to post your question so that we can help you figure the issue out: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Damian

Damian39
Starting Member

33 Posts

Posted - 2015-05-01 : 14:08:00

Hello gbritton,
I cannot remove the (nolock) within my query, because there are many people that access the tables I'm pulling from, and I cannot have my query locking up the table while it runs.

quote:
Originally posted by gbritton

For starters, remove 'WITH (NOLOCK)'. Since your query concerns payments, the LAST thing you want is dirty reads.

Damian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-01 : 14:08:27

I think you are missing the point of what we need. We need SAMPLE data that illustrates the issue and expected output. The data does not need to be real. SAMPLE data only plus expected result set when using that sample data. You perhaps need to take a step back and remove any portions of your query that are not essential to the issue so that we can help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-01 : 14:16:54

quote:
Originally posted by Damian39

Hello gbritton,
I cannot remove the (nolock) within my query, because there are many people that access the tables I'm pulling from, and I cannot have my query locking up the table while it runs.

quote:
Originally posted by gbritton

For starters, remove 'WITH (NOLOCK)'. Since your query concerns payments, the LAST thing you want is dirty reads.

Damian

You misunderstand locking. the default is Read Committed, which

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

This is what you want. Otherwise you may show a payment that is incorrect, about to be changed or even deleted. You really, really don't want that.

NOLOCK is considered bad practice except for DBAs who really, really know what they are doing and understand the consequences.

Snapshot isolation is a better alternative for reducing blocking. Never NOLOCK, NEVER!!!!!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-05-01 : 15:45:35

Using aggregate function (max) on field grouped by (b.amount), doesn't make any sence.
Also, do either distinct or group by - not both.
Oh yeah - nolock is no go (like I told you before and gbritton just told you).

Damian39
Starting Member

33 Posts

Posted - 2015-05-01 : 17:31:52

Thank you all for your input. I managed to get it to work by adding a couple more temp tables to the query.

Damian

Kristen
Test

22859 Posts

Posted - 2015-05-02 : 03:35:30

quote:
Originally posted by Damian39

I cannot remove the (nolock) within my query, because there are many people that access the tables I'm pulling from, and I cannot have my query locking up the table while it runs.

You ABSOLUTELY (Sorry about the caps!) need to remove NOLOCK.

It is the most widely abused means of trying to improve performance and prevent blocking that I know of. I dread to think of the money / time / business opportunities that have been lost - perhaps whole companies too

- because of that.

You will get some rows repeated twice, some missing all together. Is that what you want?

It happens VERY rarely, it is impossible to repeat - so when a user tells you it has happened you won't be able to see it / believe them (assuming they spotted it before the payments went out of the door) .... but it WILL happen.

If NOLOCK is used widely in the code in your shop they ALL need to be removed.

Look at setting the database to READ_COMMITTED_SNAPSHOT instead. (Almost certainly you can just set that and remove all the NOLOCKS, but there is a slim chance that some of your existing code may behave differently - that circumstance is rare though, but you can GOogle for what the situations are and decide if that is likely to effect the logic in any of your code)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources