Strange SQL error
Question

Dear all,

strangest thing happened.

Just a very simple SQL (instead of an aggregate); 

"SELECT * FROM {AFDELING}" results in "Error in advanced query SQL1: Input string was not in a correct format".

I'm no expert, but i think i didn't do anything wrong :-)

Has something to do with the integrity of the database? If yes, how to solve?


Kind regards,

me

 

mvp_badge
MVP
Solution

Hello Jeroen

It depends. The system will try to put the first column retrieved in the first column of the output. 

If they match, it will work. If not, like in this case, two things can happen: data in wrong columns, or an error if the data type is not compatible (happened here).

When you use the way I showed, the query sent to database will have each columns specified in the same order than the entity, and thus you always are putting right columns in right columns.

Cheers

mvp_badge
MVP

Hello Jeroen,

Please, rewrite the query to have {AFDELING}.* instead of *

This is a problem with the order of the fields that the Select is bringing.

Cheers


Hi Eduardo,

indeed this works, thanks!!

But only one question; Normally my query should work??

mvp_badge
MVP
Solution

Hello Jeroen

It depends. The system will try to put the first column retrieved in the first column of the output. 

If they match, it will work. If not, like in this case, two things can happen: data in wrong columns, or an error if the data type is not compatible (happened here).

When you use the way I showed, the query sent to database will have each columns specified in the same order than the entity, and thus you always are putting right columns in right columns.

Cheers

Thanks Eduardo !!

Hello sorry for reopen this topic, but I've the same problem and I think I'm doing it right!
This is my exemple:
SELECT
{Employee}.*,
{Person}.*,
{EmployeeSector}.*,
Team.*,
Sector.*,
{DailyReserve}.*,
DailyReserveSector.*
FROM {Employee}
INNER JOIN {Person} ON {Person}.[EmployeeId] = {Employee}.[Id]
LEFT JOIN {EmployeeSector} ON {EmployeeSector}.[EmployeeId] = {Employee}.[Id] and {EmployeeSector}.[IsActive] = 1
LEFT JOIN {Sector} Team ON Team.[Id] = {EmployeeSector}.[SectorId]
LEFT JOIN {Sector} Sector ON Sector.[Id] = Team.[ParentSectorId]
LEFT JOIN {DailyReserve} ON {DailyReserve}.[PersonId] = {Person}.[Id] and {DailyReserve}.[ReserveDate] = convert(date, getdate(), 120) and {DailyReserve}.[IsActive] = 1
LEFT JOIN {Sector} DailyReserveSector ON DailyReserveSector.[Id] = {DailyReserve}.[SectorId]

Can someone tell me what I'm doing wrong?

mvp_badge
MVP

Hi Richardo,

Please check the syntax in select statement, few tables like Team.*, Sector & more should be {Team}.*.


Regards,

Manish Jawla

Manish Jawla wrote:

Hi Richardo,

Please check the syntax in select statement, few tables like Team.*, Sector & more should be {Team}.*.


Regards,

Manish Jawla

Hi Manish,

Those are alias soo it's not suposed to have curly braces!


Regards




Ricardo, 

Manish is right.

But i also wonder if your left join will still work when you add {EmployeeSector}.[IsActive] = 1 to your left join.


Stefano Valente wrote:

Ricardo, 

Manish is right.

But i also wonder if your left join will still work when you add {EmployeeSector}.[IsActive] = 1 to your left join.


Hi Stefano,

I put that query into test in SQL Server and it works just fine! 


Regards


mvp_badge
MVP

@Manish and @Stefano,

Team, Sector and DailyReserveSector are 'alias' to the {Sector}, so that should not be the problem.

@Jeroen,

Assuming the error is the same as the one in the OP.

Check if your output structure is correct.
It should be, in this order, the following entities:

Employee
Person
EmployeeSector
Sector
Sector
DailyReserve
Sector

If it is already like this, then you probably have some "deleted" attributes that the platform is not avoiding due to the use of the alias. In this case, the solution would be to clean the database from those deleted attributes. You can use DBCleaner and DBCleanerOnSteroids to perform this cleaning.

