rama kishan
rama kishan

Reputation: 39

UNION in Spring data JPA native query two table

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

Answers (0)

Related Questions