Solved

problem with Sql query

Posted on 2013-06-24
4
279 Views
Last Modified: 2013-06-24
Im trying to update a field in the table where I'm joining the table to an aggregation of itself. The problem is that im getting a cartesian product, rather than an Inner join.

can somebody point me to the right direction?

UPDATE dbo.tblBNKRecon
SET dbo.tblBNKRecon.GroupedPmtID = GRPPMTID
From (SELECT tblBNKRecon.GroupedPmtID, tblBNKRecon.BAITypeCode, tblBNKRecon.Status, tblBNKRecon.NewDesc, tblBNKRecon.ID, tblBNKRecon.BankAccount, tblBNKRecon.BankDate
FROM tblBNKRecon INNER JOIN tblBNK_GRP_ENT ON tblBNKRecon.BankAccount = tblBNK_GRP_ENT.ACCT
WHERE (((tblBNKRecon.BAITypeCode)='169') AND ((tblBNKRecon.Status)='LD') AND ((tblBNKRecon.NewDesc) Is Not Null) AND ((tblBNK_GRP_ENT.GroupedPmts)=1)))as a
inner join (
select MIN(ID) as minimalID, NewDesc, BankAccount, BankDate, 
COALESCE(CONVERT(VARCHAR, MONTH(GETDATE())), '') +
COALESCE(CONVERT(VARCHAR, DAY(GETDATE())), '')+
COALESCE(CONVERT(VARCHAR, MIN(ID)), '') as GRPPMTID

from  dbo.tblBNKRecon

group by NewDesc, BankAccount, BankDate) as b
on a.NewDesc = b.NewDesc and
a.BankAccount = b.BankAccount and 
a.BankDate = b.BankDate

Open in new window

0
Comment
Question by:damixa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39273154
All I can suggest here without having access to your data is that your JOIN on a.NewDesc = b.NewDesc and a.BankAccount = b.BankAccount and a.BankDate = b.BankDate is not producing the UNIQUE combination.

I suggest you do the following: Run these two queries separate:

SELECT tblBNKRecon.GroupedPmtID, tblBNKRecon.BAITypeCode, tblBNKRecon.Status, tblBNKRecon.NewDesc, tblBNKRecon.ID, tblBNKRecon.BankAccount, tblBNKRecon.BankDate
FROM tblBNKRecon INNER JOIN tblBNK_GRP_ENT ON tblBNKRecon.BankAccount = tblBNK_GRP_ENT.ACCT
WHERE (((tblBNKRecon.BAITypeCode)='169') AND ((tblBNKRecon.Status)='LD') AND ((tblBNKRecon.NewDesc) Is Not Null) AND ((tblBNK_GRP_ENT.GroupedPmts)=1))

Open in new window


select MIN(ID) as minimalID, NewDesc, BankAccount, BankDate, 
COALESCE(CONVERT(VARCHAR, MONTH(GETDATE())), '') +
COALESCE(CONVERT(VARCHAR, DAY(GETDATE())), '')+
COALESCE(CONVERT(VARCHAR, MIN(ID)), '') as GRPPMTID
from  dbo.tblBNKRecon
group by NewDesc, BankAccount, BankDate

Open in new window


Check first of all that the queries themselves are not returning duplicate entries. Check that the number of rows match (they may not match, depending on your data). If you have not many rows you will be able to compare the results side-by-side.

Then, you need to run this query and check that the cnt column equals 1:
SELECT NewDesc, BankAccount, BankDate, count(*) as cnt
FROM tblBNKRecon
GROUP BY NewDesc, BankAccount, BankDate

Open in new window


If it is not you will be updating duplicate entries with the same GroupedPmtID value (probably it is also fine, depending on your ).

If possible, can you upload some fake data to SQLFiddle, or Access database, so that we could check it
0
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 39273158
Forget all that I've said. I think I found the problem. You do not join tblBNKRecon to the two subqueries. So, just do this, and I think you'll be fine:

UPDATE dbo.tblBNKRecon
SET dbo.tblBNKRecon.GroupedPmtID = GRPPMTID
From dbo.tblBNKRecon INNER JOIN
(SELECT tblBNKRecon.GroupedPmtID, tblBNKRecon.BAITypeCode, tblBNKRecon.Status, tblBNKRecon.NewDesc, tblBNKRecon.ID, tblBNKRecon.BankAccount, tblBNKRecon.BankDate
FROM tblBNKRecon INNER JOIN tblBNK_GRP_ENT ON tblBNKRecon.BankAccount = tblBNK_GRP_ENT.ACCT
WHERE (((tblBNKRecon.BAITypeCode)='169') AND ((tblBNKRecon.Status)='LD') AND ((tblBNKRecon.NewDesc) Is Not Null) AND ((tblBNK_GRP_ENT.GroupedPmts)=1)))as a
ON a.NewDesc = dbo.tblBNKRecon.NewDesc and
a.BankAccount = dbo.tblBNKRecon.BankAccount and 
a.BankDate = dbo.tblBNKRecon.BankDate
inner join (
select MIN(ID) as minimalID, NewDesc, BankAccount, BankDate, 
COALESCE(CONVERT(VARCHAR, MONTH(GETDATE())), '') +
COALESCE(CONVERT(VARCHAR, DAY(GETDATE())), '')+
COALESCE(CONVERT(VARCHAR, MIN(ID)), '') as GRPPMTID
from  dbo.tblBNKRecon
group by NewDesc, BankAccount, BankDate) as b
on a.NewDesc = b.NewDesc and
a.BankAccount = b.BankAccount and 
a.BankDate = b.BankDate

Open in new window

0
 

Author Comment

by:damixa
ID: 39273169
Amazing, thanks
0
 

Author Closing Comment

by:damixa
ID: 39273170
Much appreciated
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question