Referencing another table on the inner join  in a where part of LINQ query

Anthony Matovu
Anthony Matovu used Ask the Experts™
on
Dear Experts

I have the code below
But in the last query that starts with "  Dim bund As IEnumerable" it is complaining "wallet_type_cd is not in datatable bund",  it is true it is in datatable wallets

How do i point to datatable wallets;

Thank you

  odadp = New OracleDataAdapter("select msisdn_nsk as msisdn, regexp_substr(flex_3_txt,'[^~]+',1,1) as byts, ded_acc_id from " & _
                                      " stg_cdr.cs5_ccn_wmx_da where date_key = 20180801 " & _
                                      " AND (REGEXP_SUBSTR(FLEX_3_TXT,'[^~]+',1,1)) <> 'X' " & _
                                      " AND (REGEXP_SUBSTR(FLEX_2_TXT,'[^~]+',1,7)) <> 'X' " & _
                                      " AND (REGEXP_SUBSTR(FLEX_2_TXT,'[^~]+',1,7)) IS NOT NULL  " & _
                                      " AND (REGEXP_SUBSTR(FLEX_2_TXT,'[^~]+',1,13)) = '3333' and rownum <= 10000", conn)
        odadp.Fill(udat, "bund")

        odadp = New OracleDataAdapter("select wallet_type_cd, wallet_cd,wallet_key from bib.dim_wallet", conn)
        odadp.Fill(udat, "wallets")

        Dim wallets As IEnumerable = (From dr In udat.Tables("wallets").AsEnumerable() Select dr).ToList

        Dim bund As IEnumerable = (From dr In udat.Tables("bund").AsEnumerable() Join wallet In udat.Tables("wallets").AsEnumerable() _
                                  On dr.Field(Of String)("ded_acc_id") Equals wallet.Field(Of String)("wallet_cd")
                     Group dr By a = dr.Field(Of Decimal)("msisdn") Into grp = Group Select New With
                            { _
                                .msisdn = a, _
                                .data_bytes_bundle = grp.Sum(Function(x) If( _
                                   Not (x.Field(Of String)("wallet_type_cd").Contains("PROMO") _
                                   Or x.Field(Of String)("wallet_type_cd").Contains("BONUS") _
                                   Or x.Field(Of String)("wallet_type_cd").Contains("LOYALTY") _
                                   Or x.Field(Of String)("wallet_type_cd").Contains("FREE")), x.Field(Of String)("byts"), 0)),
                                .data_bytes_bonus = grp.Sum(Function(x) If( _
                                   x.Field(Of String)("wallet_type_cd").Contains("PROMO") _
                                   Or x.Field(Of String)("wallet_type_cd").Contains("BONUS") _
                                   Or x.Field(Of String)("wallet_type_cd").Contains("LOYALTY") _
                                   Or x.Field(Of String)("wallet_type_cd").Contains("FREE"), x.Field(Of String)("byts"), 0))
                            }).ToList
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial