EJB-QL problem. max() statement.

[scar]

Hi.

I have a problem with ejb-ql statement.

I have entity-bean with finder findByShiftIdAndDate that consists:
select OBJECT(oshcd) from OsdShiftHourCorrection oshcd where oshcd.shiftIdFK = ?1 and (oshcd.year = (select max(oshcdy.year) from OsdShiftHourCorrection oshcdy where oshcdy.year<=?2))

When I deploy jar, I got next application error message:

2002-09-08 12:51:26,221 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.OsdShiftHourCorrection.findByShiftIdAndDate] EJB-QL: select OBJECT(oshcd) from OsdShiftHourCorrection oshcd where oshcd.shiftIdFK = ?1 and (oshcd.year = (select max(oshcdy.year) from OsdShiftHourCorrection oshcdy where oshcdy.year<=?2))
2002-09-08 12:51:26,251 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.parentTraceEnabled=true
2002-09-08 12:51:26,251 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.nestedTraceEnabled=true
2002-09-08 12:51:26,251 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.detectDuplicateNesting=true
2002-09-08 12:51:26,261 ERROR [org.jboss.ejb.EjbModule] Starting failed
org.jboss.deployment.DeploymentException: Error compiling ejbql; - nested throwable: (org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "= ( select" at line 1, column 99.
Was expecting one of:
")" ...
"+" ...
"-" ...
"*" ...
"/" ...
"=" "+" ...
"=" "-" ...
"=" <NUMERIC_VALUED_PATH> ...
"=" <INTEGER_LITERAL> ...
"=" <FLOATING_POINT_LITERAL> ...
"=" "(" "+" ...
"=" "(" "-" ...
"=" "(" <NUMERIC_VALUED_PATH> ...
"=" "(" <INTEGER_LITERAL> ...
"=" "(" <FLOATING_POINT_LITERAL> ...
"=" "(" "(" ...
"=" "(" <NUMERIC_VALUED_PARAMETER> ...
"=" "(" "LENGTH" ...
"=" "(" "LOCATE" ...
"=" "(" "ABS" ...
"=" "(" "SQRT" ...
"=" <NUMERIC_VALUED_PARAMETER> ...
"=" "LENGTH" ...
"=" "LOCATE" ...
"=" "ABS" ...
"=" "SQRT" ...
"NOT" ...
"BETWEEN" ...
)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.(JDBCEJBQLQuery.java:46)
...

Does that means I can't use MAX() oracle function in my ejb-ql statements?

I've been tried to set

<function-mapping>
<function-name>max</function-name>
<function-sql>max(?1)</function-sql>
</function-mapping>


in my standardjbosscmp-jdbc.xml file

but a result is the same: error message still exists.

Would you so kind to clear up me what i have to do to solve my problem?

Thanks a lot for advice.

Regards, Scar

[mipe]

hi,

In the specification of EJB 2.0 you can read the followings:

EJB QL includes the following built-in functions

String Functions:
CONCAT(String, String) returns a String
SUBSTRING(String, start, length) returns a String
LOCATE(String, String [, start]) returns an int
LENGTH(String) returns an int

Note that start and length designate the positions in a string defined by an int.

Arithmetic Functions:
ABS(number) returns a number (int, float, or double)
SQRT(double) returns a double

So, I'm afraid EJB-QL doest not support MAX, but if it does, I'm interested in the solution as well.

regards
-peter

[mipe]

well, the real problem might be that EJB 2.0 says:

select_clause ::= SELECT [DISTINCT ] { single_valued_path_expression |OBJECT (identification_variable)}
---

And I found the following in the EJB 2.1 Proposed Final Draft:

select_clause ::=SELECT [DISTINCT ] {select_expression |OBJECT (identification_variable)}

select_expression ::= single_valued_path_expression | aggregate_select_expression

aggregate_select_expression ::= {AVG | MAX | MIN | SUM | COUNT }( [DISTINCT ] cmp_path_expression) | COUNT ( [DISTINCT ] identification_variable | single_valued_cmr_path_expression)
---

So, aggregate expressions, such as MAX, are definied in the select clause in EJB 2.1. As I know, JBoss supports EJB 2.0 :-(

[scar]

Thanx for your answer mipe -- one is very helpful.

I am disappointed in EJB2.0 really -- max() function is not very difficult to support I think :( Of course, there are another ways to solve my problem and to get the same result --just search in the query-without-max() result and fish data from result collection.

Thanks again for your tips mipe.

Regards, Scar.