Unexpected SQL Unexpected '?' in SQL statement of queryname. Recheck the SQL to ....

Hi,

I'm using Outsystems 11.6.26.

I have written a sql query which gives me "warning" syaying

"Unexpected SQL
Unexpected '' in SQL statement of GetCastAndCrew. Recheck the SQL to make sure it is valid"

BUT everything works proper. I am just curious to know why this warning is always propmting.

Exact test is pasted below 

SELECT {Person}.[Id], {Person}.[Name] + ' ' + {Person}.[Surname] Fullname
 FROM {Person}
 JOIN {PersonMovieRole} ON {PersonMovieRole}.[PersonId] = {Person}.[Id]
 JOIN {PersonRole} ON {PersonRole}.[Id] = {PersonMovieRole}.[PersonRoleId]
 WHERE @MovieId is not null
 AND {PersonMovieRole}.[MovieId]  = @MovieId
 AND {PersonMovieRole}.[PersonRoleId] IN (@ActorRole, @CrewRole)
 ORDER BY Fullname


also,see attached screenshot.

in this line try to put AS on alias

SELECT {Person}.[Id], {Person}.[Name] + ' ' + {Person}.[Surname] AS Fullname

If this not enough, please, remove all after 

FROM {Person}

and try step by step join line by line, and you will find what line isn't correct.

Deevan wrote:

Hi,

I'm using Outsystems 11.6.26.

I have written a sql query which gives me "warning" syaying

"Unexpected SQL
Unexpected '' in SQL statement of GetCastAndCrew. Recheck the SQL to make sure it is valid"

BUT everything works proper. I am just curious to know why this warning is always propmting.

Exact test is pasted below 

SELECT {Person}.[Id], {Person}.[Name] + ' ' + {Person}.[Surname] Fullname
 FROM {Person}
 JOIN {PersonMovieRole} ON {PersonMovieRole}.[PersonId] = {Person}.[Id]
 JOIN {PersonRole} ON {PersonRole}.[Id] = {PersonMovieRole}.[PersonRoleId]
 WHERE @MovieId is not null
 AND {PersonMovieRole}.[MovieId]  = @MovieId
 AND {PersonMovieRole}.[PersonRoleId] IN (@ActorRole, @CrewRole)
 ORDER BY Fullname


also,see attached screenshot.


Hi Deevan


I have checked the same query in my Outsystems studio but not getting any warring. can you please click on the warning so it will redirect on the warring statement. Please see the attachment. 



  Apart from above, you can skip the warring and Test the query. warring will not any impact on your query. 

Deevan wrote:

Hi,

I'm using Outsystems 11.6.26.

I have written a sql query which gives me "warning" syaying

"Unexpected SQL
Unexpected '' in SQL statement of GetCastAndCrew. Recheck the SQL to make sure it is valid"

BUT everything works proper. I am just curious to know why this warning is always propmting.

Exact test is pasted below 

SELECT {Person}.[Id], {Person}.[Name] + ' ' + {Person}.[Surname] Fullname
 FROM {Person}
 JOIN {PersonMovieRole} ON {PersonMovieRole}.[PersonId] = {Person}.[Id]
 JOIN {PersonRole} ON {PersonRole}.[Id] = {PersonMovieRole}.[PersonRoleId]
 WHERE @MovieId is not null
 AND {PersonMovieRole}.[MovieId]  = @MovieId
 AND {PersonMovieRole}.[PersonRoleId] IN (@ActorRole, @CrewRole)
 ORDER BY Fullname


also,see attached screenshot.

Hello Deevan,


As Jitender Gaur mentioned I have also used the same query and with no warnings.

My suggestion to you is try removing the single quotes and again add the single quotes.


I hope that works fine as we are not getting any warnings and the query executes as expected.


Nuno Miguel Verdasca wrote:

in this line try to put AS on alias

SELECT {Person}.[Id], {Person}.[Name] + ' ' + {Person}.[Surname] AS Fullname

Thanks 

@Nuno : It does not Work, I tried this before.

I have also tried removing all and rewriting the query again, But no luck.


@Jitender Gaur : Query test is working absolutely fine, I have also checked "Executed SQL" which is also correct. 

@Changalrao Bairaboyana : tried every thing, even i deleted the SQL and created new SQL and rewritten the query.


If i write simple Select, warning goes away but as soon as i do Join with any entity, Warning comes back.


That's why it made me more curious about "Warning" even if query is working fine!!


Hi Deevan,

I'm running 11.6.27 and I don't get any warnings with that query.

I'm guessing you're following the training exercises: did you copy/paste the query from a PDF? I remember a similar thread and it turned out it was an invisible character being copied over and being flagged by Service Studio.

Have you tried rewriting the query by hand?

Afonso Carvalho wrote:

Hi Deevan,

I'm running 11.6.27 and I don't get any warnings with that query.

I'm guessing you're following the training exercises: did you copy/paste the query from a PDF? I remember a similar thread and it turned out it was an invisible character being copied over and being flagged by Service Studio.

Have you tried rewriting the query by hand?

Hi Afonso & Everyone.,

Its strange, but started working, I saved the query in txt file, opend it from cmd prompt and copied the text,

then pasted in SQL of outsystems and its started working. 

Check my screenshot GetCastAndCrew with Old Text and GetCastAndCrew2 with new one. 

First SQL still shows warning and new one does not.

CAN anyone spot the difference in these two SQLs.




Pasting it and saving it in a text editor might have purged the characters that were causing you issues.

If you have access to Notepad++ or any other advanced text editor, try forcing all characters to be displayed with the Show All Characters option or equivalent:

It would show you all characters being used in the query, even the invisible ones. Glad you got it sorted out!

Agree with you Afonso, Thanks everyone, i dont know whome to select for "MARK AS A SOLUTION".

This issue mostly occurs when you copy text from pdf. I copied text from OutSystems Associate Developer Certification course. 


You can mark your own reply, it's okay! That way if someone finds this thread in the future, they can just scroll down to see how you figured it out.

It's not the first time I hear about this, and if people know they can pass the text through another program to fix it, it'll be helpful.