Query Report - ORDER BY after UNION Two Tables

ERPNext
2

Hi @umarless,

Please try it.

SELECT * FROM (
    SELECT
        name as "Voucher No.:Data:250",
        loan_date as "Voucher Date:Date:150",
        loan_amount as "Amount:Currency:150"
    FROM `tabFlexi Loan`
    WHERE
        employee = %(employee)s AND docstatus = 1
    UNION
    SELECT
        name as "Voucher No.:Data:250",
        payroll_date as "Voucher Date:Date:150",
        amount * -1 as "Amount:Currency:150"
    FROM `tabAdditional Salary`
    WHERE
        employee = %(employee)s AND docstatus = 1 
        AND custom_status = 'Paid' AND salary_component = 'Loan Repay'
) AS combined_data
ORDER BY 2 ASC;  -- Order by the second column in the result set

Set your according.

Thank You!

2 Likes