[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

How do I get specific values from datatable where certain fields match and the sum equals zero?

I have a vb.net datatable that has the following (pertinant) fields:
PK
Amt
AcctNbr
Source

I want to get the items where the AcctNbrs are equal, the Sources are equal and the Amts sum to zero (ie same account numbers & sources where the amounts are $5 and -$5) within the same datatable.

In other words, if I had the data in an actual table, I would do a select like this:
SELECT tbl.PK, tbl.AMT
FROM tbl, tbl AS tbl_1
WHERE tbl.ACCT_NBR=tbl_1.ACCT_NBR AND tbl.AMT+tbl_1.AMT=0 AND tbl.SOURCE=tbl_1.SOURCE

Unfortunately, all the data is already worked over and manipulated and is all within a single datatable.

So, my question is how can I get the data from my datatable?
Thanks in advance!
0
kevink34
Asked:
kevink34
  • 5
  • 2
1 Solution
 
kevink34Author Commented:
I should have added that I could use a filter and loop through each item but I am looking at a faster way because there may be thousands of records and time is important.
I am hoping for a simple way of using the datatable.select method or something like that.
0
 
tigin44Commented:
try this
SELECT tbl.PK, tbl.AMT
FROM tbl T1 INNER JOIN tbl T2 ON T1.ACCT_NBR = T2.ACCT_NBR AND Tl.SOURCE= T2.SOURCE
WHERE (T1.AMT + T2.AMT) = 0
0
 
kevink34Author Commented:
Thanks for the fast reply.

Where do I put that code?  Again, the data I have to search through is in a dataset.datatable and NOT on an actual table I can query from.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kevink34Author Commented:
Well, I did end up trying a for next loop:

dvItems.Sort = "PK ASC"
Dim dvTableCopy As DataView = dvItems
     For x As Int32 = 0 To dvItems.Count - 1
          dvTableCopy.RowFilter = "ACCT_NBR='" & dvItems(x).Item("ACCT_NBR") & _
                "' AND AMT=" & dvItems(x).Item("AMT") * -1 & _
                " AND PK>" & dvItems(x).Item("PK")
          If dvTableCopy.Count > 0 Then
               For y As Int32 = 0 To dvTableCopy.Count - 1
                   'These are the items I want
               Next y
    Next

I am still interested in a better, faster way if anyone can come up with one.
Thanks!
Kevin
0
 
tigin44Commented:
You can send the sql statement to the database by using a DBcommand and execute it...
That will give you what you want in a very short time period with compared to the method You did..
0
 
kevink34Author Commented:
Thank you.  Again, the issue I am having is that I already have data that has been manipulated and changed in my dataset.  That is the data I want to process.  I know I could go back to the table and get the data but the data that is in the table is different than in the dataset table, if that makes sense....

Thanks again for your help.
0
 
kevink34Author Commented:
It was not exactly what I was looking for.  I mentioned twice that the data was in a datatable already and I did NOT want to go back to the database on the server.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now