Reputation: 39
I have a requirement where i need to fetch number of record both the table using JPA native UNION query for Pagination
table 1=>txrh_bcaterms
table 2=> txrm_bcaterms
Ex: txrh_bcaterms UNION txrm_bcaterms
In Below code i am trying to fetching the record in both table Using UNION
@Query(nativeQuery = true,value= "SELECT "
+ " customerid, "
+ "finance_type, "
----------
-------etc----
+ "institution, "
+ "limit_groupid, "
+ "tx_booking_location, "
+ "finance_tenor_days, "
+ "margin_type , "
+ "additional_margin_percentage, "
+ "margin_percentage, "
+ "stale_document_condition_proforma_fin, "
+ "customer_name "
+ "from "
+ "txrh_bcaterms "
+ "where "
+ " institution=:institution "
+ "AND tx_booking_location=:txBookingLocation "
+ "OR finance_type=:financeType "
+ "OR customerid=:customerID "
+ "OR limit_groupid=:limitGroupID "
+ "UNION "
+ "SELECT "
+ "customerid, "
+ "finance_type, "
----------
-------etc----
+ "institution, "
+ "limit_groupid, "
+ "tx_booking_location, "
+" finance_tenor_days, "
+ "margin_type, "
+ "additional_margin_percentage, "
+ "margin_percentage, "
+ "stale_document_condition_proforma_fin, "
+ "customer_name "
+ "from txrm_bcaterms "
"
+ " institution=:institution "
+ "AND tx_booking_location=:txBookingLocation "
+ "OR finance_type=:financeType "
+ "OR customerid=:customerID "
+ "OR limit_groupid=:limitGroupID "
)
Page<Map<String,Object>> historyAndMaster(@Param("institution") String institution, @Param("txBookingLocation") String txBookingLocation,
@Param("financeType") String financeType, @Param("customerID") String customerID, @Param("limitGroupID") String limitGroupID, Pageable page);
NOTE:Actually stepid is the primary key txrh_bcaterms table and stepid is not there txrm_bcaterms table
ERROR ------- in ui i passed institution =BC/AC/EC then UNION query able to execute and getting data but if i passed SC then getting below exception
Caused by ERROR: each UNION query must have the same number of columns
Position: 581
at org.axonframework.axonserver.connector.ErrorCode.lambda$static$15(ErrorCode.java:107)
... 9 common frames omitted
=>If there is no same number of columns then how it is worked BC/AC/EC
I am new the Spring Data JPA Could you please help me to resolve this issue
Any other better suggestion welcome!
Upvotes: 0
Views: 1136