473,883 Members | 1,809 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

I found this problem on ORACLE board.
2 input TABLES:

Items
Id ItemName
1 Phone
2 Table
3 Lamp
4 TV
5 Stereo

ItemsByRooms
Id Room RoomItems
1 Bedroom Phone, Lamp, Bed, TV
2 Kitchen Phone, Table
3 LvRoom Phone, Lamp, TV, Stereo

Expected result:
Results would be:

Id ItemName QtyUsed
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1

This Problem wasn't resolved by ORACLE board members.

Here is my solution:
WITH T1 (Id,Room,RoomIt ems) 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 CHAR(T2.ID)|| ' '||ITEMNAME "ID ITEM_NAME" ,count(*) AS
QTY_USED
FROM T1,T2
WHERE (LENGTH(STRIP(R oomItems)) - LENGTH(REPLACE( STRIP(RoomItems ),
ITEMNAME,''))) 0
GROUP BY CHAR(T2.ID)|| ' '||ITEMNAME;

ID ITEM_NAME QTY_USED
------------------- -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1

5 record(s) selected.

Is there is another solutions?
For example using Recursion.
Thank's in advance Leny G.

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

Jun 27 '08 #1
13 1604
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 "interestin g"
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.
Jun 27 '08 #2
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 "interestin g"
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

Jun 27 '08 #3
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

Jun 27 '08 #4
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(val ue)) (although it's got arguments for
leading / trailing / both as well).

Cheers,

Dave.
Jun 27 '08 #5
Tonkuma wrote:
Another solution:
------------------------------ Commands Entered
------------------------------
WITH
T1 (Id,Room,RoomIt ems) 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,RoomIt ems) 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.
Jun 27 '08 #6
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 "ItemsByRoo ms" 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(Fur nishingID)
ON UPDATE CASCADE,
PRIMARY KEY (RoomID, FurnishingID));

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

SELECT FR1.FurnishingI D,
F1.FurnishingNa me,
COUNT(FR1.Furni shingID) AS FurnishingTally
FROM Furnishings AS F1
INNER JOIN FurnishedRooms AS FR1
ON F1.FurnishingID = FR1.FurnishingI D
GROUP BY FR1.FurnishingI D, F1.FurnishingNa me

Brgds

Philipp Post
Jun 27 '08 #7
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,RoomIt ems) 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.
Jun 27 '08 #8
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.
Jun 27 '08 #9
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,RoomIt ems) 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.
Jun 27 '08 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1752
by: Muzamil | last post by:
hello I've a denormalized table PRODUCTS with following fields: ProductNo , OrderNo , SerialNo , OrderDate , PromiseDate ,
5
1531
by: MJunium | last post by:
I hope someone can help me with this one. For performance reasons, I have a denormalized database. There are two tables in the database we can call them table a and table b. Both of theses tables contain columns that are text with comma seperated values. example: TABLE A ---------- id - integer - primary key
3
1324
by: Thelma Lubkin | last post by:
I've inherited a SQL statement that looks approximately like this: SELECT Sum(tblA.A9A) As , Sum(tblA.A9A)/Sum(tblA.I9N) As , Sum(tblA.A0A) As , Sum(tblA.A0A)/Sum(tblA.I9N) As FROM INNER JOIN tblA ON .STFID = tblA.STFID;
2
2772
by: Janelle.Dunlap | last post by:
I have a table in my database that is linked to an excel spreadsheet. I need to be able to manipulate the data in this linked table so that I can create smaller normalized tables that work with what I am trying to do (all data used in my db will come from this one spreadsheet). I am currently trying to utilize the make-table queries to create the tables that I need from this data, however I am unsure of how to add foreign keys to a table...
3
6956
by: fkulaga | last post by:
Hi all, I have a problem with the issue in the subject, i have all data in one big excel file, in a denormalized form, and on the other side, i have mysql database with many tables, which is already in production, and it is of course in the 3rd NF. How do i go about importing that excel file , is there any good tool i could use to handle prim/foreign key issues for me? Any advice appreciated!
1
2959
by: phlype.johnson | last post by:
Suppose we have to design a database for a recruitment agency. There will be a table "candidates" with fields "candidateid","last name","first name" ; the languages mastered by a candidate as well as the skills are separated into different tables. Eg. The skills are stored in the table "skills" with fields "skillid", "skillname","candidateid" where skillid is the primary autoincrement key. The language table is defined in a similar way. A...
1
1254
by: Neekos | last post by:
Hey guys, So after some of you (FishVal, Nico, NeoPa) pointed out previously that my tables are not normalized, i've decided to go back to the drawing board, but im not really sure where to start. This table is pretty big right now, and im sure it can be broken up into different tables - im just not sure how. So, i'm hoping you guys can help me out. Right now, i have one main table for a passenger manifest. In this table, i have the...
3
4271
by: vikas000000a | last post by:
Hi All, I am new to this forum as far as posting a question is concerned, although I have quite frequently visiting this site as a guest. My quertion relates to preforming fast searched in a very large oracle database. This is my company database. I am having two very huge tables containing crores of records. I have to match each record from one table (I'll call it base table from here onwards) to another table which may contain one or more...
5
4968
by: jrod11 | last post by:
hi, I found a jquery html table sorting code i have implemented. I am trying to figure out how to edit how many colums there are, but every time i remove code that I think controls how many colums there are, it crashes. There are currently 6 columns, and I only want 4. How do I remove the last two (discount and date)? Here is a link: http://www.jaredmoore.com/tablesorter/docs/salestable.html Here is some jquery js that I think...
0
9792
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10847
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10416
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7971
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7129
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5797
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4612
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4220
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3233
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.