Advanced Query Error

I am connected to external database and I am getting this error when I am trying to execute this query! 

Does anyone has an idea about why is this happening?

Hi Mohamed,

I am not sure, but maybe its something to do with the output structure, and the delete not returning anything.

Try adding a select that will populate the structure after the delete.

eg

delete from {CITIES} where City_Id = @CityId;

select 'ABC';

Otherwise, some more information may be helpful, such as

* What type of database is the external database?

* What attributes and type does Structure1 have?

* Do you get a similar error with a query that returns values?

Hope this helps!

Kind regards,

Stuart

Stuart,

No need to add a select. Although the delete does not return anything Outsystems requires an output structure. Anyone will do.

The problem is the SQL it self

instead of City_Id = you need to code {CITIES.[City_Id] =

Also as it is an external database, ensure that you have the proper permissions on the table to delete records.

Regards,

Daniel 

Ah yes, I missed that. Daniël is correct.

Also, just a typo above, it should be {CITIES}.[City_Id] = 

To give you more explanation (over Daniël's response) on how SQL queries works in OutSystems. 

• To reference an entity in your SQL query write it between  { curly brackets (e.g. {CITIES}), and
• To reference an entity attribute write it between  [ square brackets (e.g. [City_Id]), and
• To reference an input parameter in your SQL statement use a @ prefix, e.g. @CityId.

So, your query should be 

 delete from {CITIES} where {CITIES}.[City_Id] = @CityId

Daniël Kuhlmann wrote:

Delete, insert and update statement return something. The number of rows affected by the operation. Since advanced queries require a structure on these situations I create a structure with a integer attribute

Hello Nelson. That is a really good idea. I never though on it. Can who give some more details how you fill it?

TIA

Hi all,

Thanks for your answers, but I don't think the syntax is the problem:

Also, I have an integere attribute in my output structure.

Answering some questions:

I am using EXASOL as an external Database.

I don't get this error with Select Query for example.

Alberto Ferreira wrote:

Check this post https://www.outsystems.com/forums/discussion/45595/getting-total-affected-rows-in-advanced-update-query/

Thanks Nelson. Very Useful