Error in advanced query: "Could not assign '' to '[Structure].[Field]'..."

Hi,

I am getting the following error in an advanced query in a preparation that fetches data from the database to display in a screen:

"Could not assign '' to '[Structure].[Field]'... Thread was being aborted."

It is a relatively long query, with a UNION and some sub queries, but the odd thing and what makes it harder to understand the issue is that it doesn't always happen, even with the same inputs, and that the error doesn't always happen in the same field.

We even tried to execute the query directly in sql server and didn't get any error.

Has anyone dealed with anything like this?

Thanks in advance

Hello Diogo,

Can you share your query?

In the service center it was not possible to identify more details about this error?

Greetings.

Hi Diogo,


You can not found the actual error in service center and if the query is giving correct result for some input values it means the issues is with the data (mapping datatype) you are fetching and passing to the structure Or it may be the issue of union because union always expecting the same data type in result from both the queries joined with union. 

Instead you can do one thing.. just pass the input and check the mapping with structure and union queries (datatype) in correct result and error result both. 

you will find the actual issue Or else if possible please share the query. :-)


Regards,

Himanshu

Diogo Raposo wrote:

Hi,

I am getting the following error in an advanced query in a preparation that fetches data from the database to display in a screen:

"Could not assign '' to '[Structure].[Field]'... Thread was being aborted."


Not sure if it is a typo or you wrote your query this way: [Structure].[Field] but should be this way: {Structure}.[Field]

can you check if isn't that? or share the oml or the query itself?


Diogo Raposo wrote:

Hi,

I am getting the following error in an advanced query in a preparation that fetches data from the database to display in a screen:

"Could not assign '' to '[Structure].[Field]'... Thread was being aborted."

It is a relatively long query, with a UNION and some sub queries, but the odd thing and what makes it harder to understand the issue is that it doesn't always happen, even with the same inputs, and that the error doesn't always happen in the same field.

We even tried to execute the query directly in sql server and didn't get any error.

Has anyone dealed with anything like this?

Thanks in advance

Hallo Diogo,

the sql produces an output based on the columns / attributes in the query.

this output has to be mapped to an output structure and every column of the query has to fit in the defined output structure. So if it sometimes works and sometimes doesn't it could be that the query does not always produces the same columns / datatypes. But to be able to tell more you would have to share query and output structure.

Kind Regards, Sven


Hi Diogo,

I think Sven is right, and the cause is very likely a conversion going wrong. If you have an Integer in your Structure, and the database column is Text, the Platform will automatically try to convert the text column to an Integer, and this will actually succeed if the column contains a convertible text like "1" or "435243", but it will fail if the conversion can't be done, like when there's "A" in the value.

So it sometimes going wrong means you sometimes get an output that contains an inconvertible value.

Hi,

First of all, thanks everyone for your reply.

Unfortunately I can't share the query.

I think that Sven and Kilian may be right, but the odd thing is that event when the query is executed with the same inputs the result is not always the same. Sometimes the query executes normally, and sometimes I get an error. Is it possible that a timeout is causing this error?

A time-out will typically result in a different error, so I don't think so. Do you have a TOP in the query without an ORDER BY? Because without an ORDER BY, the order is undefined, and with a TOP you could get different results each time.

Kilian Hekhuis wrote:

A time-out will typically result in a different error, so I don't think so. Do you have a TOP in the query without an ORDER BY? Because without an ORDER BY, the order is undefined, and with a TOP you could get different results each time.

No, I don't have any TOP clause in the query.