Problem with custom query

sankaran

I have created a business object called Project_task_schedule. One of the attributes is called BaseLineNextTask which is an object reference to itself.

I have 2 problems

To allow addition of business entities to this attribute, I clicked on the Presentation to navigate into the Reference element window. I then had the "Generate "Add" button' ticked and then clicked on the settings to get into the "Add References" window. I then selected the "Use Default Implementation" radio button, to edit the Custom query as follows:

FIND Project_task_schedule WHERE Project_task_schedule IN ThisProject_task_schedule.BaseLineNextTask

The query returns correctly all the tuples in the attribute BaseLineNextTask.

Next I changed the query so as to complement it and get tuples other than the ones referred by BaseLine NextTask by introducing a negation "NOT" as follows:

FIND Project_task_schedule WHERE NOT(Project_task_schedule IN ThisProject_task_schedule.BaseLineNextTask)

I would have expected the query to return other tuples. However the query results were empty.

I find the following error in the Tomcat log output:

Web server returned error: Internal error. Element NOT(Project_task_schedule IN ThisProject_task_schedule.BaseLineNextTask) in query condition NOT(Project_task_schedule IN ThisProject_task_schedule.BaseLineNextTask) of query FIND Project_task_schedule WHERE NOT(Project_task_schedule IN ThisProject_task_schedule.BaseLineNextTask) cannot be converted to the currently installed version of SQL

Is there a limitation?

I thought of trying out directly the SQL by using the SQL form instead of the rule form and create my own SQL. However I find that SQL does not get saved. Whenever I exit from the query and come back I only find the following statement in the SQL form:

SELECT Project_task_schedule.* FROM PROJECT_MANAGEMENT_PROJECT_TASK_SCHEDULE AS Project_task_schedule

Is there a problem that the user created SQL statements do not get saved?

aware_support

Yes, it is a limitation documented in the User Guide.

NOT (.. IN) is not supported in queries

Queries in the SQL form are obsolete. Use queries implemented as stored procedures instead.

tford

Re #1 .. See Appendix B #14 for a work around.

himanshu

Hi,

negation is not supported then what will be other approach to do similar queries.

can u provide me any procedure example.

Thanks ,

Please reply
:roll:

tford

Re #1 .. See Appendix B #14 for a work around.

Do you have a specific question not covered in this example?

himanshu

Yes,

here is the specific question.

THIS WHAT WE CAN DO RIGHT NOW USING FIND ACTION.

SELECT * FROM TABLE AS T WHERE T.n IN LIST;

WHAT WE SHOULD WRITE TO PERFORM VICE VERS.

SELECT * FROM TABLE AS T WHERE NOT (T.n IN LIST);

what are the other methods of achieving the not query results ?

i require at dozen of places where i want to show with NOT() results.

tford

Re #1 .. See Appendix B #14 for a work around.

Have you tried to what is suggested here? If so, do you have a specific question about what you tried?

himanshu

Hi tom,

please suggest me to do following task.

Having

CUSTOMER
ORDERS

I create a reference field in CUSTOMER of ORDER display as grid as one to many relationship.

FIND Order WHERE (Order IN Customer.Order AND Customer=ThisCustomer)
this query will display order belong to this customer

I want to write a query which shows order not belong to this customer or ( say write a query which uses give results like we use NOT with IN in select statement).

as in users guide it mention "Negated IN expression is not supported in queries".

Thanks,

tford

From Appendix B:

Negated IN expression is not supported in queries. For example, the following expression is not supported:
FIND Event WHERE NOT(LoggedInMember IN Event.Participants)

In many cases the negated IN expression can be replaced by using the equivalent COUNT or EXISTS expression, for example:
FIND Event WHERE COUNT Member WHERE(Member IN Event.Participants AND Member=LoggedInMember) = 0