?
Solved

Oracle sql to find missing data

Posted on 2011-09-15
52
Medium Priority
?
416 Views
Last Modified: 2012-05-12
 
select task_id,a.procedure_id,task_desc
from qc_tasks a,qc_procedures b
where a.procedure_id = b.procedure_id
and b.procedure_id = 1066
-------------------
TASK_ID|PROCEDURE_ID|TASK_DESC
1279|1066|Weight set value (ml)
1067|1066|Calibrated Weight Value (grams)
1066|1066|Calibration performed successfully -"Cal-0" displays

-------------------------------

select a.procedure_id,b.task_id,task_desc,c.equip_code,
a.qc_subtype_id,alpha_value,num_value from
qc_procedures a,qc_tasks b,qc_task_results c,qc_subtype d
where a.procedure_id = b.procedure_id
  and b.task_id = c.task_id
  and a.qc_subtype_id = d.qc_subtype_id
  and c.dept_id = 23
  and trunc(c.create_time) = trunc(sysdate-2)
  and equip_type_id = 1003
  and c.equip_code = 'SCALHEM113'
-----------------------

see attached xls file

My question is

for a given data, I want to list what were the tasks missing.
In the first query they were supposed to complete 3 tasks, but the results table has 2 tasks completed. So need a query to find missing task results


data-screen.xls
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 27
  • 18
  • 6
  • +1
52 Comments
 
LVL 6

Author Comment

by:anumoses
ID: 36546289
0
 
LVL 6

Author Comment

by:anumoses
ID: 36546292
For a given date, I need to find missing results
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36546360
On mobile and cannot view XLS but it sounds like a MINUS will work:

Select col1, col2 from first_query
Minus
Select col1, col2 from second_query;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:anumoses
ID: 36546399
Actually I hard coded the values for equip code, procedure id and equip type id

That was the error we found out. But for a given date I want to know all missing data.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36546404
I mean missing results
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36546460
That doesn't help clear anything up to me.

A minus should still work.

Please post sample data and expected results.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36546559
you can use Minus as mentioned in previous post.
you can also think of outer join and not in operator.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36548658
select equip_code,a.task_id,task_desc,qc_subtype_id            
from qc_task_results a,qc_tasks b
where a.task_id = b.task_id
  and qc_subtype_id = 1013
  and a.procedure_id = 1066
  and dept_id = 23
  and trunc(a.create_time) = trunc(sysdate-3)
-------------------------
EQUIP_CODE|TASK_ID|TASK_DESC|QC_SUBTYPE_ID
SCALHEM036|1066|Calibration performed successfully -"Cal-0" displays|1013
SCALHEM036|1067|Calibrated Weight Value (grams)|1013
SCALHEM036|1279|Weight set value (ml)|1013
SCALHEM114|1066|Calibration performed successfully -"Cal-0" displays|1013
SCALHEM114|1067|Calibrated Weight Value (grams)|1013
SCALHEM114|1279|Weight set value (ml)|1013
SCALHEM112|1066|Calibration performed successfully -"Cal-0" displays|1013
SCALHEM112|1067|Calibrated Weight Value (grams)|1013
SCALHEM112|1279|Weight set value (ml)|1013
SCALHEM113|1066|Calibration performed successfully -"Cal-0" displays|1013
SCALHEM113|1279|Weight set value (ml)|1013

All equip codes have three result sets, but SCALHEM113 is missing one result.
--------------------------
select task_id,task_desc,qc_subtype_id
from qc_tasks a,qc_procedures b
where a.procedure_id = b.procedure_id
and b.procedure_id = 1066
and qc_subtype_id = 1013

TASK_ID|TASK_DESC|QC_SUBTYPE_ID
1279|Weight set value (ml)|1013
1067|Calibrated Weight Value (grams)|1013
1066|Calibration performed successfully -"Cal-0" displays|1013

These are the tasks to be performed for that qc subtype and for that procedure id.
And I want to know for a given date, what were the tasks to be performed and what is missing the result.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36548680
select equip_code,a.task_id,task_desc,qc_subtype_id              
from qc_task_results a,qc_tasks b
where a.task_id = b.task_id
  and qc_subtype_id = 1013
  and a.procedure_id = 1066
  and dept_id = 23
  and trunc(a.create_time) = trunc(sysdate-3)

Here how can I get a count as three for the equip codes having three and the one that is having two results.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36549237
As long as you don't have many rows in the tables, the following should work.

