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
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;