MIN Statement Causing Optimizer Error

[B_rad]

Hi,

I am running SAS version 7.1 on a PC with Windows 10 Enterprise. I query using SQL in a Teradata environment.  I am trying to use a function that takes an attribute that is a free form text field and then expects to return the next 15 characters after the 4th character, then TRIM the result and finds the MIN value. Finally, this function groups on 35 attributes in my select statement. This function is the last attribute in my select statements. See below. 

MIN (TRIM (SUBSTR (my_string_text, 4, 71))) as My_New_Txt

Recently I made some sourcing changes in my query, but the overall select results do not change. However, when I run my query, I am getting an error,:  Teradata row not delivered: Request aborted by TDWM . The error as I understand means that the environment optimizer fails as it expects the query may create more rows than expected.  When I remove said function line, my program runs without any errors or performance issues; thus I think there is something in this substring function that is causing the error and I am looking for alternative.

Does anyone have any suggestions on an alternative function to use or help direct to a proper thread?

[sbxkoenk]

Hello @B_rad ,

As far as I know SAS Version 7 was never on the market. 

SAS went from SAS 6 straight to SAS 8 (well, internally there has been a SAS 7 of course).

I suppose you mean SAS 9.4 Maintenance Level 7 (SAS 9.4 M7)?

Anyway, nothing is wrong with your statement
, although I cannot match your "next 15 characters" with the number 71.

MIN (TRIM (SUBSTR (my_string_text, 4, 71))) as My_New_Txt

No idea why TeraData cannot handle it.

Would it be problematic (performance-wise) to have a simpler query that returns the data to SAS and then do in SAS whatever you want to do.

Thanks,

Koen

[Tom]

There are many things that can cause a Teradata query to fail.  High on the list is if cannot be properly split across the compute nodes.

Ask Teradata to show you its explain plan for your query.

Work with your Teradata administrator to restructure the query (or perhaps the partitioning of of the source tables) so that it will run faster.