Home » SQL & PL/SQL » SQL & PL/SQL » missing data (Oracle 9i)
| missing data [message #443636] |
Tue, 16 February 2010 11:29  |
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi Experts,
I have a table with the following data.
item_code item_name
101 Pen
101 Pencil
101 Scale
102 Pen
102 Scale
103 Pen
103 Pencil
103 Scale
103 Marker
My aim is to display the missing itemnames for each itemcode.
For each ITEM_CODE there should be four items i.e Pen,Pencil,Scale and Marker
If any itemcode missed any of the itemnames those records should display in the output.
So the output should be like this
101 Marker Because the item Marker is missed.
102 Pencil Because the items Pencil and Marker are missed.
102 Marker Because the items Pencil and Marker are missed.
For itemcode 103 no records should be displayed because it having all four itemnames.
Please help me on this scenario.
Thanks in advance.
Ramesh
|
|
|
|
|
|
|
|
|
|
|
|
| Re: missing data [message #443693 is a reply to message #443638] |
Wed, 17 February 2010 00:42  |
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi Experts,
Whatever the reply you have given that is working fine thanks for user immediate response.
I am very sorry to modify the requirement
I have a table with the following data.
item_code Visit item_name
101 V1 Pen
101 V1 Pencil
101 V1 Scale
101 V2 Pencil
102 V4 Pen
102 V4 Scale
102 V5 Pen
102 V5 Pencil
103 V7 Pen
103 V7 Pencil
103 V7 Scale
103 V8 Pen
103 V8 Pencil
103 V8 Scale
My aim is to display the missing item_names for each item_code and each Visit
For V1 and V2 There should be the item_names
Pen,Pencil,Scale,Eraser and Marker
But in the table V1 having Pen,Pencil and Scale it's missing Eraser and Scale.
V2 is having only Pencil it's missing Pen,Scale and Eraser
So that For item_code 101 and Visits V1 and V2 the output should be like this.
101 V1 Eraser
101 V1 Scale
101 V2 Pen
101 V2 Scale
101 V2 Eraser
For V4 and V5 There should be the item_names
Pen,Pencil,Scale and Sharpener
But in the table V4 having Pen,Scale it's missing Pencil and Sharpener
V5 is having Pen and Pencil it's missing Scale and Sharpener
So that For item_code 102 and Visits V4 and V5 the output should be like this.
102 V4 Pencil
102 V4 Sharpener
102 V5 Scale
102 V5 Sharpener
For V7 and V8 There should be the item_names
Pen,Pencil and Scale
In the table V7 having Pen,Pencil and Scale no missing items
For V8 is having Pen,Pencil and Scale no missing items.
So the last output should be like this
ITEM_COED VISIT ITEM_NAME
101 V1 Eraser
101 V1 Scale
101 V2 Pen
101 V2 Scale
101 V2 Eraser
102 V4 Pencil
102 V4 Sharpener
102 V5 Scale
102 V5 Sharpener
So that no records Should come in the out put for V7 and V8
Please help me on this scenario.
Thanks in advance.
Ramesh
|
|
|
|
|
|
|
|
| Re: missing data [message #443787 is a reply to message #443702] |
Wed, 17 February 2010 05:42 |
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi Experts,
Now I am explaing the requirement clearlay.
The following is the table and values in that table.
CREATE TABLE Item(item_code NUMBER(3),item_name VARCHAR2(20),visit Varchar2(4));
INSERT INTO item VALUES(101,'PEN','V1');
INSERT INTO item VALUES(101,'PENCIL','V1');
INSERT INTO item VALUES(101,'SCALE','V1');
INSERT INTO item VALUES(101,'PENCIL','V2');
INSERT INTO item VALUES(102,'PEN','V4');
INSERT INTO item VALUES(102,'SCALE','V4');
INSERT INTO item VALUES(102,'PEN','V5');
INSERT INTO item VALUES(102,'PENCIL','V5');
INSERT INTO item VALUES(103,'PEN','V7');
INSERT INTO item VALUES(103,'PENCIL','V7');
INSERT INTO item VALUES(103,'SCALE','V7');
INSERT INTO item VALUES(103,'PEN','V8');
INSERT INTO item VALUES(103,'PENCIL','V8');
INSERT INTO item VALUES(103,'SCALE','V8');
Scenario 1:
If Visit is V1 or V2
The item_names should be Pen,Pencil,Scale,Eraser and Marker.
Scenario 2:
If Visit is V4 or V5
The item_names should be Pen,Pencil,Scale and Sharpener
Scenario 3:
If Visit is V7 or V8
The item_names should be Pen,Pencil and Scale.
Test Data1:
The following is the test data.
Item_code V1 V2
item_name item_name
101 Pen
101 Pencil Pencil
101 Scale
For V1 and V2
The item_names Pen,Pencil,Scale,Eraser and Marker
In this case Eraser,Marker for V1 and Pen,Scale,
Eraser,Marker for V2 should come in the output.
Because these are missing.
Test Data2:
Item_code V4 V5
item_name item_name
102 Pen Pen
102 Scale Pencil
For V4 and V5
The item_names should be Pen,Pencil,Scale and Sharpener
In this case Pencil,Sharpener for V4 and Scale,Sharpener
for V5 should come in the output.
Because these are missing.
Test Data3:
Item_code V7 V8
item_name item_name
103 Pen Pen
103 Pencil Pencil
103 Scale Scale
For V7 and V8
The item_names should be Pen,Pencil and Scale.
In this case for V7 and V8 no records should come in the output.
Because there is no missing item_names all data is presented in the table.
I hope this is very much clear.
Please help me as early as possible.
Thanks in advance.
|
|
|
|
|
|
| Re: missing data [message #443826 is a reply to message #443787] |
Wed, 17 February 2010 08:08 |
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
|
Senior Member |
|
|
I alined the data
[quote title=ramesh55.sse wrote on Wed, 17 February 2010 05:42]Hi Experts,
Now I am explaing the requirement clearlay.
The following is the table and values in that table.
CREATE TABLE Item(item_code NUMBER(3),item_name VARCHAR2(20),visit Varchar2(4));
INSERT INTO item VALUES(101,'PEN','V1');
INSERT INTO item VALUES(101,'PENCIL','V1');
INSERT INTO item VALUES(101,'SCALE','V1');
INSERT INTO item VALUES(101,'PENCIL','V2');
INSERT INTO item VALUES(102,'PEN','V4');
INSERT INTO item VALUES(102,'SCALE','V4');
INSERT INTO item VALUES(102,'PEN','V5');
INSERT INTO item VALUES(102,'PENCIL','V5');
INSERT INTO item VALUES(103,'PEN','V7');
INSERT INTO item VALUES(103,'PENCIL','V7');
INSERT INTO item VALUES(103,'SCALE','V7');
INSERT INTO item VALUES(103,'PEN','V8');
INSERT INTO item VALUES(103,'PENCIL','V8');
INSERT INTO item VALUES(103,'SCALE','V8');
Scenario 1:
If Visit is V1 or V2
The item_names should be Pen,Pencil,Scale,Eraser and Marker.
Scenario 2:
If Visit is V4 or V5
The item_names should be Pen,Pencil,Scale and Sharpener
Scenario 3:
If Visit is V7 or V8
The item_names should be Pen,Pencil and Scale.
Test Data1:
The following is the test data.
Item_code V1 V2
ITEM_NAME ITEM_NAME
101 Pen
101 Pencil Pencil
101 Scale
For V1 and V2
The item_names Pen,Pencil,Scale,Eraser and Marker
In this case Eraser,Marker for V1 and Pen,Scale,
Eraser,Marker for V2 should come in the output.
Because these are missing.
Test Data2:
Item_code V4 V5
item_name item_name
102 Pen Pen
102 Scale Pencil
For V4 and V5
The item_names should be Pen,Pencil,Scale and Sharpener
In this case Pencil,Sharpener for V4 and Scale,Sharpener
for V5 should come in the output.
Because these are missing.
Test Data3:
Item_code V7 V8
item_name item_name
103 Pen Pen
103 Pencil Pencil
103 Scale Scale
For V7 and V8
The item_names should be Pen,Pencil and Scale.
In this case for V7 and V8 no records should come in the output.
Because there is no missing item_names all data is presented in the table.
I hope this is very much clear.
Please help me as early as possible.
Thanks in advance.
[Updated on: Wed, 17 February 2010 08:34] Report message to a moderator |
|
|
|
|
|
| Re: missing data [message #443833 is a reply to message #443826] |
Wed, 17 February 2010 08:36 |
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
|
Senior Member |
|
|
[quote title=ramesh55.sse wrote on Wed, 17 February 2010 08:08]I alined the data
ramesh55.sse wrote on Wed, 17 February 2010 05:42
I aligned the test data please solve the problem
Hi Experts,
Now I am explaing the requirement clearlay.
The following is the table and values in that table.
CREATE TABLE Item(item_code NUMBER(3),item_name VARCHAR2(20),visit Varchar2(4));
INSERT INTO item VALUES(101,'PEN','V1');
INSERT INTO item VALUES(101,'PENCIL','V1');
INSERT INTO item VALUES(101,'SCALE','V1');
INSERT INTO item VALUES(101,'PENCIL','V2');
INSERT INTO item VALUES(102,'PEN','V4');
INSERT INTO item VALUES(102,'SCALE','V4');
INSERT INTO item VALUES(102,'PEN','V5');
INSERT INTO item VALUES(102,'PENCIL','V5');
INSERT INTO item VALUES(103,'PEN','V7');
INSERT INTO item VALUES(103,'PENCIL','V7');
INSERT INTO item VALUES(103,'SCALE','V7');
INSERT INTO item VALUES(103,'PEN','V8');
INSERT INTO item VALUES(103,'PENCIL','V8');
INSERT INTO item VALUES(103,'SCALE','V8');
Scenario 1:
If Visit is V1 or V2
The item_names should be Pen,Pencil,Scale,Eraser and Marker.
Scenario 2:
If Visit is V4 or V5
The item_names should be Pen,Pencil,Scale and Sharpener
Scenario 3:
If Visit is V7 or V8
The item_names should be Pen,Pencil and Scale.
Test Data1:
The following is the test data.
Item_code V1 V2
ITEM_NAME ITEM_NAME
101 Pen
101 Pencil Pencil
101 Scale
For V1 and V2
The item_names Pen,Pencil,Scale,Eraser and Marker
In this case Eraser,Marker for V1 and Pen,Scale,
Eraser,Marker for V2 should come in the output.
Because these are missing.
Test Data2:
Item_code V4 V5
item_name item_name
102 Pen Pen
102 Scale Pencil
For V4 and V5
The item_names should be Pen,Pencil,Scale and Sharpener
In this case Pencil,Sharpener for V4 and Scale,Sharpener
for V5 should come in the output.
Because these are missing.
Test Data3:
Item_code V7 V8
item_name item_name
103 Pen Pen
103 Pencil Pencil
103 Scale Scale
For V7 and V8
The item_names should be Pen,Pencil and Scale.
In this case for V7 and V8 no records should come in the output.
Because there is no missing item_names all data is presented in the table.
I hope this is very much clear.
Please help me as early as possible.
Thanks in advance.
[Updated on: Wed, 17 February 2010 08:50] Report message to a moderator |
|
|
|
| Re: missing data [message #443888 is a reply to message #443833] |
Wed, 17 February 2010 21:20 |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Sql+>CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000);
2 /
Type created.
Sql+>CREATE OR REPLACE FUNCTION in_list (p_in_list IN VARCHAR2)
2 RETURN t_in_list_tab
3 AS
4 l_tab t_in_list_tab := t_in_list_tab();
5 l_text VARCHAR2(32767) := p_in_list || ',';
6 l_idx NUMBER;
7 BEGIN
8 LOOP
9 l_idx := INSTR(l_text, ',');
10 EXIT WHEN NVL(l_idx, 0) = 0;
11 l_tab.extend;
12 l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
13 l_text := SUBSTR(l_text, l_idx + 1);
14 END LOOP;
15
16 RETURN l_tab;
17 END;
18 /
Function created.
Sql+>
CREATE TABLE Item(item_code NUMBER(3),item_name VARCHAR2(20),visit Varchar2(4));
Table created.
Sql+>INSERT INTO item VALUES(101,'PEN','V1');
1 row created.
Sql+>INSERT INTO item VALUES(101,'PENCIL','V1');
1 row created.
Sql+>INSERT INTO item VALUES(101,'SCALE','V1');
1 row created.
Sql+>INSERT INTO item VALUES(101,'PENCIL','V2');
1 row created.
Sql+>INSERT INTO item VALUES(102,'PEN','V4');
1 row created.
Sql+>INSERT INTO item VALUES(102,'SCALE','V4');
1 row created.
Sql+>INSERT INTO item VALUES(102,'PEN','V5');
1 row created.
Sql+>INSERT INTO item VALUES(102,'PENCIL','V5');
1 row created.
Sql+>INSERT INTO item VALUES(103,'PEN','V7');
1 row created.
Sql+>INSERT INTO item VALUES(103,'PENCIL','V7');
1 row created.
Sql+>INSERT INTO item VALUES(103,'SCALE','V7');
1 row created.
Sql+>INSERT INTO item VALUES(103,'PEN','V8');
1 row created.
Sql+>INSERT INTO item VALUES(103,'PENCIL','V8');
1 row created.
Sql+>INSERT INTO item VALUES(103,'SCALE','V8');
1 row created.
Sql+>commit;
Commit complete.
Sql+>desc item
Name Null? Type
------------------------------------------------------- -------- -------------------------------------
1 ITEM_CODE NUMBER(3)
2 ITEM_NAME VARCHAR2(20)
3 VISIT VARCHAR2(4)
Sql+>col item_name format a25
Sql+>select column_value as Item_name from (select * from table(in_list('PEN,PENCIL,SCALE,ERASOR,MARKER')))
2 minus
3 select item_name from item where visit='V1';
ITEM_NAME
-------------------------
ERASOR
MARKER
Sql+>select column_value as Item_name from (select * from table(in_list('PEN,PENCIL,SCALE,ERASOR,MARKER')))
2 minus
3 select item_name from item where visit='V2';
ITEM_NAME
-------------------------
ERASOR
MARKER
PEN
SCALE
Sql+>select column_value as Item_name from (select * from table(in_list('PEN,PENCIL,SCALE,SHARPNER')))
2 minus
3 select item_name from item where visit='V4';
ITEM_NAME
-------------------------
PENCIL
SHARPNER
Sql+>select column_value as Item_name from (select * from table(in_list('PEN,PENCIL,SCALE,SHARPNER')))
2 minus
3 select item_name from item where visit='V5';
ITEM_NAME
-------------------------
SCALE
SHARPNER
Sql+>select column_value as Item_name from (select * from table(in_list('PEN,PENCIL,SCALE')))
2 minus
3 select item_name from item where visit='V7';
no rows selected
Sql+>select column_value as Item_name from (select * from table(in_list('PEN,PENCIL,SCALE')))
2 minus
3 select item_name from item where visit='V8';
no rows selected
Sql+>
We can achive this kind of results in many other ways...
Let me input this as a first version....
And always maintain data in a proper way...
At your first example data is in init cap.
But at your latest it is all Caps.Which may leads wrong type of results.
Source of in_list function: Oracle Base Dynamic IN-Lists
Good luck
sriram
[Updated on: Wed, 17 February 2010 21:22] Report message to a moderator |
|
|
|
| Re: missing data [message #443901 is a reply to message #443888] |
Wed, 17 February 2010 23:40 |
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi Experts,
Good morning
I am appreciating your answer,but i need a answer in a single SQL query.
The result should be like this.
ITEM_COED VISIT ITEM_NAME
101 V1 Eraser
101 V1 Marker
101 V2 Pen
101 V2 Scale
101 V2 Eraser
101 V2 Marker
102 V4 Pencil
102 V4 sharpner
102 V5 Scale
102 V5 Sharpner
Please help me.
Thanks in advance.
|
|
|
|
| Re: missing data [message #443917 is a reply to message #443901] |
Thu, 18 February 2010 00:33 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
How coud we (or Oracle) know that item 101 and 102 do not miss visit V3, V4, V5, V6, V7, V8?
How could we know that item 103 does not miss visit V1, V2...?
In short how could we know the mandatory visits for each item?
Regards
Michel
[Updated on: Thu, 18 February 2010 00:37] Report message to a moderator |
|
|
|
|
|
| Re: missing data [message #443938 is a reply to message #443919] |
Thu, 18 February 2010 01:21 |
|
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Yes....Thats Exactly what i feel..
What ever the points raised in this topic are correct & perfect.
Sql+>CREATE GLOBAL TEMPORARY TABLE item_tab (visit varchar2(10),
2 element VARCHAR2(4000)
3 );
Table created.
Sql+>CREATE OR REPLACE PROCEDURE setup_in_list (visit in varchar2,p_in_list IN VARCHAR2) AS
2 l_text VARCHAR2(32767) := p_in_list || ',';
3 l_idx NUMBER;
4 l_element VARCHAR2(32767);
5 BEGIN
6 LOOP
7 l_idx := INSTR(l_text, ',');
8 EXIT WHEN NVL(l_idx, 0) = 0;
9 l_element := TRIM(SUBSTR(l_text, 1, l_idx - 1));
10 l_text := SUBSTR(l_text, l_idx + 1);
11 INSERT INTO item_tab (visit,element)
12 VALUES (visit,l_element);
13 END LOOP;
14 END;
15 /
Procedure created.
Sql+>exec setup_in_list('V1','PEN,PENCIL,SCALE,ERASOR,MARKER');
PL/SQL procedure successfully completed.
Sql+>exec setup_in_list('V2','PEN,PENCIL,SCALE,ERASOR,MARKER');
PL/SQL procedure successfully completed.
Sql+>exec setup_in_list('V4','PEN,PENCIL,SCALE,SHARPNER');
PL/SQL procedure successfully completed.
Sql+>exec setup_in_list('V5','PEN,PENCIL,SCALE,SHARPNER');
PL/SQL procedure successfully completed.
Sql+>exec setup_in_list('V7','PEN,PENCIL,SCALE');
PL/SQL procedure successfully completed.
Sql+>exec setup_in_list('V8','PEN,PENCIL,SCALE');
PL/SQL procedure successfully completed.
Sql+> select visit, element item_name from item_tab
2 minus
3 select visit,item_name from item;
VISIT ITEM_NAME
---------- -------------------------
V1 ERASOR
V1 MARKER
V2 ERASOR
V2 MARKER
V2 PEN
V2 SCALE
V4 PENCIL
V4 SHARPNER
V5 SCALE
V5 SHARPNER
10 rows selected.
Sql+>
Sql+>select visit||','||wm_concat(item_name)----->wm_concat which does not exist in oracle 9i.
2 from ( select visit, element item_name from item_tab
3 minus
4 select visit,item_name from item)
5 group by visit;
VISIT||','||WM_CONCAT(ITEM_NAME)
----------------------------------------------------------------------------------------------------
V1,ERASOR,MARKER
V2,ERASOR,SCALE,PEN,MARKER
V4,PENCIL,SHARPNER
V5,SCALE,SHARPNER
Sql+>select distinct a.item_code, b.visit,b.item_name from ( select visit, element item_name from item_tab
2 minus
3 select visit,item_name from item) b,item a
4 where a.visit=b.visit
5 order by visit,item_name
6 /
ITEM_CODE VISIT ITEM_NAME
---------- ---------- -------------------------
101 V1 ERASOR
101 V1 MARKER
101 V2 ERASOR
101 V2 MARKER
101 V2 PEN
101 V2 SCALE
102 V4 PENCIL
102 V4 SHARPNER
102 V5 SCALE
102 V5 SHARPNER
10 rows selected.
Sql+>
which is not perfect and not advisible....
Goodluck
sriram 
[Updated on: Thu, 18 February 2010 01:22] Report message to a moderator |
|
|
|
| Re: missing data [message #444218 is a reply to message #443938] |
Fri, 19 February 2010 07:19 |
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi Experts,
My aim is to find the data which is missing.
i.e. The data that is not entered according to the protocol.The item table containsmillions of rows.
I have given some rows as example.
I am working on Production data base so that i can't perform create,insert and delete operations.
For V1 and V2
The item_names Pen,Pencil,Scale,Eraser and Marker
should be entered into the form then that is inserted into the table.
In this case Eraser,Marker for V1 and Pen,Scale,
Eraser,Marker for V2 are not entered so that these itemnames should come in the output.
Because these are missing.
For remaing visits also same.
So please help me.
|
|
|
|
| Re: missing data [message #444223 is a reply to message #444218] |
Fri, 19 February 2010 07:40 |
|
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Quote:My aim is to find the data which is missing.
Best of luck.We really dont know what is missing and how to achive without knowing anything about how/why they are really missing!
Quote:I am working on Production data base so that i can't perform create,insert and delete operations.
you dont any access to your production environment But you should work for that Good.sounds great.
Quote:For V1 and V2
The item_names Pen,Pencil,Scale,Eraser and Marker
should be entered into the form then that is inserted into the table.
In this case Eraser,Marker for V1 and Pen,Scale,
Eraser,Marker for V2 are not entered so that these itemnames should come in the output.
Because these are missing.
Don`t you got that Output?
Quote:For remaing visits also same.
Again you changed the requirement ?
Think once again How one can know what are missing without any information.......
definately should check with some data which is having all the information right?
How can you say that some are missing without without having all the data information...
Hope its clear...
So please send a valid information.So that we will help you.
Goodluck
sriram
|
|
|
|
| Re: missing data [message #444224 is a reply to message #444218] |
Fri, 19 February 2010 08:01 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ramesh55.sse wrote on Fri, 19 February 2010 13:19I am working on Production data base so that i can't perform create,insert and delete operations.
Are you really, seriously, telling us that you don't have a test db you can use?
If you really don't have one then get one. Now. Before you do anything else.
|
|
|
|
| Re: missing data [message #444227 is a reply to message #444218] |
Fri, 19 February 2010 08:18 |
|
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You still don't answer to my questions:
Quote:How coud we (or Oracle) know that item 101 and 102 do not miss visit V3, V4, V5, V6, V7, V8?
How could we know that item 103 does not miss visit V1, V2...?
In short how could we know the mandatory visits for each item?
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 03:16:53 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
