Re: [eclipselink-users] Data from multiple table

[Tom Ware <tom.ware@xxxxxxxxxx>]

You have to specify the attribute you are joining.

i.e.

SELECT new net.msl.usicaf.jpa.entities.transformed.Temp(mc.companyName)
FROM  Certificate c JOIN  c.user j JOIN j.masterCompany u
WHERE c.userKeyField=u.userkeyfield
AND  u.masterCompanyID= c.masterCompanyID
AND u.certCommonName like '1%'
AND c.certState='APPLY'
AND u.masterCompanyID IN (0,3,6,9,12,7,18,10,1,4,2,13,5,16,11,14,17,8)]

Tanujit Chowdhury wrote:
> Hi,
> Thanks for the input.
> I am getting this exception after modifying the query:
>
> Caused by: Exception [EclipseLink-8024] (Eclipse Persistence Services - 
> 1.0.2 (B
> uild 20081024)): org.eclipse.persistence.exceptions.JPQLException
> Exception Description: Syntax error parsing the query [SELECT new 
> net.msl.usicaf
> .jpa.entities.transformed.Temp(mc.companyName) FROM  Certificate c JOIN 
> User u J
> OIN MasterCompany mc WHERE c.userKeyField=u.userkeyfield AND 
> u.masterCompanyID=m
> c.masterCompanyID AND u.certCommonName like '1%' AND c.certState='APPLY' 
> AND u.m
> asterCompanyID IN (0,3,6,9,12,7,18,10,1,4,2,13,5,16,11,14,17,8)], line 
> 1, column
>  102: syntax error at [u].
> Internal Exception: MismatchedTokenException(66!=71)
>         at 
> org.eclipse.persistence.exceptions.JPQLException.syntaxErrorAt(JPQLEx
> ception.java:355)
>
> The query is in BLUE.
>
> Can you tell me what should be the problem here?
>
> Tanujit
>
> On Tue, Aug 10, 2010 at 8:20 PM, Samba <saasira@xxxxxxxxx 
> <mailto:saasira@xxxxxxxxx>> wrote:
>
>     You can have an ordinary class that represents your desired
>     structure, and then use it for transforming the results of the JPQL,
>     for example you can use an object Temp who has a constructor that
>     takes the arguments you want in the resultset; then you can use some
>     thing like:
>
>     SELECT new com.company.product.Temp(a.i,a.j,a.k,b.l,b.m,c.n,c.p)
>     FROM A a JOIN B b JOIN C c WHERE a.z=b.z  AND a.k=c.p);
>
>     In Hibernate, there is some thing called Transformer API that lets
>     you configure the transformer object for each query instead of using
>     this constructor approach.
>
>     Eclipse Link also have a similar API, but I haven't used it so far,
>     so I cannot comment on whether that will be useful to you in this
>     scenario.
>
>     Regards,
>     Samba
>
>
>
>     On Tue, Aug 10, 2010 at 10:54 AM, Tanujit Chowdhury
>     <chowdhury.tanujit@xxxxxxxxx <mailto:chowdhury.tanujit@xxxxxxxxx>>
>     wrote:
>
>         Hi,
>         I am using EclipseLink 1.0 and SQL Server.
>         I am pretty new to the JPQL and EclipseLink.
>
>         I have two tables A and B. I have execute a query like :
>
>         select a,x, b.y from A x, B y where a.z=b.z
>
>         How to write its equivalent JPQL and what would be its output
>         object? In general when we execute JPQL it returns an
>         Object/list of Object that represents a database table. In this
>         case it is mixed.
>
>         -- 
>         Tanujit Chowdhury
>
>         _______________________________________________
>         eclipselink-users mailing list
>         eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@xxxxxxxxxxx>
>         https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>
>
>     _______________________________________________
>     eclipselink-users mailing list
>     eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@xxxxxxxxxxx>
>     https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>
>
>
> --
> Tanujit Chowdhury
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users