So that's what the problem is ..
I would suggest read the Fm documentation first and get yourself familiar with how a model should be made in FM and then start on it.
You can use #2 method (create Data Source query subjects) , one for each table and then create joins between them in the FM. The reason you are getting error for this is because you have to provide a column name for the substring function.
Select
CM_CLIENT_FIELD_DATA.USER_ID,
substring(CM_CLIENT_FIELD_DATA.ITEM_VALUE,1,6),
CM_CLIENT_FIELD_DATA.FIELD_ID
From
[Ilink].CM_CLIENT_FIELD_DATA as CM_CLIENT_FIELD_DATA
Model Query subjects are meant to create Business Layer, not to read data from the Database.
and try using ltrim and rtrim in substring function as I mentioned in my previous post.
If you have to cast it as an integer for the join, cast it as integer like this:
cast(substring(ltrim(rtrim(CM_CLIENT_FIELD_DATA.ITEM_VALUE)),1,6),integer)
However, creating a join on a field that has the cast function might result in performance issues.
