I have also searched in the db2 documentation for nr 42601. What is the best way to solve this problem? When i use my old code (not ibatis) it works fine, when i use ibatis it also works fine, but there must be a order by and a substr for getting the correct information to the user, and there is the problem, the order by and substr.
>----- Oorspronkelijk bericht ----- >Van: Richard Yee [mailto:[EMAIL PROTECTED] >Verzonden: maandag, augustus 6, 2007 04:08 PM >Aan: [email protected] >Onderwerp: Re: Question using order by > >It seems that the where clause is where the problem is. > >-Richard > > >translate to as an error message in the DB2 documentation? > >-Richard > > > >davy wrote: >> You are right, sorry. >> >> Yes i have tried that : >> ORDER BY substr(refnumber,16,12) DESC, refnumber, begindate DESC >> >> I get the following error message in my stacktrace. >> >> com.ibatis.dao.client.DaoException: Failed to queryForList - id >> [BEHEER.selectByBeheerList1], parameterObject [mode >> [EMAIL PROTECTED] Cause: >> com.ibatis.common.jdbc.exception.NestedSQLException: >> --- The error occurred while applying a parameter map. >> --- Check the BEHEER.selectByBeheerList1-InlineParameterMap. >> --- Check the statement (query failed). >> --- Cause: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -104, >> SQLSTATE: 42601, SQLERRMC: where;DER BY ENDDATE;<space> >> Caused by: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -104, >> SQLSTATE: 42601, SQLERRMC: where;DER BY ENDDATE;<space> >> >> >> >> >> >>> ----- Oorspronkelijk bericht ----- >>> Van: Richard Yee [mailto:[EMAIL PROTECTED] >>> Verzonden: maandag, augustus 6, 2007 03:36 PM >>> Aan: [email protected] >>> Onderwerp: Re: Question using order by >>> >>> Have you tried putting >>> >>> select refnumber, rownumber, begindate, enddate, officenumber >>> from ${schema1}.office >>> <isParameterPresent> >>> <include refid="office.abatorgenerated_Example_Where_Clause"/> >>> <isNotNull property="orderByClause"> >>> </isNotNull> >>> </isParameterPresent> >>> ORDER BY substr(refnumber,16,12) DESC, refnumber, begindate DESC >>> >>> </select> >>> >>> in your SQL map? You haven't really explained what error or problem you are >>> having. That is probably why no one is responding. Are you getting any >>> errors? >>> >>> -Richard >>> >>> >>> >>> >>> >>> Jdev wrote: >>> >>>> Is there really nobody who can help me using an order by? >>>> I have searched on the Internet but I can find nothing useful. >>>> I have post this question a couple of days ago, but I have got still no >>>> answer. >>>> >>>> -------------------------------------------- >>>> >>>> >>>> I have the following query which must be converted to Ibatis. >>>> But I don’t know how I must write the SUBSTR and DESC in Ibatis >>>> >>>> private final String select1 = >>>> "SELECT refnumber, rownumber, begindate, enddate, officenumber, " + >>>> FROM " + ConnectDB.schema1 + ".office " + >>>> "WHERE refnumber LIKE ? " + >>>> "ORDER BY substr(refnumber,16,12) DESC, refnumber, begindate DESC >>>> "; >>>> >>>> Abator has everything generated and everything works perfectly, except I >>>> don’t know how to write the query in the Ibatis xml file for SUBSTR and >>>> DESC. >>>> Below a small query what Abator has generated and the query mentioned >>>> below must be extended to what above stands. >>>> Can someone explain me how or what is the best way to process the above >>>> query to my new Ibatis query mentioned below. >>>> >>>> <select id="………etc…. >>>> select refnumber, rownumber, begindate, enddate, officenumber >>>> from ${schema1}.office >>>> <isParameterPresent> >>>> <include refid="office.abatorgenerated_Example_Where_Clause"/> >>>> <isNotNull property="orderByClause"> >>>> order by $orderByClause$ >>>> </isNotNull> >>>> </isParameterPresent> >>>> </select> >>>> >>>> >>>> Thank you in advance, >>>> Davy >>>> >>>> >>>> >>>> >>>> >>> >>> >> >> >> >> > > >
