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??

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?

Hi Richardo,

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

Regards,

Manish Jawla

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.

@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.

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.

@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.

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