Link to home
Start Free TrialLog in
Avatar of kevink34
kevink34Flag for United States of America

asked on

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!
Avatar of kevink34
kevink34
Flag of United States of America image

ASKER

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.
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
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.
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
ASKER CERTIFIED SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.