Hipe this helps.

Cheers.

P.S. Even being the same error, is always better to open a new Topic and make a reference to this one, for example.

Eduardo Jauch wrote:

@Manish and @Stefano,

Team, Sector and DailyReserveSector are 'alias' to the {Sector}, so that should not be the problem.

@Jeroen,

Assuming the error is the same as the one in the OP.

Check if your output structure is correct.
It should be, in this order, the following entities:

Employee
Person
EmployeeSector
Sector
Sector
DailyReserve
Sector

If it is already like this, then you probably have some "deleted" attributes that the platform is not avoiding due to the use of the alias. In this case, the solution would be to clean the database from those deleted attributes. You can use DBCleaner and DBCleanerOnSteroids to perform this cleaning.

Hipe this helps.

Cheers.

P.S. Even being the same error, is always better to open a new Topic and make a reference to this one, for example.

Thank you very much Eduardo!

You're right! The problem is related to previous deleted attributes! I already delete them and now it's working! 

Next time I'll create a new topic as you mentioned!


mvp_badge
MVP

Glad to see it worked and is solved.

Cheers! :)

I am facing similar problem. If I try to run the Query in SQL Sandbox it works but inside Outsystem Advance Query it simply doesn't.

I have an Aggregate I want to fill with some data in order to use it as temporary table inside a Server Action.

I want to select only specific condition from the original aggregate .

SELECT A.* from {PipingMasterPriceList} A
where A.[RefDate] = (select max (B.[RefDate]) from {PipingMasterPriceList} B
where A.[ITEMSID] = B.[ITEMSID]); 

Database returned the following error: Error in advanced query SQL1: Input string was not in a correct format.

Sorry solved.

SELECT {PipingMasterPriceList}.* from {PipingMasterPriceList} 
where {PipingMasterPriceList}.[RefDate] = (select max (B.[RefDate]) from {PipingMasterPriceList} B
where {PipingMasterPriceList}.[ITEMSID] = B.[ITEMSID]); 

sometime Outsystem advanced query is a bit awkward

Hello,

I was looking for a solution for a similar problem. Although in you case you have the choice to not use table alias, there are situations where we need to use it. Example:


This is a serious problem, as it seems we cannot return all the fields when we use a table Alias.

@Vasco Mendes  @Vasco Mendes did you ever find a solution to this issue? I am having the exact issue where I am using an Alias and wanting to use .* but it gives this same error. So I have to manually specify every column which is not great.

Hello,

No, i have not found a solution... Also specifying all columns.

Hi Vasco and Nicholas.

For a number of reasons whenever possible aggregates should be used instead of advanced queries.

Vasco's particular query should be done with an aggregate, that should solve the problem. It's as simple as 'get all columns from joining two tables'.

Advanced queries should only be used when you need a complex query that can't be done using an aggregate.

Hello,

The query i posted here, was just a simplified query, so i could exemplify the issue. The query where i needed this feature is much larger.

And even if this was the query i needed, avanced queries should include all the basic sql features.

Hi Vasco,

I totally agree on that! But somehow advanced queries don't always behave as expected when using alias.

Keep in mind that advanced queries output all the columns specified in the SELECT statement. So when I really need to use them, I create a structure with only the columns I need and use it as output for the advanced query.

I have tried building something alike and seems to be working. maybe missing the [ ?


Hi Giulio.

I think the issue relates to deleted attributes from the entity.

When you delete an attribute from an entity it will not be deleted from the physical SQL table, just from the logical entity that you see in Service Studio.

And when you have SELECT * in an advanced query, the executed SQL will be SELECT *. No column is specified so all columns will be retrieved - even the deleted ones.

As an example I have this simple table that I creatively called TestTable2. If I do a select all:


It will execute the SQL statement:


Which will retrieve the record:


Now if I delete the IntField from the entity and execute the query again:

The content of the removed IntField will be stuffed into TextField. If data types of both columns are not compatible then an error is thrown. Like the one shown by Jeroen.


Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.