I have to write criteria query for the following sql query
SELECT distinct ON(applicant.name,applicant_login.applicant_login_id) applicant_login.applicant_login_id, applicant.name, applicant_address.address1 , applicant_address.address2 , applicant_address.postoffice , applicant_address.district, applicant_address.state , applicant_address.pincode, applicant.pan_no , applicant.nationality FROM public.applicant INNER JOIN public.applicant_login ON (applicant_login.applicant_login_id=applicant.applicant_login_id) INNER JOIN public.application ON ( applicant.app_service_id = application.app_service_id) INNER JOIN public.stock_register ON (application.application_id = stock_register.application_id ) LEFT JOIN public.applicant_address ON ( applicant.app_service_id = applicant_address.app_service_id ) LEFT JOIN public.address_type ON (applicant_address.address_type = address_type.address_type_id) WHERE address_type.address_type_id = 'T' ORDER BY applicant.name,applicant_login.applicant_login_id,applicant.app_service_id desc
So I have written as follows
Criteria crt=getSession().createCriteria(Applicant.class,"ant"); crt.createAlias("ant.applicantLogin", "al"); crt.createAlias("ant.applications", "ap"); crt.createAlias("ap.stockRegisters", "stock"); crt.createAlias("ant.applicantAddresses","ads",CriteriaSpecification.LEFT_JOIN); crt.createAlias("ads.addressType","adst",CriteriaSpecification.LEFT_JOIN); ProjectionList proj=Projections.projectionList(); proj.add(Projections.distinct(Projections.property("ant.name"))); proj.add(Projections.distinct(Projections.property("al.applicantLoginId"))); proj.add(Projections.property("ads.address1")) ; proj.add(Projections.property("ads.address2")) ; proj.add(Projections.property("ads.postoffice")); proj.add(Projections.property("ads.district")); proj.add(Projections.property("ads.state")); proj.add(Projections.property("ads.pincode")); proj.add(Projections.property("ant.panNo")) ; proj.add(Projections.property("ant.nationality")) ; crt.addOrder(Order.asc("ant.name")); crt.addOrder(Order.asc("al.applicantLoginId")); crt.addOrder(Order.asc("ant.appServiceId")); crt.setProjection(proj); crt.add(Restrictions.eq("adst.addressTypeId","T"));
But execution of these query throws exception
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "distinct"
I can't understand what is wrong with this query.Please advice me.Thanks in advance
|