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