Derived Column for Oracle Queries
Derived Column for Oracle Queries
- Subject: Derived Column for Oracle Queries
- From: Frank C <email@hidden>
- Date: Mon, 25 Feb 2013 13:34:21 -0600
- Acceptlanguage: en-US
- Thread-topic: Derived Column for Oracle Queries
I have implemented several custom EOQualifiers for Oracle. Some for text searches and some for spatial searches. The queries work fine for what they do, however, I have been unable to take advantage of some of the Oracle features.
When you perform these searches in Oracle you can assign a number to the query that allows you to perform operations on that query. As an example, when you do a full text search using the "CONTAINS" function, I can assign a number to the contains clause. I can then use the "SCORE" function to get the relevancy score for that contains clause.
An Example:
select SCORE(1) from Foo t0 where CONTAINS(t0.description, 'bar', 1) > 0 order by SCORE(1) desc;
As you can see above I can use the "SCORE" function to both get the relevancy score in the column results and to order the results and put the most relevant results first. I could even add a second CONTAINS and label it "2" and then use "SCORE(2)." There are similar functions for Spatial queries as well.
There are a couple of problems I do not know how to solve in WebObjects:
1) When my custom qualifier is generating the SQL, is there a way for it to insert the function into the select list and/or into the ORDER BY clause. Or is there a way to add a function column or ORDER BY clause that can peek into the qualifiers to see if there is a matching qualifier?
2) If I can accomplish #1, is there anyway for the qualifiers to know how many qualifiers are in the fetch specification so that I can make sure the multiple qualifiers do not use the same identifier.
3) If I can accomplish #1 and/or #2, how can I build a fetch on a table that brings back the extra column from the select (i.e. SCORE(1)).
Thanks,
Frank
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden