Outputting the query to see the rule num as a column rather than a row

[5peed0]

This is on a DB2 database...Let me try explaining the scenario in business terms and then let me break it up by the SQL.

The business problem:

An exception report is required when a customer qualifies from exemption and is behaving inappropriately based on certain rules.

A customer qualifies for exemption in the following conditions:
The customer is a British citizen; AND
The customer must have one of the following accounts with the bank:
Low Saving
Low Current

Customer breaches Low Risk Behaviour criteria in the following:
Rule 1. Having no Debits in excess of R5 000 per day;
Rule 2. No cross-border transactions may take place;
Rule 3. The balance in the account may not exceed R25 000 at any time
Rule 4. Having more than one account with Bank

The exception is when the customer qualifies from exemption and breaches any of the rules. It might be possible for the customer to breach more than one rule. However the customer must qualify for exemption in order to check them against the rules.

The database structure:
Customer table contains the usual info about the Customer
Account table contains the usual info about the account
Customer_Account contains all the accounts the customer has.
Summary table contains the daily totals for an account, per day.

The SQL:
Rule 1 and 3 are value based
Rule 2 needs to show which of the excluded transactions the customer has used
Rule 4 is showing additional accounts

RULE 1:
select 'Rule 1' as Rule_Num, c.customer_id, a.account_id, b.total_value, 'N/A' as TXN_TYPE
from tb_rf_account a, summary b, tb_rf_customer c
where b.txn_type='Debits'
and b.total_value>5000
and b.day='1 Jan 2007'
and a.account_id=b.account_id
and a.customer_id=c.customer_id
and c.country='England'

RULE 2:
select 'Rule 2' as Rule_Num, c.customer_id, a.account_id, b.total_value, d.txn_type_name as TXN_TYPE_NAME
from tb_rf_account a, summary b, tb_rf_customer c, tb_rf_txn_type d
where b.txn_type_name IN ('x', 'y', 'z')
and b.day='1 Jan 2007'
and a.account_id=b.account_id
and a.customer_id=c.customer_id
and d.txn_type_name=b.txn_type_name
and c.country='England'

RULE 3:

select 'Rule 3' as Rule_Num, c.customer_id, a.account_id, b.total_value, 'N/A' as TXN_TYPE_NAME
from tb_rf_account a, summary b, tb_rf_customer c
where b.txn_type='Debits'
and b.total_value>25000
and b.month='Jan'
and a.account_id=b.account_id
and a.customer_id=c.customer_id
and c.country='England'

RULE 4:
select 'Rule 4' as Rule_Num, c.customer_sk, c.customer_id, a.account_id, b.total_value, 'N/A' as TXN_TYPE_NAME
from tb_rf_account a, tb_pr_monthly_summary_aml b, tb_rf_customer c
where b.txn_type='Debits'
and b.month='Jan'
and a.account_id=b.account_id
and a.customer_id=c.customer_id
and c.country='England'
and c.customer_id in(
select customer_id
from tb_rf_customer_account
group by customer_id
having count(*)>1)

They are the 4 queries that will determine if the customer qualifies for exemption and then check against each rule.

I want the output to look as follows:

Customer ID Account ID Rule 1 Rule 2 Rule 3 Rule 4
1234 1234 5500 -
5678 5678 - x, y 30000 -
9876 9876 6500 x, z - 7891
9876 5432 7000 y - -

It would be nice if i can group by the customer ID

If i do a UNION on all the rules i get the follwing output:

Rule Num Customer ID Account ID Total Value TXN_TYPE_NAME
Rule 1 1234 1234 5500 -
Rule 1 9876 9876 6500 -
Rule 1 9876 5432 7000 -
Rule 2 5678 5678 - x, y
Rule 2 9876 9876 - x, z
Rule 2 9876 5432 - y
Rule 3 5678 5678 30000 -
Rule 4 9876 9876 7891 -

Ok i hope that defines the situation , now if someone can help me from this i would be very grateful.

Thanks in advance.

 

[rac2]

A general method for moving rows into columns is to write queries which yield the rows of interest for a particular column and a key value. These queries are used as if they were tables by giving them an alias or by saving them as VIEWs. The key value is used to join these queries to the basic table.

In this case it seems like the various queries you have posted can be joined to a customer table. This will generate a row for every customer. If you wish to have only the customers with problems in the result then you will need another query which has the list of such customers, a GROUP BY over the union query would do it.

Suppose you save the queries for the rules as BehavingBadlyRule1, BBR2, BBR3, and BBR4. Then the list of customers of interest is given by

SELECT CustomerID
FROM BBR1

UNION

SELECT CustomerID
FROM BBR2

UNION

SELECT CustomerID
FROM BBR3

UNION

SELECT CustomerID
FROM BBR4

The UNION eliminates duplicate rows, UNION ALL retains duplicates. At least that is true with MS SQL Server. DB2 might be different, in that case GROUP BY would do it.

Save this as a VIEW named BadBehavers and use it as the basic table in a query like this.

SELECT a.CustomerID, a.AccountID,
            b.total_value as "Rule 1",
            c.total_value as "Rule 2",
            d.txn_type_name as "Rule 3",
            e.total_value as "Rule 4"
FROM BadBehavers a
LEFT JOIN BBR1 b ON b.CustomerID = a.CustomerID
LEFT JOIN BBR2 c ON c.CustomerID = a.CustomerID
LEFT JOIN BBR3 d ON d.CustomerID = a.CustomerID
LEFT JOIN BBR4 e ON e.CustomerID = a.CustomerID

That ought to do it. If the basic entity is a customer account instead of a customer, simply add AccountID to BadBehavers and to the JOIN conditions.

Hope I understood the gist of your requirement properly. Let me know if I overlooked something, and whether this works.