Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
amitksingh2003
Regular Visitor

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

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.

 

PBI Error2.JPGPBI Error.JPG

 


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.

1 ACCEPTED SOLUTION
amitksingh2003
Regular Visitor

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.

View solution in original post

2 REPLIES 2
amitksingh2003
Regular Visitor

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
Community Support
Community Support

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.

 

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors