3
Views
8
Comments
Solved
SQL1: ORA-00904: "COMP":invalid identifier

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?

Rank: #95
Solution

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

Rank: #70

Hi Jake,


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

Rank: #95
Solution

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