Hello John,
I have not, but will try. I was not aware, that OpenJPA supports CASE
ordering. At least the Criteria API does not contain this feature. I
thought this would be something only Hibernate or EclipseLink supported.
But I might be wrong here.
Thanks,
Heiko
--
Dr.-Ing. Heiko Kopp / Fa. Vision iT media GmbH
john.bobl...@bertschi.com
17.10.2012 11:09
Bitte antworten an
users@openjpa.apache.org
An
users@openjpa.apache.org
Kopie
Thema
AW: Antwort: AW: Antwort: AW: @Enumerated(EnumType.ORDINAL) in JPQL CASE
WHEN generates invalid SQL using name of Enumeration member, not ordinal
Yes, that is much clearer as to your intent.
Have you tried this?
SELECT c
FROM DbContract c
WHERE c.person.personId = :personId
ORDER BY
( CASE c.approval.state
WHEN ApprovalState.IN_PROCESS THEN 1
WHEN ApprovalState.RELATION_CHECK THEN 2
WHEN ApprovalState.IN_APPROVAL THEN 3
WHEN ApprovalState.NEEDS_SCORING THEN 4
WHEN ApprovalState.ADOPTION_REQUEST_IN_PROCESS THEN 5
WHEN ApprovalState.ADOPTION_REQUESTED THEN 6
WHEN ApprovalState.APPROVED.ordinal() THEN 7
WHEN ApprovalState.APPROVED_WITH_COLLATERALS THEN 8
ELSE 0
END
)
, c.insertDate
John
> -----Ursprüngliche Nachricht-----
> Von: it-media.k...@daimler.com [mailto:it-media.k...@daimler.com]
> Gesendet: Mittwoch, 17. Oktober 2012 10:16
> An: users@openjpa.apache.org
> Betreff: Antwort: AW: Antwort: AW:
> @Enumerated(EnumType.ORDINAL) in JPQL CASE WHEN generates
> invalid SQL using name of Enumeration member, not ordinal
>
> Hello,
>
> that is correct. I need to do the SELECT CASE, because with
> this I'm applying a specific sort order to the members of the
> enumeration.
>
> E.g. I need to have GEN_STATUS value 14 which corresponds to
> enumeration value IN_APPROVAL to be before GEN_STATUS value 1
> which corresponds to enumeration value ACCEPTED. I need to do
> this within the SQL statement, as this statement is used
> within a lazy load data model of a JSF table. I'm not able to
> change the database ordering, as sometimes the order ist this
> way, sometimes it is needed otherwise.
>
> Hope I explained this in a way it is undestandable :-) Here
> is the full SELECT to make it even more clear:
>
> SELECT c,
> ( CASE c.approval.state
> WHEN ApprovalState.IN_PROCESS THEN 1
> WHEN ApprovalState.RELATION_CHECK THEN 2
> WHEN ApprovalState.IN_APPROVAL THEN 3
> WHEN ApprovalState.NEEDS_SCORING THEN 4
> WHEN ApprovalState.ADOPTION_REQUEST_IN_PROCESS THEN 5
> WHEN ApprovalState.ADOPTION_REQUESTED THEN 6
> WHEN ApprovalState.APPROVED.ordinal() THEN 7
> WHEN ApprovalState.APPROVED_WITH_COLLATERALS THEN 8
> ELSE 0
> END
> ) AS stateOrder
> FROM DbContract c
> WHERE c.person.personId = :personId
> ORDER BY stateOrder, c.insertDate
>
> Best regards, and thank you for your help :-)
>
> Heiko
>
> --
> Dr.-Ing. Heiko Kopp / Fa. Vision iT media GmbH
>
>
>
>
> john.bobl...@bertschi.com
> 17.10.2012 10:08
> Bitte antworten an
> users@openjpa.apache.org
>
>
> An
> users@openjpa.apache.org
> Kopie
>
> Thema
> AW: Antwort: AW: @Enumerated(EnumType.ORDINAL) in JPQL CASE
> WHEN generates
> invalid SQL using name of Enumeration member, not ordinal
>
>
>
>
>
>
> Sorry that didn't work ...
>
> If I'm reading it correctly,
> - in the DB you have a column "GEN_STATUS" which is a number type
> (integer?)
> - this number corresponds to your enumeration as the ordinal position
> - in the result, you want to show the number (and sort the
> results based
> on it)
>
> Is that correct?
>
> If so, why are you trying to transform the results?
>
> John
>
>
>
>
>
> > -----Ursprüngliche Nachricht-----
> > Von: it-media.k...@daimler.com [mailto:it-media.k...@daimler.com]
> > Gesendet: Mittwoch, 17. Oktober 2012 09:47
> > An: users@openjpa.apache.org
> > Betreff: Antwort: AW: @Enumerated(EnumType.ORDINAL) in JPQL
> > CASE WHEN generates invalid SQL using name of Enumeration
> > member, not ordinal
> >
> > Hello,
> >
> > that is not working, as you cannot simply use a method call
> > withni JPQL ... I think this is a severe bug in the implementation.
> >
> > I tried to build up a query like this using Criteria API and
> > Criteria API only allows to provide the enumeration at all
> > when using a selectCase(), but the generated SQL is wrong too.
> >
> > This should be fixed, as the @Enumeration annotation's type
> > is not taken into account when generating the resulting XML.
> >
> > Regards,
> >
> > Heiko
> >
> > --
> > Dr.-Ing. Heiko Kopp / Fa. Vision iT media GmbH
> >
> >
> >
> >
> > john.bobl...@bertschi.com
> > 10.10.2012 17:55
> > Bitte antworten an
> > users@openjpa.apache.org
> >
> >
> > An
> > users@openjpa.apache.org
> > Kopie
> >
> > Thema
> > AW: @Enumerated(EnumType.ORDINAL) in JPQL CASE WHEN generates
> > invalid SQL
> > using name of Enumeration member, not ordinal
> >
> >
> >
> >
> >
> >
> > Hello,
> >
> > Shouldn't you be using
> >
> > WHEN
> > model.protocol.ApprovalState.IN_PROCESS.ordinal()
> > THEN 1
> >
> > Just an idea ...
> >
> > John
> >
> >
> > > -----Ursprüngliche Nachricht-----
> > > Von: it-media.k...@daimler.com [mailto:it-media.k...@daimler.com]
> > > Gesendet: Mittwoch, 10. Oktober 2012 11:18
> > > An: users@openjpa.apache.org
> > > Betreff: @Enumerated(EnumType.ORDINAL) in JPQL CASE WHEN
> > > generates invalid SQL using name of Enumeration member,
> not ordinal
> > >
> > > Hello,
> > >
> > > I've come across an issue with a column used within a JQPL
> > > CASE WHEN Selection.
> > >
> > > The column's definition is as follows:
> > >
> > > @Column(name = "GEN_STATUS")
> > > @Enumerated(EnumType.ORDINAL)
> > > private ApprovalState state;
> > >
> > > The JPQL used here is
> > >
> > > SELECT c,
> > > ( CASE c.approval.state
> > > WHEN
> model.protocol.ApprovalState.IN_PROCESS THEN 1
> > > WHEN
> > > model.protocol.ApprovalState.RELATION_CHECK THEN 2
> > > WHEN
> model.protocol.ApprovalState.IN_APPROVAL THEN 3
> > > WHEN
> > model.protocol.ApprovalState.NEEDS_SCORING THEN 4
> > > WHEN
> > > model.protocol.ApprovalState.ADOPTION_REQUEST_IN_PROCESS THEN 5
> > > WHEN
> > > model.protocol.ApprovalState.ADOPTION_REQUESTED THEN
> > > 6
> > > WHEN model.protocol.ApprovalState.APPROVED THEN 7
> > > WHEN
> > > model.protocol.ApprovalState.APPROVED_WITH_COLLATERALS THEN 8
> > > WHEN model.protocol.ApprovalState.ACTIVATED THEN 9
> > > WHEN
> model.protocol.ApprovalState.CANCELLED THEN 10
> > > WHEN model.protocol.ApprovalState.DECLINED THEN 11
> > > WHEN model.protocol.ApprovalState.CREATED THEN 12
> > > WHEN model.protocol.ApprovalState.DEDUCTED THEN 13
> > > WHEN model.protocol.ApprovalState.DELETED THEN 14
> > > WHEN
> > > model.protocol.ApprovalState.PROPOSAL_CANCELLED THEN
> > > 15
> > > ELSE 0
> > > END ) AS vehicleStateOrder
> > > FROM DbContract c WHERE c.person.personId = :personId ORDER
> > > BY vehicleStateOrder, c.insertDate
> > >
> > > However, when this JPQL is transferred to SQL, e.g. ORACLE in
> > > this case throws the following error:
> > >
> > > ORA-00932: Inkonsistente Datentypen: NUMBER erwartet,
> CHAR erhalten
> > >
> > > A relevant part of the query is:
> > >
> > > SELECT * FROM (SELECT t0.VTR_ID AS c0, CASE t2.GEN_STATUS
> > > WHEN 'IN_PROCESS' THEN 1 WHEN 'RELATION_CHECK' THEN 2 WHEN
> > > 'IN_APPROVAL' ...
> > >
> > > The problem here is, that as the column definition denotes,
> > > not the ordinal number, but the enumeration name is used.
> > >
> > > Wer are using WebSphere 8.0.0.4 which includes
> > > openjpa-2.1.2-SNAPSHOT-r422266:1333100.
> > >
> > > Can somebody please tell me, what we can do to get this query
> > > working or is this a bug in the JPQL parsing system?
> > >
> > > Best regards,
> > >
> > > Heiko
> > >
> > > --
> > > Dr.-Ing. Heiko Kopp / Fa. Vision iT media GmbH
> > >
> > > If you are not the intended addressee, please inform us
> > > immediately that you have received this e-mail in error, and
> > > delete it. We thank you for your cooperation.
> >
> >
> > If you are not the intended addressee, please inform us
> > immediately that you have received this e-mail in error, and
> > delete it. We thank you for your cooperation.
>
>
> If you are not the intended addressee, please inform us
> immediately that you have received this e-mail in error, and
> delete it. We thank you for your cooperation.
If you are not the intended addressee, please inform us immediately that you
have received this e-mail in error, and delete it. We thank you for your
cooperation.
