Strange SQL error

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

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 Jawla wrote:

Hi Manish,

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

Regards

Stefano Valente wrote:

Hi Stefano,

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

Regards

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

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!

Glad to see it worked and is solved.

Cheers! :)