Ask for help from ORACLE board: matching normalized and denormalized tables

lenygold via DBMonster.com wrote:

[snip]

Is there is another solutions?
For example using Recursion.

Naturally... (not that I approve of such horrid structures ;-)

WITH
T1 (ID, ROOM, ITEMS) AS (
VALUES
(1, 'Bedroom', 'Phone, Lamp, Bed, TV'),
(2, 'Kitchen', 'Phone, Table'),
(3, 'LvRoom', 'Phone, Lamp, TV, Stereo')
),
T2 (ID, ITEM) AS (
VALUES
(1, 'Phone'),
(2, 'Table'),
(3, 'Lamp'),
(4, 'TV'),
(5, 'Stereo')
),
T3 (ITEMS, I, J, ITEM) AS (
SELECT
ITEMS,
1,
CASE
WHEN LOCATE(',', ITEMS) = 0 THEN LENGTH(ITEMS) + 1
ELSE LOCATE(',', ITEMS) + 1
END,
TRIM(CASE
WHEN LOCATE(',', ITEMS) = 0 THEN ITEMS
ELSE SUBSTR(ITEMS, 1, LOCATE(',', ITEMS) - 1)
END)
FROM T1

UNION ALL

SELECT
ITEMS,
I + 1,
CASE
WHEN LOCATE(',', ITEMS, J) = 0 THEN LENGTH(ITEMS) + 1
ELSE LOCATE(',', ITEMS, J) + 1
END,
TRIM(CASE
WHEN LOCATE(',', ITEMS, J) = 0 THEN SUBSTR(ITEMS, J)
ELSE SUBSTR(ITEMS, J, LOCATE(',', ITEMS, J) - J)
END)
FROM T3
WHERE I < 20
AND J <= LENGTH(ITEMS)
)
SELECT
T2.ID,
T2.ITEM,
COUNT(*) AS COUNT
FROM
T2 INNER JOIN T3 ON T2.ITEM = T3.ITEM
GROUP BY
T2.ID,
T2.ITEM;
Note that the I column in the recursive T3 CTE is only included to
prevent infinite recursion warnings. The J column is the "interesting"
one which calculates the start of the next substring.

The result of the above query is as follows:

ID ITEM COUNT
----------- ------ -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1

5 record(s) selected.
Just to prove the recursion is splitting out the ITEMS column
correctly, and to observe the J column incrementing, replace the main
query with "SELECT ITEMS, J, ITEM FROM T3", and the result becomes:

ITEMS J ITEM
----------------------- ----------- -----------------------
Phone, Lamp, Bed, TV 7 Phone
Phone, Table 7 Phone
Phone, Lamp, TV, Stereo 7 Phone
Phone, Lamp, Bed, TV 13 Lamp
Phone, Table 13 Table
Phone, Lamp, TV, Stereo 13 Lamp
Phone, Lamp, Bed, TV 18 Bed
Phone, Lamp, TV, Stereo 17 TV
Phone, Lamp, Bed, TV 21 TV
Phone, Lamp, TV, Stereo 24 Stereo

10 record(s) selected.
It's a bit clearer if "WHERE ID = 1" is added to the initial part of
the T3 query, in which case you get:

ITEMS J ITEM
----------------------- ----------- -----------------------
Phone, Lamp, Bed, TV 7 Phone
Phone, Lamp, Bed, TV 13 Lamp
Phone, Lamp, Bed, TV 18 Bed
Phone, Lamp, Bed, TV 21 TV

4 record(s) selected.
Cheers,

Dave.

Thank you Dave.
We have the best board in world
Cheers. Leny G.

Dave Hughes wrote:

>[snip]

>Is there is another solutions?
For example using Recursion.

Naturally... (not that I approve of such horrid structures ;-)

WITH
T1 (ID, ROOM, ITEMS) AS (
VALUES
(1, 'Bedroom', 'Phone, Lamp, Bed, TV'),
(2, 'Kitchen', 'Phone, Table'),
(3, 'LvRoom', 'Phone, Lamp, TV, Stereo')
),
T2 (ID, ITEM) AS (
VALUES
(1, 'Phone'),
(2, 'Table'),
(3, 'Lamp'),
(4, 'TV'),
(5, 'Stereo')
),
T3 (ITEMS, I, J, ITEM) AS (
SELECT
ITEMS,
1,
CASE
WHEN LOCATE(',', ITEMS) = 0 THEN LENGTH(ITEMS) + 1
ELSE LOCATE(',', ITEMS) + 1
END,
TRIM(CASE
WHEN LOCATE(',', ITEMS) = 0 THEN ITEMS
ELSE SUBSTR(ITEMS, 1, LOCATE(',', ITEMS) - 1)
END)
FROM T1

UNION ALL

SELECT
ITEMS,
I + 1,
CASE
WHEN LOCATE(',', ITEMS, J) = 0 THEN LENGTH(ITEMS) + 1
ELSE LOCATE(',', ITEMS, J) + 1
END,
TRIM(CASE
WHEN LOCATE(',', ITEMS, J) = 0 THEN SUBSTR(ITEMS, J)
ELSE SUBSTR(ITEMS, J, LOCATE(',', ITEMS, J) - J)
END)
FROM T3
WHERE I < 20
AND J <= LENGTH(ITEMS)
)
SELECT
T2.ID,
T2.ITEM,
COUNT(*) AS COUNT
FROM
T2 INNER JOIN T3 ON T2.ITEM = T3.ITEM
GROUP BY
T2.ID,
T2.ITEM;

