Hi,
I am trying to use a list box to apply multiple filters to a table records.
The current method I am using is creating an IN condition in a SQL query in which a text parameter is being passed in that looks like the following: 'Assessment 1', 'Assessment 2' which its Expand Inline set to yes.
It filter correctly when only one option is selected but when another is selected the error 'Error excuting query appears' and the error 'ORA-00920: invalid relational operator' displays in Service Center.
I have tried to trouble shoot but I have not been successful.
Please let me know if anyone knows the solution to this problem. I cannot attach the module as it is confidential
As explanation, if you are no expanding online, the second will be valid as you will be comparing texts and the list inside the in will be a single item (text).
If you want to have multiple items, you must expand in-line, but the second must disappear as it will give an error. And if you are not providing a value, the list will be empty and I am not sure Oracle accepts that...
Eduardo Jauch wrote:
Hi Eduardo,
Thank you, it is working now.
The reason it was not working was because the list starts off as empty and so I had to assign values to it in the screen preparation. Oracle obviously does not accept and empty list
What is the letter f at the end of your AND?
Regards,
Daniel
Daniël Kuhlmann wrote:
Hi Daniel,
It is just the alias for that subquery. I have cut the top of the query out for easier viewing of the WHERE clause.
Are you sure that the @AssesmentName parameter is correctly filled?
Yes, I test it by viewing the parameter like this: (you can see the parameter next to the Export button)
My assign when selecting a filter is as follows: (the default value for the parameter AssessmentName is 0)
To be honest I don't see the issue, maybe try to put the ( and ) also in the @AssesmentName
It is complaining about relational operator, so the where clause must be wrong somehonw.
https://www.tekstream.com/oracle-error-messages/ora-00920-error-message/
Did you try without the @AssesmentName = '0 '?
I have tried without the @AssessmentName = '0' and it just starts by returning no results.
I have researched the error but it doesn't seem to help.
Hello Cassandra,
Could you put your full sql here?
There is a mismatch in the parenthesis in the where clause and is difficult to say of that is the cause or just because you removed part of the query.
You can copy the query and pla place it here, no need to put an image.
Cheers
Please see below:
SELECT
f.[Id]
,f.[Title]
,f.[Description]
,f.[CreatedBy]
,{User}.[Name]
,f.[CreatedOn]
,f.[AssessmentStatusId]
,f.[DueDate]
,f.[AssignedTo]
,u.[Name]
,f.[DateCompleted]
,f.[AssignmentStatusId]
,f.[DateAssigned]
,f.[Score]
FROM
(
{Assessment}.[Id]
,{Assessment}.[Title]
,{Assessment}.[Description]
,{Assessment}.[CreatedBy]
,{Assessment}.[CreatedOn]
,{Assessment}.[AssessmentStatusId]
,{Assessment}.[DueDate]
,{Assignment}.[AssignedTo]
,{Assignment}.[DateCompleted]
,{Assignment}.[AssignmentStatusId]
,{Assignment}.[DateAssigned]
,{Assignment}.[Score]
{Assessment} left JOIN {Assignment}
ON
{Assessment}.[Id] = {Assignment}.[AssessmentId]
WHERE
{Assignment}.[AssignmentStatusId] = 1
AND
({Assessment}.[Title] in (@AssessmentName) or @AssessmentName = '0')) f
LEFT JOIN
{User}
f.[CreatedBy] = {User}.[Id]
{User} u
f.[AssignedTo] = u.[Id]
{Question}
f.[Id] = {Question}.[AssessmentId]
{QuestionSectionLink}
{Question}.[Id] = {QuestionSectionLink}.[QuestionId]
{Section}
{QuestionSectionLink}.[SectionId] = {Section}.[Id]
GROUP BY
No results is better than an error.
You have a problem here, as for this to work, your input must be expand in-line, but as it is expanded, it will not be treated as text, and that's the reason why you have an error when try to compare it against '0'.
So, you can't use it.
As for the fact that without it it will return no rows, the problem lies somewhere else.
What result were you expecting?
Thanks Eduardo, now when I remove the 'or @AssessmentName = '0'' it gives me this error when testing the query:
However, when I turn Expand Inline to No, it gives no error
What is the exact value you are setting as the value of this parameter when testing?