Databases
StackOverflow2013
Postsdbo
POadding "empty" rows to the query result
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POadding "empty" rows to the query result
    primarykey
    data
    text
    <p>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:</p> <p>lets say we have a table BROKERS with company names, their transaction types and cumulative amounts for a current month and all months ever.</p> <pre><code>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 </code></pre> <p>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:</p> <pre><code>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 </code></pre> <p>so the results shown above is just a simple join of those two tables:</p> <pre><code>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; </code></pre> <p>everything is pretty simple, but here is where the problem starts : the report i am working on should look like this:</p> <pre><code>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 </code></pre> <p>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): </p> <pre><code>SELECT DECODE (TRAN_TYPE_CD_EXPL.TRAN_TYPE_CD_EXPLD, 'CURRENCY_SELL', 'FOREX', 'CURRENCY_BUY', 'FOREX', 'STOCKS_SELL', 'STOCKMARKET', 'STOCKS_BUY', 'STOCKMARKET') AS MARKET, </code></pre> <p>or based on TRAN_TYPE_CD value:</p> <pre><code>SELECT DECODE (BROKERS.TRAN_TYPE_CD, 9, 'FOREX', 10, 'FOREX', 1, 'STOCKMARKET', 2, 'STOCKMARKET') AS MARKET, </code></pre> <p>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 </p> <pre><code>Broker1 FOREX CURRENCY_BUY $0.00 $5500000.00 </code></pre> <p>and</p> <pre><code>Broker1 STOCKMARKET STOCKS_BUY $0.00 $9500000.00 </code></pre> <p>The thing is that it will be executed thru plsql batch on unix, so it must be a single query.</p> <p>Any ideas and/or suggestions are very appreciated!</p> <p>Thanks</p> <p>P.S. </p> <p>Its oracle 11gr2, with read-only role.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1324100
    1. USuser1324100
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POadding "empty" rows to the query result
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POadding "empty" rows to the query result
      1. USJon Heller
      1. VTFavorite
    3. VO
      singulars
      1. POadding "empty" rows to the query result
      1. This table or related slice is empty.
      1. VTUpMod
    1. COwhy it cuts off very first line saying "Hello guys"???
      singulars
      1. POadding "empty" rows to the query result
      1. USuser1324100
    2. COStackoverflow automatically filters out those kinds of greetings. They're unnecessary and detract from the problem at hand. Just dive right into the meat of your question when you're here.
      singulars
      1. POadding "empty" rows to the query result
      1. USJoe Stefanelli
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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