Creating "phantom" rows from SELECT

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Thu, 9 Mar 2006 12:27:27 -0600
Message-ID: <FB5D3CCFCECC2948B5DCF4CABDBE6697545F02@QTEX1.qg.com>

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

Received on Thu Mar 09 2006 - 12:27:27 CST