SQL & PL/SQL

Hi,

I am sorry about this question. Seems weird to me also to do such thing but this is what the business requirement is.

Here's an example of what the requirement is


Transaction Type __ Result ____ Total_Count
A __________________ Success ______ 25
A __________________ Failed ________ 10
A __________________ Error _________ 16
B __________________ Success_______ 2
C __________________ Success________3
C __________________ Failed__________4

In summary, transaction type A has 25 success, 10 failed, and 16 error, while type B has 2 success, and lastly type C has 3 success and 4 failed.

The boss wants the query to return something like this.
Transaction Type __ Result ____ Total_Count
A __________________ Success ______ 25
A __________________ Failed ________ 10
A __________________ Error _________ 16
B __________________ Success_______ 2
B __________________ Failed ________ 0
B __________________ Error _________ 0
C __________________ Success________3
C __________________ Failed__________4
C __________________ Error _________ 0

So it means that what he wants is, if one transaction type has a value for 'Result', the other transaction types must also have that 'Result' and set the count to '0'. So if Transaction Type 'A' has another Result called 'Pending' and the count is 5, all other transaction types (B and C) must return a row where 'Pending' = 0.

I cannot think of any possible way. The statement I have to prepare is called via JDBC. The framework is existing and wish I could just manipulate the result in the java program but in this case I shouldn't.

Your help will be very much appreciated.

Regards,
Mark