Creating "phantom" rows from SELECT
- From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
- To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 9 Mar 2006 12:27:27 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
