adding "empty" rows to the query result

Question

i am really stuck with this task - i need to populate a report with rows with "0" values based on a particular criteria, here is the example:

lets say we have a table BROKERS with company names, their transaction types and cumulative amounts for a current month and all months ever.

COMPANY       TRAN_TYPE          CURR_MNTH         ALL_MNTH  
Broker1       CURRENCY_SELL      $1000.00          $1500000.00 
Broker1       GOLD_SELL          $50000.00         $2500000.00 
Broker1       GOLD_BUY           $80000.00         $8500000.00 
Broker1       STOCKS_SELL        $35000.00         $3500000.00 

table BROKERS does not have a field TRAN_TYPE, but has a field TRAN_TYPE_CD which reffers to another table called TRAN_TYPE_CD_EXPL, where all the codes are explained:

TRAN_TYPE_CD  TRAN_TYPE_CD_EXPLD
1             STOCKS_SELL
2             STOCKS_BUY
3             GOLD_SELL
4             GOLD_BUY
5             SILVER_SELL
6             SILVER_BUY
7             COPPER_SELL
8             COPPER_BUY
9             CURRENCY_SELL
10            CURRENCY_BUY

so the results shown above is just a simple join of those two tables:

select  b.COMPANY, tt.TRAN_TYPE, b.CURR_MONTH, b.ALL_MNTH
from    BROKERS b, TRAN_TYPE_CD_EXPL tt
where   b.TRAN_TYPE_CD = tt.TRAN_TYPE_CD;

everything is pretty simple, but here is where the problem starts : the report i am working on should look like this:

COMPANY       MARKET      TRAN_TYPE          CURR_MNTH         ALL_MNTH  
Broker1       FOREX       CURRENCY_SELL      $1000.00          $1500000.00 
Broker1       FOREX       CURRENCY_BUY       $0.00             $5500000.00 
Broker1       CONTRACTS   GOLD_SELL          $50000.00         $2500000.00 
Broker1       CONTRACTS   GOLD_BUY           $80000.00         $8500000.00 
Broker1       STOCKMARKET STOCKS_SELL        $35000.00         $3500000.00 
Broker1       STOCKMARKET STOCKS_BUY         $0.00             $9500000.00 

so let me explain : firstable, report should contains a column MARKET, which should be populated based on the values in the column TRAN_TYPE, but the thing is there is no table MARKET in database, so each time when you need to populate it in a report or somewhere else, you need to use decode like this (assumed everybody knows where all kind of tran_types belong to):

SELECT DECODE (TRAN_TYPE_CD_EXPL.TRAN_TYPE_CD_EXPLD,
             'CURRENCY_SELL', 'FOREX',
             'CURRENCY_BUY', 'FOREX',
             'STOCKS_SELL', 'STOCKMARKET',
             'STOCKS_BUY', 'STOCKMARKET') AS MARKET,

or based on TRAN_TYPE_CD value:

SELECT DECODE (BROKERS.TRAN_TYPE_CD,
             9, 'FOREX',
             10, 'FOREX',
             1, 'STOCKMARKET',
             2, 'STOCKMARKET') AS MARKET,

2) problem number 2 is more complicated : the report logic says - is a company has at least one transaction in a particular group (lets say CURRENCY_SELL for market type 'FOREX'), the report should be populated with other tran_types from the market type group with $0 even thou this company did not have any of those transactions during current month. so in this case it should be populated with the rows

Broker1       FOREX       CURRENCY_BUY       $0.00             $5500000.00

and

Broker1       STOCKMARKET STOCKS_BUY         $0.00             $9500000.00

The thing is that it will be executed thru plsql batch on unix, so it must be a single query.

Any ideas and/or suggestions are very appreciated!

Thanks

P.S.

Its oracle 11gr2, with read-only role.

Answer 1

First of all, is there a reason you can't create a MARKET table? That will obviously be the most straight-forward solution. However, if you don't have DB access and have to hack it into your query, you could do something like this:

WITH m AS (
    select 'CURRENCY_SELL' as tran_type, 'FOREX' as market_name from dual
    UNION ALL
    select 'CURRENCY_BUY' as tran_type, 'FOREX' as market_name from dual
    UNION ALL
    select 'STOCKS_SELL' as tran_type, 'STOCKMARKET' as market_name from dual
    UNION ALL
    select 'STOCKS_BUY' as tran_type, 'STOCKMARKET' as market_name from dual
)
select
    b.company, m.tran_type, m.market_name, 
    nvl(v.curr_month, 0) as curr_month,
    nvl(v.all_mnth, 0) as all_mnth
from
    m
cross join
    (select distinct b.company from brokers) b
left join (
    select b.COMPANY, tt.TRAN_TYPE, b.CURR_MONTH, b.ALL_MNTH
    from    BROKERS b, TRAN_TYPE_CD_EXPL tt
    where   b.TRAN_TYPE_CD = tt.TRAN_TYPE_CD
) v
on v.company = b.company and v.tran_type = m.tran_type;

First, I've done a cross join of companies with the inline Market view - You'll get a combination of all those records for each company, even if they do not exist together in the Brokers table. I've performed a left outer join to get your numbers, so it plugs in 0 if a NULL value comes back (no corresponding record).

This may not be exactly right (I don't know how you partition your months, for example), but should point you in the correct direction.

UPDATE

OK, so if I understand correctly, you want to limit the "empty" rows to transaction types where the broker has had at least one transaction within the same market.

I'm going to assume that the "BROKERS" is a view of the current month - so if there's a record in the table, it's happened this month.

So we could remove the cross join and do this instead:

WITH m AS (
    select 9 as tran_type_cd, 'FOREX' as market_name from dual
    UNION ALL
    select 10, 'FOREX' as market_name from dual
    UNION ALL
    select 1, 'STOCKMARKET' from dual
    UNION ALL
    select 2, 'STOCKMARKET' from dual
    UNION ALL 
    SELECT 3, 'CONTRACTS' from dual
    UNION ALL 
    SELECT 4, 'CONTRACTS' from dual
)
select
    bm.company, tt.tran_type, m.market_name, 
    nvl(v.curr_month, 0) as curr_month,
    nvl(v.all_mnth, 0) as all_mnth
from
    (select distinct b.company, m.market_name
     from brokers b
     join m on m.tran_type_cd = b.tran_type_cd) bm
join
    m on m.market_name = bm.market_name
join
    TRAN_TYPE_CD_EXPL tt on tt.tran_type_cd = m.tran_type_cd
left join (
    select b.COMPANY, b.tran_type_cd, b.CURR_MONTH, b.ALL_MNTH
    from    BROKERS b
) v
on v.company = bm.company and v.tran_type_cd = m.tran_type_cd;

The view "bm" (great acronym choice, I know!) should give you a distinct list of markets tied to the company for the month. I then have joined back to the market view to get all transaction types associated with that market. As before, I've used a left join to fill in the empty rows with 0, if there's not a matching record in the Brokers table.

Here's the SQL Fiddle:

http://sqlfiddle.com/#!4/2f16e/13