[bug] Input string was not in a correct format

[bug] Input string was not in a correct format

  
I just encountered the below error:

Error in advanced query GetProspectById in Preparation in Prospect_Show in MainFlow in SBCardsLOS (Select {Prospect}.*, md1.*, md2.*, md3.*, md4.*,{Scheme}.*, p1.*, p2.*, {Enum}.*, {User}.*, {Activities}.*  FROM {Prospect}  left outer JOIN {MasterData} md1 ON md1.[Id] = {Prospect}.[Portfolio]  left outer JOIN {MasterData} md2 ON md2.[Id] = {Prospect}.[Channel]  left outer JOIN {MasterData} md3 ON md3.[Id] = {Prospect}.[Sublocation]  left outer JOIN {MasterData} md4 ON md4.[Id] = {Prospect}.[DSA]  left outer JOIN {Scheme} ON {Scheme}.[Id] = {Prospect}.[SchemeType]   left outer JOIN {Product} p1 ON p1.[Id] = {Prospect}.[Portfolio]  left outer JOIN {Product} p2 ON p2.[Id] = {Prospect}.[Product]  left outer join {Enum} on {Enum}.[Id] = {Prospect}.[Institution]  left outer join {User} on {User}.[Id] = {Prospect}.[InitiatedByRM]  left outer join {Activities} on {Activities}.[Id] = {Prospect}.[Acitvity]  WHERE {Prospect}.[Id] = @ProspectId): Could not assign '' to 'Enum.Id'... Input string was not in a correct format.

Initially i created an advanced query to get all the information of my 'Prospect'. The query below shows that I removed the Enum, User and Activity output structures and commented out the related sql to make it work.



I thought there is a problem in my query but then I simply moved out the last part of the query and it did work:



Is there any reason for this?
Hi JC,

In short, the problem is related to the usage of alias in the query.
The explanation is a bit lengthy, so I suggest you grab a cup of coffe and check out this post: :)
Advanced Query Pitfalls - using alias and wildcards
Thanks for the reply Paulo. Given the scenario can, you suggest a workaround? By the way, I am using the query as a source of the Prospect Show Record.
Well, if you need to use alias (because you need to retrieve data from multiple MasterData records), you can, but avoid using alias.* in the select.
Instead, retrieve the fields you need one by one, for example:

select {Prospect}.*, md1.ColumnX, md1.ColumnY, md2.ColumnX, md2.ColumnY, ...

You'll also need to have a compatible output structure. Continuing this example, you would define a Structure (e.g. MDStructure) with the necessary fields (e.g. ColumnX, ColumnY). You can then add this structure to the output structure of the query (instead of using MasterData, MasterData_2, etc). Your query output structure would then be:

Prospect
MDStructure
MDStructure_2
...

As an alternative, instead of using a custom structure (like MDStructure above), you can still design your query output structure around MasterData, MasterData_2, etc, but on this case you would have to select all fields (md1.Id, md1.x, md1.y, md1.z, etc) from the entity to fully match the select statement to the output. However, this has two drawbacks:
- You may not need all the fields from the MasterData entity, so it's overkill retrieving everything;
- If later on you add a new column to the MasterData entity, this query would be broken (you would have to add the new column to the select, for matching the output again). Using a structure protects your query against future changes in the entity.