Forum Discussion
Dynamic Where Statement for ODBC Connection
Situation: I am using a SQL query via an ODBC connection to get transaction level account information at a fairly large bank so 1 month of data is tens of millions of rows for all accounts at this bank andI'm unable to run it as I get a "No more spool space" error.
I just need transaction information for a subset of about 7 accounts and I have the list of all those account numbers. I'm able to hardcode these account numbers in the where clause and that works, however, the list of accounts will continue to grow so in the future I will have to manually add the account numbers. My initial plan was to use an excel doc on a shared drive where the operations staff can add the new accounts and join that excel doc to the transaction table to filter down the results to just those accounts automatically. This doesn't work though since I initially need to load all accounts before making that merge. Unfortunately there is no field within the object I'm pulling from that identifies the accounts that I need. I only know which accounts to select with information from the product manager.
Is there any way where I reference the list of account numbers in the where clause from the excel list i have, or is the only option to hard code them in every so often?
2 Replies
show your M code for the ODBC part. This should be possible with query parameters, potentially at the cost of losing query folding.
So I hard coded the account numbers but also joined to the table with the account numbers in case that helps
let Source = Odbc.Query("dsn=Teradata-Prod", "SELECT#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.ACCT_NBR,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_APPL,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.SEQ_NBR,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DATAYM_NBR,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.ACCT_ID,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.BANK_NBR,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_INST,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_UPD_DATE,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_UPD_TIME,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_UPD_USER,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_UPD_ORG,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TRIND,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TRTOD,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TRITEMS,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_DIND,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TRSOURCE,#(lf) DEPOSITREP.F_DPS_DEPOSIT_HIST_EOM.DPDDM_MTYPE,#(lf) count(DEPOSITREP.F_DPS_DEPOSIT_HIST_EOM.DPDDM_ACCOUNT),#(lf) DEPOSITREP.F_DPS_DEPOSIT_HIST_EOM.DPDDM_MSTATUS,#(lf) DEPOSITREP.F_DPS_DEPOSIT_HIST_EOM.DPDDM_MDORM,#(lf) DEPOSITREP.F_DPS_DEPOSIT_HIST_EOM.DPDDM_MANALYSIS,#(lf) Cast(DEPOSITREP.F_DPS_DEPOSIT_HIST_EOM.CTR_ID as Integer),#(lf) REFERENCE_BU.STND_CORP_HRCHY_CRNT_CDM.CST_CNTR_NM,#(lf) REFERENCE_BU.STND_CORP_HRCHY_CRNT_CDM.MKT_NM,#(lf) REFERENCE_BU.STND_CORP_HRCHY_CRNT_CDM.SPU_NM,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_ACCOUNT,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TDDESC_1,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TDDESC_2,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_SEQUENCE,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TRAMT,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TRSEQ,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TREXTC,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TRPROCDT,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TREFFDT,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.TRANS_TYPE_ID,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TRINIT,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TRITC,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.ORIGIN_CD,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_DATE,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DATA_DT,#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TRSERIAL#(lf)FROM#(lf) REFERENCE_BU.STND_CORP_HRCHY_CRNT_CDM RIGHT OUTER JOIN DEPOSITREP.F_DPS_DEPOSIT_HIST_EOM ON (REFERENCE_BU.STND_CORP_HRCHY_CRNT_CDM.CST_CNTR_CD=SUbstr(DEPOSITREP.F_DPS_DEPOSIT_HIST_EOM.CTR_ID,2,4))#(lf) INNER JOIN DEPOSITREP.F_CHKG_TRAN_HIST_EOM ON (DEPOSITREP.F_CHKG_TRAN_HIST_EOM.ACCT_NBR=DEPOSITREP.F_DPS_DEPOSIT_HIST_EOM.ACCT_NBR)#(lf) #(lf)WHERE#(lf) (#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DATAYM_NBR >= 202004#(lf) AND#(lf) DEPOSITREP.F_DPS_DEPOSIT_HIST_EOM.DPDDM_MTYPE IN ( 2, 3, 120, 123, 124, 127, 128, 130, 150, 160, 164, 170, 173, 174, 180, 183, 184, 193, 200, 340, 341, 409, 412, 415, 427, 440, 443, 445, 450, 452, 453, 460, 470, 473, 480, 483, 490, 493, 166, 171, 172, 175, 182, 410, 411, 422, 432, 437 )#(lf) AND#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_ACCOUNT IN (581xxx,1133xx,74099xxx,3452xxxx,1310xxx,6206xxx,68302xxx,5290xxxx)#(lf) AND#(lf) DEPOSITREP.F_CHKG_TRAN_HIST_EOM.DPST2_TREXTC NOT IN ( 4876, 5686 )#(lf) AND#(lf) REFERENCE_BU.STND_CORP_HRCHY_CRNT_CDM.SPU_NM IN ( 'Commercial Bank' )#(lf) )#(lf)GROUP BY#(lf) 1, #(lf) 2, #(lf) 3, #(lf) 4, #(lf) 5, #(lf) 6, #(lf) 7, #(lf) 8, #(lf) 9, #(lf) 10, #(lf) 11, #(lf) 12, #(lf) 13, #(lf) 14, #(lf) 15, #(lf) 16, #(lf) 17, #(lf) 19, #(lf) 20, #(lf) 21, #(lf) 22, #(lf) 23, #(lf) 24, #(lf) 25, #(lf) 26, #(lf) 27, #(lf) 28, #(lf) 29, #(lf) 30, #(lf) 31, #(lf) 32, #(lf) 33, #(lf) 34, #(lf) 35, #(lf) 36, #(lf) 37, #(lf) 38, #(lf) 39, #(lf) 40, #(lf) 41#(lf)"), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"ACCT_NBR", Int64.Type}}), #"Merged Queries1" = Table.NestedJoin(#"Changed Type1", {"ACCT_NBR"}, #"Prefer Pay Clients", {"Account Number"}, "Prefer Pay Clients", JoinKind.LeftOuter), #"Expanded Prefer Pay Clients" = Table.ExpandTableColumn(#"Merged Queries1", "Prefer Pay Clients", {"Program Name"}, {"Prefer Pay Clients.Program Name"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Prefer Pay Clients",{"ACCT_NBR", "Count(DPDDM_ACCOUNT)", "DPDDM_MANALYSIS", "DPST2_ACCOUNT", "DPST2_TDDESC_1", "DPST2_TDDESC_2", "DPST2_SEQUENCE", "DPST2_TRAMT", "DPST2_TRSEQ", "DPST2_TREXTC", "DPST2_TRPROCDT", "DPST2_TREFFDT", "TRANS_TYPE_ID", "DPST2_TRITC", "DPST2_DATE", "DATA_DT"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"ACCT_NBR", "DPDDM_MANALYSIS", "DPST2_ACCOUNT", "DPST2_TDDESC_1", "DPST2_TDDESC_2", "DPST2_SEQUENCE", "DPST2_TRAMT", "DPST2_TRSEQ", "DPST2_TREXTC", "DPST2_TRITC", "DPST2_TRPROCDT", "DPST2_TREFFDT", "TRANS_TYPE_ID", "DPST2_DATE", "DATA_DT"}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"DPST2_TRITC", type text}, {"DPST2_TREXTC", type text}}, "en-US"),{"DPST2_TRITC", "DPST2_TREXTC"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"DPST2_ACCOUNT"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ACCT_NBR", "Account Number"}, {"DPST2_TDDESC_1", "Description 1"}, {"DPST2_TDDESC_2", "Description 2"}, {"DPST2_TRAMT", "Transaction Amount"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Merged"}, Mapping, {"Merged"}, "Mapping", JoinKind.LeftOuter), #"Expanded Mapping" = Table.ExpandTableColumn(#"Merged Queries", "Mapping", {"Merged", "DR_CR_CODE_1", "SC Description"}, {"Merged.1", "DR_CR_CODE_1", "SC Description"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Mapping", each ([DR_CR_CODE_1] = "D")) in #"Filtered Rows"
