Блог

У меня есть требование, чтобы получить все данные в таблице истории и несоответствующие данные из главной таблицы. Используя запрос ОБЪЕДИНЕНИЯ, можно получить как данные таблицы.

НО проблема заключалась в том, что таблица истории 10 дочерняя таблица и главная таблица также 10 дочерняя таблица. Не удалось получить данные дочерней таблицы. Я также использовал запрос соединения для извлечения данных дочерней таблицы, но не смог извлечь данные дочерней таблицы, нажимая на свагер, который он загружал, и не смог извлечь.а также не получил никаких ошибок.

Любая помощь будет признательна.

 @Query(nativeQuery = true,value= "SELECT                                                                                                          "            
                             " a.customerid AS customerID,                                                                                          "
                             " a.stepid AS stepID,                                                                                                  "
                             "a.finance_percentage AS financePercentage,                                                                            "
                             "a.finance_tenor_days AS financeTenor,                                                                                 "
                             "a.finance_type AS financeType,                                                                                        "
                             "a.institution AS institution,                                                                                         "
                           etc...............                                      "
                            "a.customer_name AS customerName                                                                                        "
                             "FROM  txrh_bcaterms a"
                            " INNER JOIN txrh_bcaterms_allowed_finance_currencies b ON b.institution = a.institution AND b.customerid=a.customerid AND b.tx_booking_location=a.tx_booking_location AND b.finance_type=a.finance_type AND b.limit_groupid=a.limit_groupid             "
                                            etc...........
                          
                            " INNER JOIN txrh_bcaterms_allowed_proforma_invoice_documents j ON j.institution = a.institution  AND j.customerid=a.customerid AND j.tx_booking_location=a.tx_booking_location AND b.finance_type=a.finance_type AND b.limit_groupid=a.limit_groupid    "
                            " INNER JOIN txrh_bcaterms_allowed_payments_third_parties k ON k.institution = a.institution  AND k.customerid=a.customerid AND k.tx_booking_location=a.tx_booking_location   AND b.finance_type=a.finance_type AND b.limit_groupid=a.limit_groupid      "
                            "where step_status IN ('SAV', 'REJ') "   " AND a.institution = :institution                        "
                            " AND (COALESCE(:txBookingLocation, null) is null or (a.tx_booking_location IN :txBookingLocation))                     "
                            "UNION                                                                                                                           "

                  "SELECT                                                                                                                          "
                             "a.customerid AS customerID,                                                                                            "
                             "null AS stepID,                                                                                                      "
                             "a.finance_percentage AS financePercentage,                                                                             "
                            " a.finance_tenor_days AS financeTenor,                                                                                  "
                             "a.finance_type AS financeType,                                                                                         "
                       
                             "a.institution AS institution,                                                                                          "
                             "a.limit_groupid AS limitGroupID,                                                                                       "
                             "null AS checkerID,                                                                                                   "
                                  etc..........                                   "
                             "a.customer_name AS customerName                                                                                        "
                             "FROM  txrm_bcaterms a "
                             " INNER JOIN txrm_bcaterms_allowed_finance_currencies b ON b.institution = a.institution AND b.customerid=a.customerid  AND b.tx_booking_location=a.tx_booking_location  AND b.finance_type=a.finance_type AND b.limit_groupid=a.limit_groupid            "
                                 etc...............
                             " INNER JOIN txrm_bcaterms_allowed_proforma_invoice_documents j ON j.institution = a.institution  AND j.customerid=a.customerid AND j.tx_booking_location=a.tx_booking_location AND b.finance_type=a.finance_type AND b.limit_groupid=a.limit_groupid   "
                             " INNER JOIN txrm_bcaterms_allowed_payments_third_parties k ON k.institution = a.institution  AND k.customerid=a.customerid AND k.tx_booking_location=a.tx_booking_location   AND b.finance_type=a.finance_type AND b.limit_groupid=a.limit_groupid     "
                             "where (a.institution, a.tx_booking_location) NOT IN                                                  "  
                             " (select a.institution, a.tx_booking_location from txrh_bcaterms a  where step_status IN ('SAV', 'REJ', 'PNR') "
                             " AND (COALESCE(:txBookingLocation, null) is null or ( a.tx_booking_location IN :txBookingLocation))"
                             " AND a.institution = :institution ) "
                             " AND a.institution = :institution "
                             " AND (COALESCE(:txBookingLocation, null) is null OR a.tx_booking_location IN :txBookingLocation)" ,
                          
                            
                        countQuery = " SELECT count(*) FROM ("
                                  " SELECT institution AS institution, tx_booking_location AS txBookingLocation"
                                  " FROM txrh_bcaterms where step_status IN ('SAV', 'REJ') "
                                  " AND institution = :institution "
                                  " AND (COALESCE(:txBookingLocation, null) is null or (tx_booking_location IN :txBookingLocation)) "
                                  " UNION "   " SELECT institution AS institution, tx_booking_location AS txBookingLocation "
                                  " FROM txrm_bcaterms   where (institution, tx_booking_location) NOT IN "
                                  " (select institution, tx_booking_location from txrh_bcaterms  where step_status IN ('SAV', 'REJ', 'PNR') "
                                  " AND (COALESCE(:txBookingLocation, null) is null or  tx_booking_location IN :txBookingLocation)"
                                  " AND institution = :institution ) "
                                  " AND institution = :institution "
                                  "AND (COALESCE(:txBookingLocation, null) is null OR tx_booking_location IN :txBookingLocation)"
                                  ")as cnt")                                                                                                                                                                                                                    
    Page<ITXBCATermsHistoryBookingLocationAndInstitutionDTO> historyAndMaster(
            @Param("institution") String institution,
            @Param("txBookingLocation")  List<String> txBookingLocation,
            Pageable page);