Note that the I column in the recursive T3 CTE is only included to
prevent infinite recursion warnings. The J column is the "interesting"
one which calculates the start of the next substring.

The result of the above query is as follows:

ID ITEM COUNT
----------- ------ -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1

5 record(s) selected.

Just to prove the recursion is splitting out the ITEMS column
correctly, and to observe the J column incrementing, replace the main
query with "SELECT ITEMS, J, ITEM FROM T3", and the result becomes:

ITEMS J ITEM
----------------------- ----------- -----------------------
Phone, Lamp, Bed, TV 7 Phone
Phone, Table 7 Phone
Phone, Lamp, TV, Stereo 7 Phone
Phone, Lamp, Bed, TV 13 Lamp
Phone, Table 13 Table
Phone, Lamp, TV, Stereo 13 Lamp
Phone, Lamp, Bed, TV 18 Bed
Phone, Lamp, TV, Stereo 17 TV
Phone, Lamp, Bed, TV 21 TV
Phone, Lamp, TV, Stereo 24 Stereo

10 record(s) selected.

It's a bit clearer if "WHERE ID = 1" is added to the initial part of
the T3 query, in which case you get:

ITEMS J ITEM
----------------------- ----------- -----------------------
Phone, Lamp, Bed, TV 7 Phone
Phone, Lamp, Bed, TV 13 Lamp
Phone, Lamp, Bed, TV 18 Bed
Phone, Lamp, Bed, TV 21 TV

4 record(s) selected.

Cheers,

Dave.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

One question Dave.
What fumction TRIM is doing. I know LTRIM,RTRIM.
Thank's Leny G.

lenygold wrote:

>Thank you Dave.
We have the best board in world
Cheers. Leny G.

>>[snip]

>>Is there is another solutions?

[quoted text clipped - 108 lines]

>>
Dave.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

Another solution:
------------------------------ Commands Entered
------------------------------
WITH
T1 (Id,Room,RoomItems) AS
(VALUES(1, 'Bedroom', 'Phone, Lamp, Bed, TV'),
(2, 'Kitchen', 'Phone, Table'),
(3, 'LvRoom', 'Phone, Lamp, TV, Stereo')
)
,T2(Id, ItemName) AS
(VALUES(1, 'Phone'),
(2, 'Table'),
(3, 'Lamp'),
(4, 'TV'),
(5, 'Stereo')
)
SELECT t2.id
, ItemName
, COUNT(t1.id) AS count
FROM T2
LEFT JOIN
T1
ON LOCATE(ItemName, RoomItems) 0
GROUP BY
t2.id
, ItemName;
------------------------------------------------------------------------------

ID ITEMNAME COUNT
----------- -------- -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1

5 record(s) selected.

Another solution:
------------------------- Commands Entered -------------------------
WITH
T1 (Id,Room,RoomItems) AS
(VALUES(1, 'Bedroom', 'Phone, Lamp, Bed, TV'),
(2, 'Kitchen', 'Phone, Table'),
(3, 'LvRoom', 'Phone, Lamp, TV, Stereo')
)
,T2(Id, ItemName) AS
(VALUES(1, 'Phone'),
(2, 'Table'),
(3, 'Lamp'),
(4, 'TV'),
(5, 'Stereo'),
(6, 'Sofa')
)
SELECT
t2.id
, ItemName
, COUNT(t1.id) AS QtyUsed
FROM T2
LEFT JOIN
T1
ON LOCATE(ItemName, RoomItems) 0
GROUP BY
t2.id
, ItemName
;
--------------------------------------------------------------------

ID ITEMNAME QTYUSED
----------- -------- -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1
6 Sofa 0

6 record(s) selected.

lenygold via DBMonster.com wrote:

One question Dave.
What fumction TRIM is doing. I know LTRIM,RTRIM.

