Subqueries Basic Error Before Execute

I don't whats issue with this query and after multiple times trying I'm unable to run. Kindly point out whats wrong with this how can I fix that. Thanks. SELECT cd.dr

Solution 1:

You can't give aliases to subqueries in a UNION, and you can't refer to results from the subqueries using an alias. And the column aliases in a UNION always come from the aliases in the first subquery, so you can't refer to dr.

What you can do is:

SELECT amount
FROM (
    SELECT'cc'AS type, SUM(credit_amount) as amount FROM cust_credit
    UNIONALLSELECT'cd'AS type, SUM(debit_amount) AS amount FROM cust_debit
) x
WHERE type ='cd'

Or instead of using UNION, you can put the queries in the SELECT list.

SELECT dr AS amount
FROM (
    SELECT (SELECT SUM(credit_amount) FROM cust_credit) AS cr,
           (SELECT SUM(debit_amount) FROM cust_debit) AS dr
) x