I have an entity Template and an entity Section. A template can have many sections.
There is also a SectionStatus static entity with 2 records (Complete, Incomplete).
I want to show a statistic like 5 of 20 sections completed. So I want to have two advanced queries, one to count the completes and other to count the imcompletes (dont know if its a good approach to do this like this), for example to get the complete sections I have like this however it shows "SQL1: ORA-00904: "COMP":invalid identifier".
SELECT count({TemplateSection}.[Id]) from {TemplateSection} where {Template}.[Id] = 2 and {TemplateSection}.[StatusCode] = "COMP"
Do you know why it shows that error?
Hi Jake,
Hope you're doing well.
Clearly one of the problems seems to be the fact that you have the {Template} entity in your WHERE clause, but it is not present in your FROM clause.
Another situation is that you shouldn't use fixed IDs in your queries. You should avoid things like this:
where {Template}.[TemplateId] = 2
Because if your ID changes for some reason, that query won't work as expected anymore. It is not a good practice to have it like this :)
As far as I can tell, that query seems to be doable using an Aggregate. So I suggest that you use an Aggregate in this scenario instead of a SQL Tool.
Kind regards,
Rui Barradas
Hi! It was a typo, sorry! Relative to the fixed ids is only for testing purposes I was using parameters however I was getting some errors so I was testing like this to be if it worked. Relative to aggregates is possible to do the count? And use 2 aggregates also? Thanks!
Hello Jake,
Yes, you can execute a Count using an Aggregate :) you should right click on top of the attribute that you want to count and you'll see that option:
And yes, you can use as many Aggregates as you need. The same rule applies for Advanced SQL queries. In your scenario, you'll probably need 2 Aggregates (one for each status).
You are filtering a field of entity {Template} which is not on your FROM section.
You probably want it to have this:
SELECT count({TemplateSection}.[Id]) from {TemplateSection} where {TemplateSection}.[TemplateId] = 2 and {TemplateSection}.[StatusCode] = "COMP"
Hope it helps.
Regards,João
Hi, sorry it was a typo. It shows the same error like that!
From the message it seems that you're comparing values of two different types.
Looking at your query it is likely to be the StatusCode field. What type is your StatusCode attribute?
Is it by any chance of the type SectionStatus Identifier?
Thanks! The "code" primary key field of SectionStatus static entity is of type "text".
You should definitely use an aggregate for your query which is simple enough for that.
You can add the Count on the arrow of the field you want to apply the Count:
And then select the option Count: