Why an extra line feed/carriage return in scripted E-SQL calculation

[Rich]

There's a bit of strangeness going on with this calculation between using it in a Calculation (text) field and populating a text field with a Set Field script step:

ExecuteSQL ( "SELECT DISTINCT ( Cad_Type__lxt )

FROM VALUE_LIST

WHERE Cad_FullName__lxt = \"_g_CadFullName__gxt\" AND CountryAbbreviated__lxt = \"_g_CountryAbbreviated__gxt\"

" ; "" ; "" ; "" )

The calculation works fine by itself in the Calculation (text) field but when used in a Set Field script step, a line feed (or carriage return--I can't tell which) is inserted in the text field before the calculated result.

Am I missing something? I could add a subsequent script step that would substitute the value in the text field with a calculation that would remove the line feed/carriage return but that's a bandaid approach to the problem; I'd rather solve it.

TIA for your replies!

Edited August 15, 2018 by Rich
grammar police

[Tom Assia]

Hello,

you don't specify any thing in the rowSeparator so it woulf insert a carriage return as specify in the documentation :

rowSeparator - the character string used as a separator between records in the result. If an empty string is specified, the separator is a carriage return. The row separator is not displayed after the last row in the result.

Maybe you have more than one result ? and an empty as first

 

Tom

[Rich]

Indeed, there are records where the Cad_Type__lxt field is empty so that would explain why there's a carriage return before the correct value. (Why it happens with the Set Field command and not as a calculation is still a mystery.)

I take it, then, I should use the IS NOT NULL command so only those records with a value in Cad_type__lxt will be processed, but after multiple tries I just can't get the syntax right. If anyone could address that, I'd appreciate it.

[Tom Assia]

Hello,

With adding "and Cad_Type_lxt is not null" it's not working ?

Maybe you could say you could change your field and setting it like Auto entry with calculation :

If Extract(field,1,1) = carriage return ; Extract(field; 2 (Length(field)).

So if the first char is carriage return, get the string after the first one

Tom