Creating "phantom" rows from SELECT

Hey all,

In 9.2.0.5.0, I have a request to add 10s of thousands of rows of bogus
data to a table for a single report.  Instead of fudging that data, I
thought I'd create a view.  Here's a sample of the two tables involved:

CREATE TABLE lines
(
  salesorderno VARCHAR2(12) NULL,
  partno       VARCHAR2(15) NULL
)
/

INSERT INTO lines
        VALUES ('900001', '1234');
INSERT INTO lines
        VALUES ('900002', '5678');
INSERT INTO lines
        VALUES ('900003', '4321');
COMMIT;

CREATE TABLE details
(
  salesorderno VARCHAR2(12) NULL,
  seqno        VARCHAR2(4)  NULL,
  comments     VARCHAR2(80) NULL
)
/

INSERT INTO details
        VALUES ('900001', '0100', 'Mandatory');
INSERT INTO details
        VALUES ('900001', '1000', 'Shipped');
INSERT INTO details
        VALUES ('900003', '1000', 'Shipped');
INSERT INTO details
        VALUES ('900003', '2000', 'By JBlow');
COMMIT;


What they want is every SALESORDERNO, LINENO, RELEASENO combo from LINES
and all of the matching optional rows from DETAILS.  Additionally, if
there isn't a SEQNO of '0100' for that combo, make one up with a
COMMENTS value of 'NA'.

I can handle that with this ill-performing query:

SELECT *
FROM
(
SELECT sol.salesorderno, sol.partno,
        NVL(sod.seqno, '0100') "SEQNO",
        NVL2(sod.salesorderno, sod.comments, 'NA') "COMMENTS"
FROM lines sol, details sod
WHERE sol.salesorderno = sod.salesorderno(+)
UNION
SELECT sol.salesorderno, sol.partno,
        '0100', LTRIM(MAX(CASE WHEN seqno = '0100' THEN
                comments ELSE '             NA' END))
FROM lines sol, details sod
WHERE sol.salesorderno = sod.salesorderno
GROUP BY sol.salesorderno, sol.partno
)
ORDER BY 1,3;

This seems to work, but does anyone know how to do this without the
double FTS on DETAILS?

TIA!
Rich
--
http://www.freelists.org/webpage/oracle-l

Other related posts: