Restrictions on size/number of records in Teradata SQL statement in advanced editor

[amitksingh2003]

Hi friends, I recently started learning about Power BI and trying to use to automate a task which takes huge manual effort.

I am done with most part of it except hitting an error while trying to retrieve some data from teradata database.

I have a list of payment reference numbers stored in a table named "BI_Data" and column name "Payment Reference Numbers". This column has some 37,000 odd values. I need to retrieve additional details from Teradata with reference to these payment reference numbers.

I am using a variable "PayRefNum" in advanced editor to get the list from table and format it per sql statement requirement for delimiter.

let
    PayRefNum= "'246532387" & Text.Combine(List.Distinct(BI_Data[Payment Reference Number]),"','"),
    Source = Teradata.Database("SERVER", [Query="SELECT FP.PRO_NBR,FP.SCAC_CODE,FP.WM_TRANS_MODE_CD,FP.CARRIER_NAME,FP.SHIP_DATE,FP.SHIPPER_NAME,FP.SHIPPER_CITY_NAME,FP.SHIPPER_STATE_CODE,FP.SHIPPER_POSTAL_CODE,FP.CNSGN_NAME,FP.CNSGN_CITY_NAME,FP.DEST_STORE_NBR,FP.FRT_PYMT_SEQ_NBR,FP.TOTAL_PAID_AMT,FP.PAID_DATE FROM US_WM_LDSS_VM.FREIGHT_PAYMENT FP WHERE FP.FRT_PYMT_SEQ_NBR IN (" & PayRefNum & "')", CreateNavigationProperties=false])
in
    Source

The query works fine when I pass limited number of values for payment reference numbers. However, I get error saying could not convert string to numericals when trying to pass all 37,000 odd payment reference numbers to the query.

When troubleshooting, I can see all the payment reference numbers are correctly loaded to the variable "PayRefNum". However, the values seems to get truncated in query statement.

I wanted to understand if this is because of any limit/restrictions on size or number of records in advanced editor and what is the work around it. My teradata doesn't have this size restriction as I am able to retrieve data successfully directly from teradata for same set of records.

[amitksingh2003 · Accepted solution]

Hi @v-frfei-msft. Thanks a lot for the quick response. 

As you pointed out, the limit of 32,766 seems to be the reason I am getting a truncated view in query run window. However, fortunately, that was not the reason for error in actual running of query. Seems the limit was impacting just the view.

As it turns out, there was a "#" character in my query which was giving this error. I added few validations to check data sanity before query and now it's running fine. Thanks again for your respnose.

[v-frfei-msft]

Hi @amitksingh2003 ，

As the blog, The maximum length of a text value that the Power Query engine can load into a single cell in a table in a dataset is 32766 characters – any more than that and the text will be silently truncated.