Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
|
Working with string
concatenation |
A few months back, in
the OTN forum there was an interesting challenge:
http://forums.oracle.com/forums/thread.jspa?threadID=1047415&tstart=0
I've finally gotten around to addressing it after removing the
original post's reserved words.
The blue highlights, below, are to draw your eyes to the non-trivial
parts of the solutions. |
CREATE TABLE t1 (
code VARCHAR2(6),
code_type VARCHAR2(3),
AMOUNT NUMBER);
INSERT INTO t1 VALUES ('1001','10',1000);
INSERT INTO t1 VALUES ('1001','20',2000);
INSERT INTO t1 VALUES ('1001','30',3000);
INSERT INTO t1 VALUES ('1002','10',5000);
INSERT INTO t1 VALUES ('1002','20',3000);
INSERT INTO t1 VALUES ('1003','30',2500);
CREATE TABLE t2 (
code_type VARCHAR2(3),
code_name VARCHAR2(20));
INSERT INTO T2 VALUES ('10','type10');
INSERT INTO T2 VALUES ('20','type20');
INSERT INTO T2 VALUES ('30','type30');
INSERT INTO T2 VALUES ('40','type40');
SELECT * FROM t1;
SELECT * FROM t2; |
| BluShadow's solution |
col equation format a50
col total format a20
SELECT code, equation, dbms_xmlgen.getxmltype('SELECT '|| math || '
FROM dual').EXTRACT
('ROWSET/ROW/*/text()') AS total
FROM (
SELECT code,
LTRIM(SYS_CONNECT_BY_PATH(code_type || ' ' || amount, '+'), '+') AS
EQUATION,
LTRIM(SYS_CONNECT_BY_PATH(amount, '+'), '+') AS MATH
FROM (
SELECT t1.code, t2.code_type, t1.amount, ROW_NUMBER()
OVER (PARTITION BY t1.code ORDER
BY t2.code_type) AS rn
FROM t1 JOIN t2 ON (t2.code_type = t1.code_type))
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY code =
PRIOR code
AND rn = PRIOR rn + 1
START WITH rn = 1)
ORDER BY 1;
EXPLAIN PLAN
-------------------------------------------------------------------------------
| Id | Operation
| Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| 6 | 24054 | 21 (15)|
| 1 | SORT ORDER BY
| 6 | 24054 | 21 (15)|
| 2 | VIEW
| 6 | 24054 | 21 (15)|
|* 3 | FILTER
| |
|
|
|* 4 | CONNECT BY NO FILTERING WITH
START-WITH| |
|
|
| 5 | VIEW
| 6 | 204 | 20
(10)|
| 6 | WINDOW SORT
| 6 | 144 | 20
(10)|
|* 7 | HASH JOIN
| 6 | 144 | 19
(6)|
| 8 | TABLE
ACCESS FULL T2
| 4 | 12 |
9 (0)|
| 9 | TABLE
ACCESS FULL T1
| 6 | 126 |
9 (0)|
------------------------------------------------------------------------------- |
|
Michaels2's solution |
col total
format a50
SELECT code,
extractvalue(xmltype(REPLACE(xmlagg(xmlelement(e,
code_name || ' ' || amount)
ORDER BY code_name), '</E><E>', ' + ')),
'E') || ' = ' || SUM(amount) AS TOTAL
FROM t1, t2
WHERE t1.code_type = t2.code_type
GROUP BY code;
EXPLAIN PLAN
------------------------------------------------------------
| Id | Operation
| Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------
| 0 | SELECT STATEMENT
| 6 | 216 | 20
(10)|
| 1 | SORT GROUP BY
| 6 | 216 | 20
(10)|
|* 2 | HASH JOIN
| 6 | 216 | 19
(6)|
| 3 | TABLE ACCESS FULL T2 |
4 | 60 | 9
(0)|
| 4 | TABLE ACCESS FULL T1 |
6 | 126 | 9 (0)|
---------------------------------------------------------------------------- |
|
Morgan's solution with WM_CONCAT solution |
SELECT t1.code, t2.code_name, t1.amount
FROM t1, t2
WHERE t1.code_type = t2.code_type;
SELECT code, TRANSLATE(WM_CONCAT(amount),',','+')
AS TOTAL, SUM(amount) SUM
FROM (
SELECT t1.code, t2.code_name, t1.amount
FROM t1, t2
WHERE t1.code_type = t2.code_type)
GROUP BY code;
EXPLAIN PLAN
------------------------------------------------------------
| Id | Operation
| Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------
| 0 | SELECT STATEMENT
| 6 | 144 | 20
(10)|
| 1 | SORT GROUP BY
| 6 | 144 | 20
(10)|
|* 2 | HASH JOIN
| 6 | 144 | 19
(6)|
| 3 | TABLE ACCESS FULL T2 |
4 | 12 | 9
(0)|
| 4 | TABLE ACCESS FULL T1 |
6 | 126 | 9 (0)|
------------------------------------------------------------ |
|
LIST_AGG solution. |
SELECT code, LISTAGG(amount, '
+ ')
WITHIN GROUP (ORDER BY code_name) AS
TOTAL, SUM(amount) SUM
FROM (
SELECT t1.code, t2.code_name, t1.amount
FROM t1, t2
WHERE t1.code_type = t2.code_type)
GROUP BY code;
EXPLAIN PLAN
------------------------------------------------------------
| Id | Operation
| Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------
| 0 | SELECT STATEMENT
| 6 | 216 | 20
(10)|
| 1 | SORT GROUP BY
| 6 | 216 | 20
(10)|
|* 2 | HASH JOIN
| 6 | 216 | 19
(6)|
| 3 | TABLE ACCESS FULL T2 |
4 | 60 | 9
(0)|
| 4 | TABLE ACCESS FULL T1 |
6 | 126 | 9 (0)|
------------------------------------------------------------ |
I will let my readers look at the code,
and the resulting explain plans, and decide for themselves what they
might do in a similar situation.
What I do find fascinating is that, at least to the Oracle CBO
optimizer, the final three look close enough to identical to reduce
down to the same plan. |
|