It is a quick and dirty approach that uses a cartesian join.  If you have a lot of rows, this probably will not perform well.

If I get time later I'll try to come up with a better method.

A lot of your questions involve Forms:  Does this have to be a single SQL select or can it involve some PL/SQL inside Forms?
drop table tab1 purge;
create table tab1( EQUIP_CODE varchar2(20), TASK_ID number, TASK_DESC varchar2(100), QC_SUBTYPE_ID number);

insert into tab1 values('SCALHEM036',1066,'Calibration performed successfully -"Cal-0" displays',1013);
insert into tab1 values('SCALHEM036',1067,'Calibrated Weight Value (grams)',1013);
insert into tab1 values('SCALHEM036',1279,'Weight set value (ml)',1013);
insert into tab1 values('SCALHEM114',1066,'Calibration performed successfully -"Cal-0" displays',1013);
insert into tab1 values('SCALHEM114',1067,'Calibrated Weight Value (grams)',1013);
insert into tab1 values('SCALHEM114',1279,'Weight set value (ml)',1013);
insert into tab1 values('SCALHEM112',1066,'Calibration performed successfully -"Cal-0" displays',1013);
insert into tab1 values('SCALHEM112',1067,'Calibrated Weight Value (grams)',1013);
insert into tab1 values('SCALHEM112',1279,'Weight set value (ml)',1013);
insert into tab1 values('SCALHEM113',1066,'Calibration performed successfully -"Cal-0" displays',1013);
insert into tab1 values('SCALHEM113',1279,'Weight set value (ml)',1013);
commit;

drop table tab2 purge;
create table tab2 (TASK_ID number, TASK_DESC varchar2(100), QC_SUBTYPE_ID number);

insert into tab2 values(1279,'Weight set value (ml)',1013);
insert into tab2 values(1067,'Calibrated Weight Value (grams)',1013);
insert into tab2 values(1066,'Calibration performed successfully -"Cal-0" displays',1013);

commit;

select t1.equip_code, t2.task_id
from tab1 t1, tab2 t2
minus
select t1.equip_code, task_id from tab1 t1
/

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 36549502
This query will be used to run a  report
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36549691
Here is a solution that I hope you do not ask for a lot of explanation on 'how' it works.

I had our resident MATH wiz/expert in most things computers help with this one.

It appears to work for all tests I threw at it and scales fairly well.

It uses the same test tables above.
select equip_code, lookup.task_id from
(
select task_id, mylevel,
bitand(power(2,myrownum-1),t2.mylevel) -- assigns individual tasks to combinations of task values:  a 3 is a combination of the bit values 1 and 2
from (select task_id, rownum myrownum from tab2) t3,
(select /* generate list of numbers up to all possible combinatiosn of tasks */ level mylevel from dual connect by level <= (select power(2,count(1))-1 from tab2)) t2
where bitand(power(2,myrownum-1),t2.mylevel) > 0
) lookup,
(
select equip_code, total_bits-sum(t2.mybits) missing_item
from tab1 t1,
	(select task_id, power(2,rownum-1) myBits /* generate bit values based on rowid */, sum(power(2,rownum-1)) over() total_bits from tab2) t2  -- add up sum of bit values for use above
where t1.task_id=t2.task_id
group by equip_code, total_bits
having total_bits-sum(t2.mybits) > 0
) missing
where lookup.mylevel=missing.missing_item
/

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 36549710
Is connect by a 10G concept? I am running 9i database
0
 
LVL 6

Author Comment

