kevink34
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_NB R 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!
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_NB
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!
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
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
ASKER
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.
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.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks again for your help.
ASKER
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.
ASKER
I am hoping for a simple way of using the datatable.select method or something like that.