Hello All ,
I am facing problem with batch delete. I read on the forum that batch delete doesn't need from clause and doesn't work with alias. When i tried it with from clause it was throwing NullpointerException.
After reading on forum i removed from clause. But now i am facing SQLGrammerException. So can anybody tell me how should i write my query ?

All required information is as follow :

]Hibernate version: 3.0

Mapping documents:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="qc.qbid.invoice.model.bo">
<class
name="InvoicePaymentChargeTo"
table="INVOICE_PAYMENT_CHARGETO"
>
<id
name="chargeToId"
type="integer"
column="CHARGE_TO_ID"
>
<generator class="increment"/>
</id>

<many-to-one
name="invoicePayment"
column="INVOICE_PAYMENT_ID"
class="InvoicePayment"
not-null="true"
>
</many-to-one>


</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():


final String deleteInvoicePaymentChargeTo = " delete InvoicePaymentChargeTo invoicePaymentChargeTo " +
" where invoicePayment in (select invoicePayment " +
" from InvoicePayment invoicePayment,InvoiceCharge invoiceCharge " +
" where invoicePayment.invoiceCharge.invoiceChargeId.invoiceBillingDevice = invoiceCharge.invoiceChargeId.invoiceBillingDevice and " +
" invoicePayment.invoiceCharge.invoiceChargeId.chargeType = invoiceCharge.invoiceChargeId.chargeType" +
" and invoiceCharge.invoiceChargeId.invoiceBillingDevice.invoice.invoiceId = "+invoice.getInvoiceId()+")" ;

this.sessionTmp.createQuery(deleteInvoicePaymentChargeTo).executeUpate();



Full stack trace of any exception that occurs:
15:06:21,187 ERROR JDBCExceptionReporter:58 - ORA-00904: "INVOICEPAYMENT": invalid identifier

15:06:21,187 ERROR InvoiceDAOHibImpl:? - org.hibernate.exception.SQLGrammarException: could not execute update query



Name and version of the database you are using:
Oracle 9i

The generated SQL (show_sql=true):
delete from INVOICE_PAYMENT_CHARGETO invoicepay0_ where (invoicePayment in (select invoicepay1_.INVOICE_PAYMENT_ID from INVOICE_PAYMENT invoicepay1_, INVOICE_CHARGE invoicecha2_, INVOICE_BILLING_DEVICE invoicebil3_ where (invoicepay1_.INVOICE_BILLING_DEVICE_ID=invoicecha2_.INVOICE_BILLING_DEVICE_ID and invoicepay1_.CHARGE_TYPE_ID=invoicecha2_.CHARGE_TYPE_ID and invoicebil3_.INVOICE_ID=115146 and invoicecha2_.INVOICE_BILLING_DEVICE_ID=invoicebil3_.INVOICE_BILLING_DEVICE_ID)))




Thanks in advance,
Vaibhav