Advanced Query Exception - illegal zero length identifier

Advanced Query Exception - illegal zero length identifier

  

Hi,

Being trying to avoid using advanced query as aggregate is a very good but my requirement get too complex and i have to use advanced query. 

I have come out with the following code and i got the error of illegal zero-length identifer, may i know what wrong??

*Edited*

SyntaxEditor Code Snippet

SELECT 
    DISTINCT 
    (CASE WHEN {Course}.[FirstName] <> ''
          THEN (CASE WHEN {Courses}.[FirstName] like '%{Courses}.[LastName]%'
                     THEN {Courses}.[FirstName]
                     ELSE CONCAT({Courses}.[FirstName] ,'|',{Courses}.[LastName]))
           ELSE {Courses}.[LastName]) AS Name
FROM 
    {Courses}

First of all this snippet can be done in an aggregate easily. Just create a new attribute and there you will have your function.


Second, the adv.query is very interesting to say it mildly. 

Attributes should be accessed by {entity}.[attributename] and not just [attributename]

Afaik you cannot have double quotes in an advance sql so you should have single quotes.

Next up your like condition will fail unless the lastname actually contains [LastName]


I will stop now and strongly suggest to learn how to use advanced queries and think how you can solve it in an aggegrate because it cab.



J. wrote:

First of all this snippet can be done in an aggregate easily. Just create a new attribute and there you will have your function.


Second, the adv.query is very interesting to say it mildly. 

Attributes should be accessed by {entity}.[attributename] and not just [attributename]

Afaik you cannot have double quotes in an advance sql so you should have single quotes.

Next up your like condition will fail unless the lastname actually contains [LastName]


I will stop now and strongly suggest to learn how to use advanced queries and think how you can solve it in an aggegrate because it cab.



Hello J,

As stated clearly in the question, if it is possible i would avoid using advanced query at all cost as what you have mentioned that it can be done easily in aggregate. It is because of the complex requirement which do not allow me to continue to use aggregate.  

Hi Jace,

J. as already pointed out all your obvious issues:

  • you need to properly identify your attributes, always use the {Entity}.[Attribute] syntax.
  • apart from that you have SQL syntax issues ( " vs ' ), and
  • your like '%[LastName]%' actually checks for any first name that contains, literally, [LastName]. It should be something similar to like '%' + {SomeEntity}.[LastName] + '%', so that you test for first names that contain the last name.

Finally, from your example (which is all we have from your very complex requirements), I have to agree with J., you could achieve what you have shown us pretty easily by using just an Aggregate with a computed attribute.

Placing [] around a piece of text will always assure it's being picked up as a column, this will work fine if your columns are named exactly as your attributes, however OutSystems could name them differently.

Though I'm sure Jorge's explaination covers everything already.


As the others have noted you could just use an aggregate and do the checking on calculated properties or within a loop after the aggregate.

hi Jace, you miss the END clause for CASE. Here works for me in the cloud environment:

SyntaxEditor Code Snippet

SELECT distinct 
(case
WHEN {Courses}.[FirstName] <> ''
          THEN (CASE WHEN {Courses}.[FirstName] like '%'+{Courses}.[LastName]+'%'
                     THEN {Courses}.[FirstName]
                     ELSE CONCAT({Courses}.[FirstName] ,'|',{Courses}.[LastName])
                     end)
ELSE {Courses}.[LastName]
           end)
from {Courses}

regards

bb

Hi Jace.

Your query can be done in an aggregate, very easily. What is the complex requirement you have, that you clearly does not have in the query you showed us?

What you need to do more than what is there?

Cheers

Eduardo Jauch