Takamasa
asked on
LINQ to Dataset - Subquery
I am writing a query in LINQ and I am stuck with the subquery part.
Original SQL:
SELECT account, bal_date, ccy, balance
FROM Balance A
WHERE
A.balance < 0 AND
A.date = ( SELECT
MIN(B.bal_date)
FROM Balance B
WHERE balance < 0 AND
A.account = B.account AND
A.ccy = B.ccy
)
LINQ:
Dim query = From bal In dt.AsEnumerable() _
Where bal.Item("balance") < 0 _
And bal.Item(“bal_date”) = ( subquery? )
Select bal
I learned that if I do Grouping by account and currency, I can get the MIN (bal_date), but the I need the balance of such date, not the MIN(balance) or MAX(balance).
I would appreciate if I get some help from Experts...
Original SQL:
SELECT account, bal_date, ccy, balance
FROM Balance A
WHERE
A.balance < 0 AND
A.date = ( SELECT
MIN(B.bal_date)
FROM Balance B
WHERE balance < 0 AND
A.account = B.account AND
A.ccy = B.ccy
)
LINQ:
Dim query = From bal In dt.AsEnumerable() _
Where bal.Item("balance") < 0 _
And bal.Item(“bal_date”) = ( subquery? )
Select bal
I learned that if I do Grouping by account and currency, I can get the MIN (bal_date), but the I need the balance of such date, not the MIN(balance) or MAX(balance).
I would appreciate if I get some help from Experts...
What do you mean "balance of such date"? Can you give an example?
ASKER
Hi Ramprakash
Thank you for your comment.
I want to get the balance as of MIN(B.bal_date) - the result of the subquery.
Thank you for your comment.
I want to get the balance as of MIN(B.bal_date) - the result of the subquery.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ramprakash,
Thank you so much ! It worked.
Thank you so much ! It worked.