StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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:</p> <pre><code>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; </code></pre> <p>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). </p> <p>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.</p> <p>UPDATE </p> <p>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.</p> <p>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.</p> <p>So we could remove the cross join and do this instead:</p> <pre><code>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; </code></pre> <p>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.</p> <p>Here's the SQL Fiddle:</p> <p><a href="http://sqlfiddle.com/#!4/2f16e/13" rel="nofollow">http://sqlfiddle.com/#!4/2f16e/13</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload