Jump to content

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

Recommended Posts

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 )


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 by Rich
grammar police

Share this post

Link to post
Share on other sites


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

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites


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


  • Thanks 1

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Who Viewed the Topic

    1 member has viewed this topic:

Important Information

By using this site, you agree to our Terms of Use.