0
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!
rama kishan
  • 39
  • 6
  • This is because you need to separate the columns your are selecting with commas. `select customerid, finance_type, institution, ...` – Scary Wombat Jun 16 '21 at 05:57
  • after adding comma institution error resolved but some other error getting Caused by ERROR: operator does not exist: character varying = bytea Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 3529 – rama kishan Jun 16 '21 at 06:18
  • `+ "step_status='SAV' " + " AND " + " step_status='REJ' ` impossible. – Scary Wombat Jun 16 '21 at 06:24
  • I removed your comment code but still getting Caused by ERROR: operator does not exist: character varying = bytea Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 3293 – rama kishan Jun 16 '21 at 06:41
  • `"tx_booking_location=:txBookingLocation ` - you are trying to obtain based upon a String array variable. – Scary Wombat Jun 16 '21 at 06:46
  • txBookingLocation it was resolved i am getting another issue can you please look – rama kishan Jun 17 '21 at 09:22
  • `UNION query must have the same number of columns` is fundamental. But of course with SQL you can get around it with something like `select name, age from tableA union select name, 0 from tableB` . In this example it assumes that tableB does not have the age column. – Scary Wombat Jun 17 '21 at 09:29
  • I have checked 4 to 5 times Table A and Table B has same number of column but i don't know why i am getting UNION query must have the same number of columns – rama kishan Jun 17 '21 at 11:16
  • could you please look to the below link JPA will not support UNION ? https://stackoverflow.com/questions/15062501/jpa-only-executes-first-select-statement-in-union – rama kishan Jun 18 '21 at 16:53

0 Answers0