That's mostly me being lazy :) The query was based on one I wrote a
while back for dealing with comma-separated values in a loading script.
However, those values had no space after the comma, e.g. "A,B,C". Hence
when I did the same thing here I wound up with a leading space. Rather
than fix it "properly" I just threw in a TRIM call - TRIM(value) is
equivalent to LTRIM(RTRIM(value)) (although it's got arguments for
leading / trailing / both as well).

Cheers,

Dave.

Tonkuma wrote:

Another solution:
------------------------------ Commands Entered
------------------------------
WITH
T1 (Id,Room,RoomItems) AS
(VALUES(1, 'Bedroom', 'Phone, Lamp, Bed, TV'),
(2, 'Kitchen', 'Phone, Table'),
(3, 'LvRoom', 'Phone, Lamp, TV, Stereo')
)
,T2(Id, ItemName) AS
(VALUES(1, 'Phone'),
(2, 'Table'),
(3, 'Lamp'),
(4, 'TV'),
(5, 'Stereo')
)
SELECT t2.id
, ItemName
, COUNT(t1.id) AS count
FROM T2
LEFT JOIN
T1
ON LOCATE(ItemName, RoomItems) 0
GROUP BY
t2.id
, ItemName;
----------------------------------------------------------------------
--------

ID ITEMNAME COUNT
----------- -------- -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1

5 record(s) selected.

That's a cunning solution! Although I would caution that it shouldn't
be used in practice without a certain amount of care - e.g. if one item
is a prefix of another item, interesting things will happen:

WITH
T1 (Id,Room,RoomItems) AS
(VALUES(1, 'Bedroom', 'Phone, Lamp, Sofabed, TV'),
(2, 'Kitchen', 'Phone, Table, Sofa'),
(3, 'LvRoom', 'Phone, Lamp, TV, Stereo')
)
,T2(Id, ItemName) AS
(VALUES(1, 'Phone'),
(2, 'Table'),
(3, 'Lamp'),
(4, 'TV'),
(5, 'Stereo'),
(6, 'Sofa'),
(7, 'Sofabed')
)
SELECT t2.id
, ItemName
, COUNT(t1.id) AS count
FROM T2
LEFT JOIN
T1
ON LOCATE(ItemName, RoomItems) 0
GROUP BY
t2.id
, ItemName;

ID ITEMNAME COUNT
----------- -------- -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1
6 Sofa 2
7 Sofabed 1

7 record(s) selected.
Cheers,

Dave.

Leny,

although a solution is posted, I would strongly recommend to re-
consider the table design here as you already violate first normal
form and this will draw up further problems, even when you have solved
this one. For example as you can see in your example, you have "Bed"
in "ItemsByRooms" but no item for it in the items table. This would
not have happened if you had the proper tables and foreign keys in
place.

I would expect the following tables:

CREATE TABLE Rooms
(RoomID INTEGER NOT NULL PRIMARY KEY,
RoomName VARCHAR(30) NOT NULL);

-- This table takes your phone, table, lamp etc each one in a separate
row.
CREATE TABLE Furnishings
(FurnishingID INTEGER NOT NULL PRIMARY KEY,
FurnishingName VARCHAR(30) NOT NULL);

-- This table links rooms to furnishings
CREATE TABLE FurnishedRooms
(RoomID INTEGER NOT NULL
REFERENCES Rooms(RoomID)
ON UPDATE CASCADE,
FurnishingID INTEGER NOT NULL
REFERENCES Furnishings(FurnishingID)
ON UPDATE CASCADE,
PRIMARY KEY (RoomID, FurnishingID));

Then it is easy to get a COUNT on the FurnishingID:

SELECT FR1.FurnishingID,
F1.FurnishingName,
COUNT(FR1.FurnishingID) AS FurnishingTally
FROM Furnishings AS F1
INNER JOIN FurnishedRooms AS FR1
ON F1.FurnishingID = FR1.FurnishingID
GROUP BY FR1.FurnishingID, F1.FurnishingName

Brgds

Philipp Post

On Jun 10, 6:00*pm, "Dave Hughes" <d...@waveform.plus.comwrote:

>
That's a cunning solution! Although I would caution that it shouldn't
be used in practice without a certain amount of care - e.g. if one item
is a prefix of another item, interesting things will happen:

It's easy to avoid the issue by concatenating both argument of LOCATE
function with comma(',').

