Query Report - ORDER BY after UNION Two Tables

ERPNext
1

Hello Community,

I created custom Doctype “Flexi Loan”, and now I want to create a simple Query Report for it.

I tried below Query which is working perfect as shown in SS below.

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'

image
image713×411 22.9 KB

Now I want to use ORDER BY for column Voucher Date.
But I am getting below error.

pymysql.err.OperationalError: (1054, "Unknown column 'Voucher Date' in 'order clause'")

Query after ORDER BY

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'
ORDER BY `Voucher Date`

I used below syntax also.

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

As mentioned here.
https://dev.mysql.com/doc/refman/5.7/en/union.html#:~:text=To%20additionally%20maintain,a%20table%20name.

But I am getting below Error.

Query must be of SELECT or read-only WITH type.

Any hint and solution is highly appreciated.
Thanks.

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
3

Thanks a lot @NCP.
Its working perfect.

4

@umarless , can you Please try this:
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’
ORDER BY “Voucher Date”;

5

Thanks @Krishn for response.
Its not giving any error but column is not getting sorted.