CODEWITHSUNDEEP
oracle-databasesqlplus
user7868404
user7868404

Reputation: 21

oracle sql plus script is too long

select (select SUM(t.fees_amount)from unit u, payment p, type_of_fees t WHERE u.unit_id = p.unit_id AND p.stof = t.stof AND u.unit_id LIKE '%U1001%'
        AND p.stat = 'not payed') "BLOCK 1(Not Payed)",

        (select SUM(t.fees_amount)from unit u, payment p, type_of_fees t WHERE u.unit_id = p.unit_id AND p.stof = t.stof AND u.unit_id LIKE '%U1001%'
        AND p.stat = 'payed') "BLOCK 1(Payed)",

        (select SUM(t.fees_amount)from unit u, payment p, type_of_fees t WHERE u.unit_id = p.unit_id AND p.stof = t.stof AND u.unit_id LIKE '%U1002%'
        AND p.stat = 'not payed') "BLOCK 2(Not Payed)",

        (select SUM(t.fees_amount)from unit u, payment p, type_of_fees t WHERE u.unit_id = p.unit_id AND p.stof = t.stof AND u.unit_id LIKE '%U1002%'
        AND p.stat = 'payed') "BLOCK 2(Payed)"

from dual;

is there any other method to deal with this?

Upvotes: 0

Views: 263

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

You can use conditional aggregation for this:

select sum(case when u.unit_id like '%U1001%' and p.stat = 'not payed' then t.fees_amount else 0 end) as "BLOCK 1(Not Payed)",
    sum(case when u.unit_id like '%U1001%' and p.stat = 'payed' then t.fees_amount else 0 end) as "BLOCK 1(Payed)",
    sum(case when u.unit_id like '%U1002%' and p.stat = 'not payed' then t.fees_amount else 0 end) as "BLOCK 2(Not Payed)",
    sum(case when u.unit_id like '%U1002%' and p.stat = 'payed' then t.fees_amount else 0 end) as "BLOCK 2(Payed)"
from unit u
join payment p on u.unit_id = p.unit_id
join type_of_fees t on p.stof = t.stof;

Also you should always use explicit join syntax instead of old comma based joins.

Upvotes: 1

Related Questions