WITH
T1 (Id,Room,RoomItems) AS
(VALUES(1, 'Bedroom', 'Phone, Lamp, Sofabed, TV'),
(2, 'Kitchen', 'Phone, Table, Sofa'),
(3, 'LvRoom', 'Phone, Lamp, TV, Stereo')
)
,T2(Id, ItemName) AS
(VALUES(1, 'Phone'),
(2, 'Table'),
(3, 'Lamp'),
(4, 'TV'),
(5, 'Stereo'),
(6, 'Sofa'),
(7, 'Sofabed')
)
SELECT t2.id
, ItemName
, COUNT(t1.id) AS count
FROM T2
LEFT JOIN
T1
ON LOCATE(ItemName||',', RoomItems||',') 0
GROUP BY
t2.id
, ItemName;
--------------------------------------------------------------------

ID ITEMNAME COUNT
----------- -------- -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1
6 Sofa 1
7 Sofabed 1

7 record(s) selected.

I used COUNT(t1.id) instead of COUNT(*) and LEFT JOIN instead of INNER
JOIN.
Because I considered the possibility that some item of T2 would not be
in none of RoomItems of T1.

Tonkuma wrote:

On Jun 10, 6:00*pm, "Dave Hughes" <d...@waveform.plus.comwrote:

That's a cunning solution! Although I would caution that it
shouldn't be used in practice without a certain amount of care -
e.g. if one item is a prefix of another item, interesting things
will happen:

It's easy to avoid the issue by concatenating both argument of LOCATE
function with comma(',').

Nice fix! Barring delimiters appearing within values (which would break
the recursive solution anyway), I can't think of a way to break it.

In fact, I suspect in the case of a purely delimited multi-valued
string (again, assuming the delimiter cannot appear within individual
items) there's never a requirement for recursion (i.e. this use of
LOCATE ought to solve all possible situations).

Recursion's better suited to dealing with a multi-valued string where
each item is prefixed with its length, as in the message column for
exception tables. The InfoCenter's got a nice example of such usage:

http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.sql.ref.doc/doc/r0001111.html

Or alternatively, when dealing with a more complex structure like
"real" CSV in which quoted values could contain the delimiter (a UDF
for locating the next "real" delimiter could be used with recursion in
such a case to split the string).

WITH
T1 (Id,Room,RoomItems) AS
(VALUES(1, 'Bedroom', 'Phone, Lamp, Sofabed, TV'),
(2, 'Kitchen', 'Phone, Table, Sofa'),
(3, 'LvRoom', 'Phone, Lamp, TV, Stereo')
)
,T2(Id, ItemName) AS
(VALUES(1, 'Phone'),
(2, 'Table'),
(3, 'Lamp'),
(4, 'TV'),
(5, 'Stereo'),
(6, 'Sofa'),
(7, 'Sofabed')
)
SELECT t2.id
, ItemName
, COUNT(t1.id) AS count
FROM T2
LEFT JOIN
T1
ON LOCATE(ItemName||',', RoomItems||',') 0
GROUP BY
t2.id
, ItemName;
--------------------------------------------------------------------

ID ITEMNAME COUNT
----------- -------- -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1
6 Sofa 1
7 Sofabed 1

7 record(s) selected.

Cheers,

Dave.

I'm sorry that I removed my post before I saw your post.
Here's an updated example(with modified and additional data) which I
added REPLACE functions to remove blanks in ItemName and RoomItems.
WITH
T1 (Id,Room,RoomItems) AS
(VALUES(1, 'Bedroom', 'Phone, Lamp , Sofa bed , TV'),
(2, 'Kitchen', 'Phone , Table , Sofa '),
(3, 'LvRoom', 'Phone,Lamp,TV,Stereo,Sofabed')
)
,T2(Id, ItemName) AS
(VALUES(1, 'Phone'),
(2, 'Table'),
(3, 'Lamp'),
(4, 'TV'),
(5, 'Stereo'),
(6, 'Sofa'),
(7, 'Sofa bed'),
(8, 'PC')
)
SELECT t2.id
, ItemName
, COUNT(t1.id) AS count
FROM T2
LEFT JOIN
T1
ON LOCATE( REPLACE( ItemName, ' ', '') || ','
, REPLACE(RoomItems, ' ', '') || ','
) 0
GROUP BY
t2.id
, ItemName
;
--------------------------------------------------------------------

ID ITEMNAME COUNT
----------- -------- -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1
6 Sofa 1
7 Sofa bed 2
8 PC 0

8 record(s) selected.

Dave Hughes wrote:

In fact, I suspect in the case of a purely delimited multi-valued
string (again, assuming the delimiter cannot appear within individual
items) there's never a requirement for recursion (i.e. this use of
LOCATE ought to solve all possible situations).

I should amend the above to say "in the case of a purely delimited
multi-valued string where all possible items are known in advance". The
recursive solution would be useful in the case where the items are not
known.
Cheers,

Dave.