mybatis_111

SELECT
    tx.*,
    r2p_visa.end_to_end_id AS endToEndId,
    r2p_visa.debtor_first_name AS debtorFirstName,
    r2p_visa.debtor_last_name AS debtorLastName,
    r2p_visa.debtor_alias AS debtorAlias,
    r2p_visa.due_date AS dueDate,
    r2p_visa.transaction_status_visa AS transactionStatusVisa,
    r2p_visa.requested_amount AS requestedAmount,
    r2p_visa.requested_amount_currency AS requestedAmountCurrency,
    r2p_visa.reference_date AS referenceDate,
    r2p_visa.reference_value AS referenceValue,
    r2p_visa.reference_type_additional_info AS referenceTypeAdditionalInfo,
    r2p_visa.status_reason_visa AS statusReason,
    r2p_visa.cancellation_reason_additional_info AS cancellationReasonAdditionalInfo,
    r2p_visa.message_visa AS messageVisa,
    r2p_visa.error_code_visa AS errorCodeVisa,
    r2p_visa.error_message_visa AS errorMessageVisa,
    r2p_visa.error_details_visa AS errorDetailsVisa,
    paymentPurpose.id AS paymentPurpose_id,
    paymentPurpose.type AS paymentPurpose_type,
    paymentPurpose.code AS paymentPurpose_code,
    paymentPurpose.name AS paymentPurpose_name,
    COALESCE(paymentPurpose.description_translations->>?,
             paymentPurpose.description_translations->>'ru',
             paymentPurpose.description) AS paymentPurpose_description,
    paymentPurpose.additional AS paymentPurpose_additional,
    cancelReason.id AS cancelReason_id,
    cancelReason.type AS cancelReason_type,
    cancelReason.code AS cancelReason_code,
    cancelReason.name AS cancelReason_name,
    COALESCE(cancelReason.description_translations->>?,
             cancelReason.description_translations->>'ru',
             cancelReason.description) AS cancelReason_description,
    cancelReason.additional AS cancelReason_additional,
    debtorAgent.id AS debtorAgent_id,
    debtorAgent.code AS debtorAgent_code,
    debtorAgent.name AS debtorAgent_name,
    (CASE WHEN debtorAgent.deleted THEN 0 ELSE 1 END) AS debtorAgent_active,
    referenceType.id AS referenceType_id,
    referenceType.type AS referenceType_type,
    referenceType.code AS referenceType_code,
    referenceType.name AS referenceType_name,
    COALESCE(referenceType.description_translations->>?,
             referenceType.description_translations->>'ru',
             referenceType.description) AS referenceType_description,
    referenceType.additional AS referenceType_additional,
    statusReasonVisa.id AS statusReasonVisa_id,
    statusReasonVisa.type AS statusReasonVisa_type,
    statusReasonVisa.code AS statusReasonVisa_code,
    statusReasonVisa.name AS statusReasonVisa_name,
    COALESCE(statusReasonVisa.description_translations->>?,
             statusReasonVisa.description_translations->>'ru',
             statusReasonVisa.description) AS statusReasonVisa_description,
    statusReasonVisa.additional AS statusReasonVisa_additional,
    r2p_retail.phone AS phone,
    r2p_retail.message AS message,
    r2p_retail.transfer_type_name AS transferTypeName,
    r2p_retail.request_form AS requestForm,
    r2p_retail.error_decline_details AS errorDeclineDetails,
    m.id AS m_id,
    m.idn AS m_idn,
    m.short_name AS m_shortName,
    p.id AS p_id,
    p.city AS p_city,
    p.name AS p_name,
    p.address AS p_address,
    c.id AS c_id,
    c.login AS c_login,
    c.role AS c_role,
    c.name AS c_name,
    cancellationUser.id AS cancellationUser_id,
    cancellationUser.login AS cancellationUser_login,
    cancellationUser.role AS cancellationUser_role,
    cancellationUser.name AS cancellationUser_name
FROM general_transaction tx
LEFT JOIN request_to_pay_visa_transaction_details r2p_visa
    ON tx.type = 'R2P_VISA' AND r2p_visa.id = tx.id
LEFT JOIN request_to_pay_retail_transaction_details r2p_retail
    ON tx.type = 'R2P_RETAIL' AND r2p_retail.id = tx.id
LEFT JOIN dictionary referenceType
    ON tx.type = 'R2P_VISA' AND r2p_visa.reference_type_id = referenceType.id
LEFT JOIN dictionary cancelReason
    ON tx.type = 'R2P_VISA' AND r2p_visa.cancellation_reason_id = cancelReason.id
LEFT JOIN dictionary statusReasonVisa
    ON tx.type = 'R2P_VISA' AND r2p_visa.status_reason_visa = statusReasonVisa.code
    AND statusReasonVisa.type = 'VISA_STATUS_REASON'
LEFT JOIN dictionary paymentPurpose
    ON tx.type = 'R2P_VISA' AND r2p_visa.payment_purpose_id = paymentPurpose.id
LEFT JOIN request_to_pay_visa_reference_data debtorAgent
    ON tx.type = 'R2P_VISA' AND r2p_visa.debtor_agent_id = debtorAgent.id
LEFT JOIN merchant m
    ON tx.merchant_id = m.id
LEFT JOIN pos p
    ON tx.pos_id = p.id
LEFT JOIN users c
    ON tx.user_id = c.id
LEFT JOIN users cancellationUser
    ON tx.type = 'R2P_VISA' AND r2p_visa.cancellation_user_id = cancellationUser.id
WHERE 1 = 1