by:anumoses
ID: 36549723
SQL> ed
Wrote file afiedt.buf

  1  select equip_code, lookup.task_id from
  2  (
  3  select task_id, mylevel,
  4  bitand(power(2,myrownum-1),t2.mylevel) -- assigns individual tasks to combinations of task valu
  5  from (select task_id, rownum myrownum from tab2) t3,
  6  (select /* generate list of numbers up to all possible combinatiosn of tasks */ level mylevel f
  7  where bitand(power(2,myrownum-1),t2.mylevel) > 0
  8  ) lookup,
  9  (
 10  select equip_code, total_bits-sum(t2.mybits) missing_item
 11  from tab1 t1,
 12     (select task_id, power(2,rownum-1) myBits /* generate bit values based on rowid */, sum(power(
 13  where t1.task_id=t2.task_id
 14  group by equip_code, total_bits
 15  having total_bits-sum(t2.mybits) > 0
 16  ) missing
 17* where lookup.mylevel=missing.missing_item
SQL> /
from (select task_id, rownum myrownum from tab2) t3,
                                           *
ERROR at line 5:
ORA-01473: cannot have subqueries in CONNECT BY clause
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36549905
connect by should work in 9i.  I believe the issue is with the sub select to limit the amount.

Give me a while to see if I can come up with a work-around.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36550429
See if this one runs
select equip_code, lookup.task_id from
(
select task_id, mylevel,
bitand(power(2,myrownum-1),t2.mylevel) -- assigns individual tasks to combinations of task values:  a 3 is a combination of the bit values 1 and 2
from (select task_id, rownum myrownum from tab2) t3,
(select rownum mylevel from user_tab_columns  where rownum <= (select power(2,count(1))-1 from tab2)) t2
where bitand(power(2,myrownum-1),t2.mylevel) > 0
) lookup,
(
select equip_code, total_bits-sum(t2.mybits) missing_item
from tab1 t1,
	(select task_id, power(2,rownum-1) myBits /* generate bit values based on rowid */, sum(power(2,rownum-1)) over() total_bits from tab2) t2  -- add up sum of bit values for use above
where t1.task_id=t2.task_id
group by equip_code, total_bits
having total_bits-sum(t2.mybits) > 0
) missing
where lookup.mylevel=missing.missing_item
/

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 36550759
When I run the query using my tables I get this error

SQL> ed
Wrote file afiedt.buf

  1  select equip_code, lookup.task_id from
  2  (
  3  select task_id, mylevel,
  4  bitand(power(2,myrownum-1),t2.mylevel) -- assigns individual tasks to combinations of task valu
  5  from (select task_id, rownum myrownum from qc_tasks) t3,
  6  (select rownum mylevel from user_tab_columns
  7  where rownum <= (select power(2,count(1))-1 from qc_tasks)) t2
  8  where bitand(power(2,myrownum-1),t2.mylevel) > 0
  9  ) lookup,
 10  (
 11  select equip_code, total_bits-sum(t2.mybits) missing_item
 12  from qc_task_results t1,
 13     (select task_id, power(2,rownum-1) myBits /* generate bit values based on rowid */,
 14     sum(power(2,rownum-1)) over() total_bits from qc_task_results) t2  -- add up sum of bit values
 15  where t1.task_id=t2.task_id
 16  group by equip_code, total_bits
 17  having total_bits-sum(t2.mybits) > 0
 18  ) missing
 19* where lookup.mylevel=missing.missing_item
 20  /
        sum(power(2,rownum-1)) over() total_bits from qc_task_results) t2  -- add up sum of bit values for
            *
ERROR at line 14:
ORA-01426: numeric overflow

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36550785
How many rows do you have in qc_task_results?

Can you narrow down the number of results being returned?


If not,
You might have to just write a code loop to figure it out.

I'll see if I can come up with some pl/sql to do this.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36550797
Can we narrow down by date
0
 
LVL 6

Author Comment

by:anumoses
ID: 36550800
like create time in qc_task_results table?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36550811
You can narrow down by whatever you need.  Just add the where clause.

I don't have your data nor do I fully understand the complete query you posted.  I just created sample tables from your results.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36550830
select equip_code, lookup.task_id from
(
select task_id, mylevel,
bitand(power(2,myrownum-1),t2.mylevel) -- assigns individual tasks to combinations of task values:  a 3 is a combination of the bit values 1 and 2
from (select task_id, rownum myrownum from qc_tasks) t3,
(select rownum mylevel from user_tab_columns  
where rownum <= (select power(2,count(1))-1 from qc_tasks)) t2
where bitand(power(2,myrownum-1),t2.mylevel) > 0
) lookup,
(
select equip_code, total_bits-sum(t2.mybits) missing_item
from qc_task_results t1,
      (select task_id, power(2,rownum-1) myBits /* generate bit values based on rowid */,
      sum(power(2,rownum-1)) over() total_bits from qc_task_results) t2  -- add up sum of bit values for use above
where t1.task_id=t2.task_id
  and trunc(t1.create_time) = trunc(sysdate-3)
group by equip_code, total_bits
having total_bits-sum(t2.mybits) > 0
) missing
where lookup.mylevel=missing.missing_item

Same error. Did not help
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36550844
You didn't answer: how many rows?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36550850
The examples you posted had 11 rows from one set and three rows from another.

If you can provide me the actual numbers you are dealing with, I can dummy up tests on my end (unless there are millions of records).
0
 
LVL 6

Author Comment

by:anumoses
ID: 36550869
653 rows in qc_task_results
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36550878
Still only 3 possible tasks?
0
 
LVL 6

Author Comment

by:anumoses
ID: 36550885
202 in qc_tasks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36550990
While I work on this, did you try the Cartesian version posted in http:#a36549237 ?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36551018
Quick and dirty PL/SQL using the test case above
begin
	for i in (select distinct equip_code from tab1) loop
		for j in (select task_id from tab2 minus select task_id from tab1 where equip_code=i.equip_code) loop
			dbms_output.put_line(i.equip_code || ' is missing task ' || j.task_id);
		end loop;
	end loop;
end;
/

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 36551071

begin
      for i in (select distinct equip_code from qc_task_results) loop
            for j in (select task_id from qc_tasks minus select task_id
            from qc_task_results where equip_code=i.equip_code) loop
                  dbms_output.put_line(i.equip_code || ' is missing task ' || j.task_id);
            end loop;
      end loop;
end;

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 5
But when I run like this
SQL> ed
Wrote file afiedt.buf

  1  begin
  2     for i in (select distinct equip_code from qc_task_results) loop
  3             for j in (select task_id from qc_tasks minus select task_id
  4                 from qc_task_results where equip_code=i.equip_code) loop
  5      dbms_output.put_line(i.equip_code || ' is missing task ' || j.task_id);
  6             end loop;
  7     end loop;
  8* end;
SQL> /

PL/SQL procedure successfully completed.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36551087
>>ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes

Increase the size of your serveroutput.  I forget what the max is in 9i:  set serveroutput 1000000

Anyway, that is just for display purposes to the screen.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36551284
select equip_code, lookup.task_id from
(
select task_id, mylevel,
bitand(power(2,myrownum-1),t2.mylevel) -- assigns individual tasks to combinations of task values:  a 3 is a combination of the bit values 1 and 2
from (select task_id, rownum myrownum from qc_tasks) t3,
(select rownum mylevel from user_tab_columns  where rownum <= (select power(2,count(1))-1 from qc_tasks)) t2
where bitand(power(2,myrownum-1),t2.mylevel) > 0
) lookup,
(
select equip_code, total_bits-sum(t2.mybits) missing_item
from qc_tasK_RESULTS t1,
      (select task_id, power(2,rownum-1) myBits /* generate bit values based on rowid */,
      sum(power(2,rownum-1)) over() total_bits from qc_tasks) t2  -- add up sum of bit values for use above
where t1.task_id=t2.task_id
group by equip_code, total_bits
having total_bits-sum(t2.mybits) > 0
) missing
where lookup.mylevel=missing.missing_item
---------------
If I have to add a where clause
where trunc(create_time) = trunc(sysdate-3) in qc_task_results table
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36551295
I do not understand that last post.  Are you asking something?
0
 
LVL 6

Author Comment

by:anumoses
ID: 36551341
yes I want to add a where clause to drill down the results to a date from qc_task_results table
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36551373
I don't have your structure so cannot say with 100% confidence but what you tried in http:#a36550830 looks correct.

There are three different possibilities here.

You might not be able to use the one with "connect by".  The numbers just might not allow it.

I don't see where you tried the version in http:#a36549237 and the pl/sql version once you set serverouptut to a decent size.

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36551390
can you post your real table structures and some sample data for those tables?

not data for query results, we can run the queries ourselves once we have a test case to reproduce

also, for your sample data, please include the expected results.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36551421
sending table structure. Will post sample data
table-structure.txt
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 36551433
The one I posted in http:#a36549237 seems to run fine with 800 rows in the master table and 200 rows in the task table.
drop table tab1 purge;
create table tab1( EQUIP_CODE varchar2(20), TASK_ID number, TASK_DESC varchar2(100), QC_SUBTYPE_ID number);

insert into tab1 (select 'a', rownum, null, null from xmltable('1 to 200'));
insert into tab1 (select 'b', rownum, null, null from xmltable('1 to 200'));
insert into tab1 (select 'c', rownum, null, null from xmltable('1 to 200'));
insert into tab1 (select 'd', rownum, null, null from xmltable('1 to 200'));

delete from tab1 where task_id in (1,3,5,7,11) and equip_code='a';
delete from tab1 where task_id in (15,16,17) and equip_code='b';
delete from tab1 where task_id in (19) and equip_code='c';

drop table tab2 purge;
create table tab2 (TASK_ID number, TASK_DESC varchar2(100), QC_SUBTYPE_ID number);
insert into tab2 (select rownum, null, null from xmltable('1 to 200'));

select t1.equip_code, t2.task_id
from tab1 t1, tab2 t2
minus
select t1.equip_code, task_id from tab1 t1
/

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 36551452
On the above I did not find a join condition, It was taking time to run
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36551481
>>I did not find a join condition

That is the definition of a Cartesian product.

I basically needed to generate a list of ALL possible values for ALL equip_codes and complete task_ids.

Then subtract out the 'missing' ones.

Since this is going in Forms code, the PL/SQL loop should be pretty quick.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36551483
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 36551488
using what slightwv posted above I think you're looking for something like this (going back to the orginal MINUS idea)

to check for a single day...

SELECT to_date('2011-09-13','yyyy-mm-dd') create_time, task_id
  FROM tab2
MINUS
SELECT create_time, task_id FROM tab1;

to check for multiple days... (in this example I'm checking the previous 5 days)


SELECT d, tab2.task_id
  FROM tab2,
       (SELECT     TRUNC(SYSDATE) - LEVEL d
              FROM DUAL
        CONNECT BY LEVEL <= 5)
MINUS
SELECT create_time, task_id FROM tab1




0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36551503
since 9i supports subquery factoring

you can use your original queries for tab1 and tab2, just include create_time as part of your tab1 results



with tab1 as (select create_time, equip_code,a.task_id,task_desc,qc_subtype_id            
from qc_task_results a,qc_tasks b
where a.task_id = b.task_id
  and qc_subtype_id = 1013
  and a.procedure_id = 1066
  and dept_id = 23
),
tab2 as (
select task_id,task_desc,qc_subtype_id
from qc_tasks a,qc_procedures b
where a.procedure_id = b.procedure_id
and b.procedure_id = 1066
and qc_subtype_id = 1013
)
SELECT to_date('2011-09-13','yyyy-mm-dd') create_time, task_id
  FROM tab2
MINUS
SELECT create_time, task_id FROM tab1;
0
 
LVL 6

Author Comment

by:anumoses
ID: 36551507
0
 
LVL 6

Author Comment

by:anumoses
ID: 36551519
Above query give me this result

CREATE_TIME|TASK_ID
9/13/2011|1066
9/13/2011|1067
9/13/2011|1279

Results table has only two tasks
0
 
LVL 6

Author Comment

by:anumoses
ID: 36551528
9/13/2011|1067
 is missing in the results table. That is what I want to list
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36551540
what are the xls files you keep loading?  are those tables or query results or what?
0
 
LVL 6

Author Comment

by:anumoses
ID: 36551575
I loaded the table structure and data for those tables
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36551595
I saw your file of 2 tables but your queries involve 4 tables (tasks, procedures, subtypes, results)

 afterward you loaded 2 more files with no explanation of what they contained.

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36551600
also, need expected results

if you want date sensitive results, then also specify what date(s) you are looking to get results for
0
 
LVL 6

Author Comment

by:anumoses
ID: 36551659
But the main tables are qc_tasks that has procedure_id
and qc_task_results.

I will post the expected results.

Actually for sept 13th 2011, dept 23, did a qc_subtype
 qc_subtype_id = 1013
  procedure_id = 1066
  dept_id = 23
qc'd 4 scales

EQUIP_CODE|TASK_ID|PROCEDURE_ID|ALPHA_VALUE|NUM_VALUE
SCALHEM036|1066|1066|Y|
SCALHEM036|1067|1066||499
SCALHEM036|1279|1066||500
SCALHEM114|1066|1066|Y|
SCALHEM114|1067|1066||500
SCALHEM114|1279|1066||500
SCALHEM112|1066|1066|Y|
SCALHEM112|1067|1066||501
SCALHEM112|1279|1066||500
SCALHEM113|1066|1066|Y|
SCALHEM113|1279|1066||500
-----------------------
Each has 3 set of results except for
SCALHEM113|1066|1066|Y|
SCALHEM113|1279|1066||500

that has only 2 results. third is missing. I want to show the missing
EQUIP_CODE|TASK_ID
SCALHEM113|1067
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 36891370
thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question