FMS Concatenation - Internal Error

[Former Member]

Hello Experts,

Can anyone help me with this FMS? I get Internal Error when i click on the formatted search but when i run this in SQL syntax it executes successfully.

SELECT 'Batch'= 
CASE When $[$3.U_SES_TrnsTyp.0]='Receipt' 
Then
(RIGHT(CAST(YEAR($[$3.11.DATE]) AS CHAR(4)),2) + RIGHT('000' + CAST(DATEPART(dy,$[$3.11.DATE]) AS varchar(3)),3)
+$[$3.U_SES_Code.0]+$[$3.U_SES_Lseq.0]+'('+$[$3.U_SES_Crg.0]+')')
When $[$3.U_SES_TrnsTyp.0]='Production' 
RIGHT(CAST(YEAR($[$3.11.DATE]) AS CHAR(4)),2) + RIGHT('000' + CAST(DATEPART(dy,$[$3.11.DATE]) AS varchar(3)),3)
Else ''
End

[Former Member]

Hi,

Are you calling a UDF from Header to Row? or Your creating a UDF from a Header then calling the field in the row,

this may create an internal error.

Thanks.

CLint

[Former Member]

Hi,

Is it ok if you just put only this part in FMS:

RIGHT(CAST(YEAR($[$3.11.DATE]) AS CHAR(4)),2) + RIGHT('000' + CAST(DATEPART(dy,$[$3.11.DATE]) AS varchar(3)),3)

Thanks,

Gordon

[Former Member]

Hi Gordon,

I know Julian Date part works fine. Also, Julian Date + one UDF concatenation works but i try to concatenate more than one field then i get error. Any other idea?

Basically user would like to enter info into 3 fields and we need to concatenate all these.

[Former Member]

Is this working?

$[$3.U_SES_Code.0]+$[$3.U_SES_Lseq.0]+'('+$[$3.U_SES_Crg.0]+')'