Блог

Я хочу преобразовать приведенный ниже SQL — запрос в hql-запрос.Я приложил все необходимые pojos. Я пытаюсь преобразовать приведенный ниже запрос в запрос hibernate, но он не работает для меня. Пожалуйста, изучите его и помогите мне преобразовать его в необходимый формат hql, чтобы он работал должным образом. Все pojo, необходимые для преобразования, прилагаются ниже, ознакомьтесь с ними. Постарайтесь решить эту проблему как можно скорее. Я немного спешу, так что постарайся ответить мне как можно скорее, пожалуйста.

 SELECT
    c.*
FROM
    (
        SELECT DISTINCT
            b.*
        FROM
            (
                SELECT
                    a.gcs_account_id,
                    a.msisdn,
                    a.partner_code    bankid,
                    0                 AS processingstatus ----, 'MF' AS FeeType, coalesce(mf.monthly_bill_amount, 0) mf
                FROM
                    (
                        SELECT
                            MIN(addition_date) fechaafilia,
                            a.gcs_account_id,
                            a.partner_code,
                            a.msisdn
                        FROM
                            r_gcscustomer_account_m        c,
                            r_gcscustomer_funding_acct_mp  a,
                            monthly_gcs_bill_payment_l     mf
                        WHERE
                                c.gcs_account_id = a.gcs_account_id
                            AND a.status = 'A'
                            AND c.status = 'A' ---AND A.GCS_ACCOUNT_ID=2501
                            AND mf.gcs_account_id = a.gcs_account_id
                            AND mf.collector_status IN ( '0', '1' )
                            AND coalesce(mf.monthly_bill_amount, 0) <> 0
--AND a.partner_code=54
                        GROUP BY
                            a.partner_code,
                            a.gcs_account_id,
                            a.msisdn
                        ORDER BY
                            a.gcs_account_id,
                            a.msisdn,
                            fechaafilia
                    ) a
                WHERE
                    ( a.fechaafilia, a.gcs_account_id, a.msisdn ) IN (
                        SELECT
                            MIN(fechaafilia), gcs_account_id, msisdn
                        FROM --- Assume the FIRST enrolled account of the Customer as the PRIMARY account
                            (
                                SELECT
                                    MIN(addition_date) fechaafilia,
                                    a.gcs_account_id,
                                    a.partner_code,
                                    a.msisdn
                                FROM
                                    r_gcscustomer_account_m        c,
                                    r_gcscustomer_funding_acct_mp  a
                                WHERE
                                        c.gcs_account_id = a.gcs_account_id
                                    AND a.status = 'A'
                                    AND c.status = 'A' ---AND A.GCS_ACCOUNT_ID=2501
--AND a.partner_code='54'
                                GROUP BY
                                    a.partner_code,
                                    a.gcs_account_id,
                                    a.msisdn
                                ORDER BY
                                    a.gcs_account_id,
                                    a.msisdn,
                                    fechaafilia
                            )
                        GROUP BY
                            gcs_account_id,
                            msisdn
                    )
                UNION ALL
                SELECT
                    a.gcs_account_id,
                    a.msisdn,
                    a.partner_code    bankid,
                    0                 AS processingstatus ----, 'NSF', COALESCE(nsf_Amount, 0) nsf
                FROM
                    (
                        SELECT
                            MIN(addition_date) fechaafilia,
                            a.gcs_account_id,
                            a.partner_code,
                            a.msisdn
                        FROM
                            r_gcscustomer_account_m        c,
                            r_gcscustomer_funding_acct_mp  a,
                            ft_nsf_t_l                     nsf,
                            ft_transfer_t_o                t
                        WHERE
                                c.gcs_account_id = a.gcs_account_id
                            AND a.status = 'A'
                            AND c.status = 'A' ---AND A.GCS_ACCOUNT_ID=2501
                            AND nsf.gcs_account_id = a.gcs_account_id
                            AND nsf_amount <> 0
                            AND nsf.is_nsf_handled_status IN ( '0', '1' )
                            AND nsf.gcs_account_id = t.gcs_account_id
                            AND nsf.gcs_sequenceno = t.gcs_sequenceno
                            AND t.msisdn = a.msisdn
--AND a.partner_code = 54
                        GROUP BY
                            a.partner_code,
                            a.gcs_account_id,
                            a.msisdn
                        ORDER BY
                            a.gcs_account_id,
                            a.msisdn,
                            fechaafilia
                    ) a
                WHERE
                    ( a.fechaafilia, a.gcs_account_id, a.msisdn ) IN (
                        SELECT
                            MIN(fechaafilia), gcs_account_id, msisdn
                        FROM --- Assume the FIRST enrolled account of the Customer as the PRIMARY account or Default Account
                            (
                                SELECT
                                    MIN(addition_date) fechaafilia,
                                    a.gcs_account_id,
                                    a.partner_code,
                                    a.msisdn
                                FROM
                                    r_gcscustomer_account_m        c,
                                    r_gcscustomer_funding_acct_mp  a
                                WHERE
                                        c.gcs_account_id = a.gcs_account_id
                                    AND a.status = 'A'
                                    AND c.status = 'A' ---AND A.GCS_ACCOUNT_ID=2501
--AND a.partner_code=54
                                GROUP BY
                                    a.partner_code,
                                    a.gcs_account_id,
                                    a.msisdn
                                ORDER BY
                                    a.gcs_account_id,
                                    a.msisdn,
                                    fechaafilia
                            )
                        GROUP BY
                            gcs_account_id,
                            msisdn
                    )
            ) b
        WHERE
            b.bankid = 68
        ORDER BY
            gcs_account_id
    ) c

 

 public class RGcscustomerAccountM implements java.io.Serializable {

    private Long gcsAccountId;
    private RBillingCycleM RBillingCycleM;
    private String customerName;
    private String billingAddress;
    private Date dob;
    private String id;
    private String idType;
    private String status;
    private String customerClass;
    private Boolean closureStatus;
    private Timestamp creationDate;
    private Timestamp lastmodifiedDate;
    private BigDecimal msisdnChangeCounter;
    private BigDecimal transactionCount;

    private Set RGcscustomerMsisdnMps = new HashSet(0);
    private Set preGcscustomerEnrollmentMs = new HashSet(0);
    private Set RGcscustomerFundingAcctMps = new HashSet(0);
    private Set customerMonthlyBillTOs = new HashSet(0);}

 

 public class RGcscustomerFundingAcctMp implements java.io.Serializable {

    private RGcscustomerFundingAcctMpId id;
    private PreGcscustomerEnrollmentM preGcscustomerEnrollmentM;
    private TransactionM transactionM;
    private String accountType;
    private String status;
    private Integer priority;
    private Timestamp additionDate;
    private Timestamp lastmodifiedDate;
    private String aliass;
    private BigDecimal partnerCode;
    private Set ftReversalTLs = new HashSet(0);
    private Set amCustomerActivityHTBs = new HashSet(0);
    private String currency;}
 

 public class MonthlyGcsBillPaymentL implements java.io.Serializable {

    private long monthlybillPaymentId;
    private BigDecimal gcsSequenceno;
    private BigDecimal billingCycleid;
    private BigDecimal gcsAccountId;
    private BigDecimal monthlyBillAmount;
    private Date billDueDate;
    private Date billPaidDate;
    private BigDecimal inactivityFee;
    private String collectorStatus;
    private Date collectorStartdate;
    private String isBatchProcess;
}