Thanks Alexis,
This was exactly what I tried to achieve.
with best wishes,
Taavi
----- Original Message -----
From: "Alexis HAUMONT" <[EMAIL PROTECTED]>
To: "Turbine Torque Users List" <[EMAIL PROTECTED]>
Sent: Friday, August 09, 2002 8:35 PM
Subject: Re: Fw: criteria: how to write this?
> Hi,
>
> one thing I learn about Crieria, is when you have complex SQL statement
> to write, use Criteria.CUSTOM !
> That way you have the full control of your where-clause :
>
> Criteria crit = new Criteria();
> crit.add(PersonPeer.FIRST_NAME, "UPPER(" + PersonPeer.LAST_NAME + " || "
> + PersonPeer.FIRST_NAME + ") LIKE UPPER( ? )", Criteria.CUSTOM);
>
> You could try to be more db-independant using crit.getDb().ignoreCase(
> String ), but I didn't found anything for the concatenation symbol ' || '
.
>
> On my project, we created a specific Criteria with helper metthods like
> this one :
> (sorry for the french-comments ..)
>
> /**
> * Ajout d'un critere UPPER( column ) LIKE UPPER( '%' + likeClause
> + '%' ) .
> * Le '%' n'est ajout� en debut et fin de clause que si likeClause
> ne contient aucun caractere '%'.
> * likeClause est echap�e et mise entre quote, par
> SqlExpression.quoteAndEscapeText()
> * @param column column a comparer
> * @param likeClause critere de comparaison
> */
> public void addLikeIgnoreCase(String column, String likeClause) {
>
> try {
> DB db = getDb();
> //si on ne trouve pas de caracterer %, on en rajoute un a la
> fin.
> if ((likeClause != null) && (likeClause.length() != 0)) {
> if (likeClause != null && likeClause.indexOf('%') == -1) {
> likeClause = "%" + likeClause + "%";
> }
>
> //on echappe et quote la chaine de comparaison ;
> likeClause =
> SqlExpression.quoteAndEscapeText(likeClause, db);
>
> this.add(column, (Object) (db.ignoreCase(column) + " " +
> Criteria.LIKE + " " + db.ignoreCase(likeClause)), Criteria.CUSTOM);
> }
> } catch (TorqueException te) {
> logger.error("", te);
> throw new IllegalStateException(te.getMessage());
> }
> }
>
>
> With such helper methods, your statement could be simplified with ;
>
> MyCriteria crit = new MyCriteria();
> crit.addLikeIgnoreCase(PersonPeer.LAST_NAME + " || " +
> PersonPeer.FIRST_NAME, <paramValue>);
> (not tested)
>
> Bye,
> Alexis Haumont.
>
> Taavi Tiirik wrote:
>
> >Dear torque users,
> >
> >Please help me with writing following select criteria for torque.
> >
> >I have person table with fields first_name and last_name.
> >Sql equivalent of the query looks like this:
> >
> >select * from person where
> >upper( last_name || ' ' || first_name ) like upper( ? )
> >
> >Given parameter can be something like this:
> > "%Smith J%" => returns all John Smiths,
> > "%john%" => returns all Johns, etc.
> >
> >How can it be done with Torque? Can it be done with Torque?
> >
> >with best wishes,
> >Taavi
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
