How to add fictional rows to an SQL results in Oracle without à grouping

here is my issue, I’m working on an existing big report and they want me to add fictional rows with specifics values each time row in database meet a condition (let’s say status = Canceld) I simplified the query (1000 lines of SQL Code) to this :

if I have two table A and B :

Table A
Name            Status              Estimated       Real  
COMPANY A       Completed           $50.00          $50.00   
COMPANY B       Canceled            $0.00           $0.00  
COMPANY C       Not Approved        $100.00     $0.00  
COMPANY D       Withdrawn           $20.00          $10.00   
COMPANY E       Not Approved        $0.00           $0.00  
COMPANY F       Canceled            $1,000.00       $1,000.00
---------------------------------------------------------------

Table B
Name            Status              Estimated       Real  
COMPANY G       In Progress         $50.00          $20.00   
COMPANY H       Not Started         $20.00          $0.00  
COMPANY H       Passed              $100.00     $100.00  
COMPANY I       Approved            $20.00          $00.00   
COMPANY J       Canceled            $14.00          $6.00  
COMPANY A       Scheduled           $2,000.00       $2,000.00

and the query is like that :

SELECT * from (
SELECT NAME,STATUS,ESTIMATED,REAL from A
UNION ALL
SELECT NAME,STATUS,ESTIMATED,REAL from B
) order by name

and the results are like that :

Name            Status              Estimated       Real  
COMPANY A       Completed           $50.00          $50.00   
COMPANY A       Scheduled           $2,000.00       $2,000.00
COMPANY B       Canceled            $0.00           $0.00  
COMPANY C       Not Approved        $100.00     $0.00  
COMPANY D       Withdrawn           $20.00          $10.00   
COMPANY E       Not Approved        $0.00           $0.00  
COMPANY F       Canceled            $1,000.00       $1,000.00
COMPANY G       In Progress         $50.00          $20.00   
COMPANY H       Not Started         $20.00          $0.00  
COMPANY H       Passed              $100.00     $100.00  
COMPANY I       Approved            $20.00          $00.00   
COMPANY J       Canceled            $14.00          $6.00  

now what I need to do is insert fictional rows whenever the Status is Canceled like that : for each row where Status = Canceled add a row with same Name and Estimated column, Status Scheduled and Real = 0. the result should look like : (I added ** before the fictional rows)

Name            Status              Estimated       Real  
COMPANY A       Completed           $50.00          $50.00   
COMPANY A       Scheduled           $2,000.00       $2,000.00
COMPANY B       Canceled            $0.00           $0.00  
**COMPANY B     Scheduled           $0.00           $0.00**  
COMPANY C       Not Approved        $100.00     $0.00  
COMPANY D       Withdrawn           $20.00          $10.00   
COMPANY E       Not Approved        $0.00           $0.00  
COMPANY F       Canceled            $1,000.00       $1,000.00
**COMPANY F     Scheduled           $1,000.00       $0.00**
COMPANY G       In Progress         $50.00          $20.00   
COMPANY H       Not Started         $20.00          $0.00  
COMPANY H       Passed              $100.00     $100.00  
COMPANY I       Approved            $20.00          $00.00   
COMPANY J       Canceled            $14.00          $6.00  
**COMPANY J     Scheduled           $14.00          $0.00**

I tried a join or UNION with dual but I don’t figure out what’s missing. thank you a lot

Answer

You can give this a try, note that C is an inline view which is used to select the fictional row and does a union with your existing resultset.

SELECT * 
FROM   (SELECT C.name, 
               'Scheduled' AS status, 
               0           AS estimated, 
               0           AS real 
        FROM   (SELECT name, 
                       status 
                FROM   a 
                UNION ALL 
                SELECT name, 
                       status 
                FROM   b) C 
        WHERE  C.status = 'Canceled') 
UNION ALL 
(SELECT name, 
        status, 
        estimated, 
        real 
 FROM   a 
 UNION ALL 
 SELECT name, 
        status, 
        estimated, 
        real 
 FROM   b) 
ORDER  BY name, 
          estimated DESC;