This discussion is archived
2 Replies Latest reply: Jan 28, 2013 2:09 AM by 974592 RSS

Compare Date in Expression Builder

974592 Newbie
Currently Being Moderated
Hallo,


I'm trying to build the following sql:


select * from (select * from TABLE1 where CODE =#myCode AND EFFECTIVE_DATE <=#myDate AND EFFECTIVE_DATE >=
ADD_MONTHS(#myDate,(-1)*#ageMaxMonth) ORDER BY EFFECTIVE_DATE DESC ) WHERE ROWNUM < 2





Vector args = new Vector();
args.add(myCode);
args.add(myDate);
args.add(ageMaxMonth);



ExpressionBuilder expBuilder = raq.getExpressionBuilder();
Expression exp = expBuilder.get("myCode").equal(expBuilder.getParameter("myCode"));
exp.and(expBuilder.get("effectiveDate").lessThanEqual(expBuilder.getParameter("myDate")));
exp.and(expBuilder.get("effectiveDate").greaterThanEqual(expBuilder.getParameter("myDate").addMonths(ageMaxMonth.intValue() * -1)));
raq.setSelectionCriteria(exp);
raq.addDescendingOrdering("creationDate");
raq.addArgument("myCode");
raq.addArgument("myDate");
raq.addArgument("ageMaxMonth");

// Include rownum
raq.setFirstResult(1);
raq.setMaxRows(1);
raq.setRedirector(new OracleRowNumRedirector());

Vector vectors= (Vector) uow.executeQuery(raq, args);


The request ist not working. Im get the entries where the effectiveDate EFFECTIVE_DATE <=

ADD_MONTHS(#myDate,(-1)*#ageMaxMonth)







Thanks

Edited by: user11344339 on 25.01.2013 00:25
  • 1. Re: Compare Date in Expression Builder
    cdelahun Pro
    Currently Being Moderated
    What is the SQL that gets generated? You can turn logging on to print the SQL, described here for JPA http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging but is still a good reference for the various levels if using native EclipseLink api.

    It maybe a cut and paste error, but the code shown is not setting exp to the new exp:
    exp =exp.and(expBuilder.get("effectiveDate").lessThanEqual(expBuilder.getParameter("myDate")));
    exp = exp.and(expBuilder.get("effectiveDate").greaterThanEqual(expBuilder.getParameter("myDate").addMonths(ageMaxMonth.intValue() * -1)));
    raq.setSelectionCriteria(exp);

    If this is not a cut and paste error, it would result in the effectiveDate range restriction not being included in the where clause at all.
  • 2. Re: Compare Date in Expression Builder
    974592 Newbie
    Currently Being Moderated
    cdelahun wrote:
    It maybe a cut and paste error, but the code shown is not setting exp to the new exp:
    exp =exp.and(expBuilder.get("effectiveDate").lessThanEqual(expBuilder.getParameter("myDate")));
    exp = exp.and(expBuilder.get("effectiveDate").greaterThanEqual(expBuilder.getParameter("myDate").addMonths(ageMaxMonth.intValue() * -1)));
    raq.setSelectionCriteria(exp);

    If this is not a cut and paste error, it would result in the effectiveDate range restriction not being included in the where clause at all.
    Hello,

    Thanks, this was a part of the problem. the generated SQL is now Ok and the query is working for the object already persisted in the Database.
    But for the Objects in the cache I’m still get records where the ‘effectiveDate’ <= ADD_MONTHS(mydate, ageMaxMonth *-1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points