When I issue a getIteratorByQuery when using a QueryBySQL, the startAtIndex and endAtIndex are ignored. When I issue a getCollectionByQuery on the query, it fails because it is a union:
Query q = new QueryBySQL( PayeeTO.class, "SELECT distinct mail_to_foreign_addr1,mail_to_city,mail_to_addr1,date_last_changed,last_chan ged_by,payee_nm,mail_to_st,payee_id,mail_to_zip,mail_to_attn,tax_id,ap_vendo r_nmbr,mail_to_addr3,mail_to_addr2 FROM mcsp_payee " + "where tax_id like '%123%' " + " or payee_nm like '%ab%' " + "union SELECT distinct mail_to_foreign_addr1,mail_to_city,mail_to_addr1,date_last_changed,last_chan ged_by,payee_nm,mail_to_st,p.payee_id,mail_to_zip,mail_to_attn,tax_id,ap_ven dor_nmbr,mail_to_addr3,mail_to_addr2 FROM mcsp_payee p,contract c where c.payee_id = p.payee_id " + "and c.document_category = 'STUDY' " + "and (c.contract_nm like '%nort%' " + "or c.site_nmbr = 160)"); q.setStartAtIndex(10); q.setEndAtIndex(20); Collection payees = dao.retrieve(q); the generated SQL to get the result set works fine SELECT distinct mail_to_foreign_addr1,mail_to_city,mail_to_addr1,date_last_changed,last_chan ged_by,payee_nm,mail_to_st,payee_id,mail_to_zip,mail_to_attn,tax_id,ap_vendo r_nmbr,mail_to_addr3,mail_to_addr2 FROM mcsp_payee where tax_id like '%123%' or payee_nm like '%ab%' union SELECT distinct mail_to_foreign_addr1,mail_to_city,mail_to_addr1,date_last_changed,last_chan ged_by,payee_nm,mail_to_st,p.payee_id,mail_to_zip,mail_to_attn,tax_id,ap_ven dor_nmbr,mail_to_addr3,mail_to_addr2 FROM mcsp_payee p,contract c where c.payee_id = p.payee_id and c.document_category = 'STUDY' and (c.contract_nm like '%nort%' or c.site_nmbr = 160) but the EndAtIndex generates select count(*) FROM mcsp_payee where tax_id like '%123%' or payee_nm like '%ab%' union SELECT distinct mail_to_foreign_addr1,mail_to_city,mail_to_addr1,date_last_changed,last_chan ged_by,payee_nm,mail_to_st,p.payee_id,mail_to_zip,mail_to_attn,tax_id,ap_ven dor_nmbr,mail_to_addr3,mail_to_addr2 FROM mcsp_payee p,contract c where c.payee_id = p.payee_id and c.document_category = 'STUDY' and (c.contract_nm like '%nort%' or c.site_nmbr = 160)|select count(*) FROM mcsp_payee where tax_id like '%123%' or payee_nm like '%ab%' union SELECT distinct mail_to_foreign_addr1,mail_to_city,mail_to_addr1,date_last_changed,last_chan ged_by,payee_nm,mail_to_st,p.payee_id,mail_to_zip,mail_to_attn,tax_id,ap_ven dor_nmbr,mail_to_addr3,mail_to_addr2 FROM mcsp_payee p,contract c where c.payee_id = p.payee_id and c.document_category = 'STUDY' and (c.contract_nm like '%nort%' or c.site_nmbr = 160) which is illegal com.sybase.jdbc2.jdbc.SybSQLException: All queries in a SQL statement containing set operators must have an equal number of expressions in their target lists. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
