[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Oracle sql to find missing data

 
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
anumoses
Asked:
anumoses
  • 27
  • 18
  • 6
  • +1
2 Solutions
 
anumosesAuthor Commented:
0
 
anumosesAuthor Commented:
For a given date, I need to find missing results
0
 
slightwv (䄆 Netminder) Commented:
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!

 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
I mean missing results
0
 
slightwv (䄆 Netminder) Commented:
That doesn't help clear anything up to me.

A minus should still work.

Please post sample data and expected results.
0
 
Devinder Singh VirdiCommented:
you can use Minus as mentioned in previous post.
you can also think of outer join and not in operator.
0
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
This query will be used to run a  report
0
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
Is connect by a 10G concept? I am running 9i database
0
 
anumosesAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
Can we narrow down by date
0
 
anumosesAuthor Commented:
like create time in qc_task_results table?
0
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
You didn't answer: how many rows?
0
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
653 rows in qc_task_results
0
 
slightwv (䄆 Netminder) Commented:
Still only 3 possible tasks?
0
 
anumosesAuthor Commented:
202 in qc_tasks
0
 
slightwv (䄆 Netminder) Commented:
While I work on this, did you try the Cartesian version posted in http:#a36549237 ?
0
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:

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
 
slightwv (䄆 Netminder) Commented:
>>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
 
anumosesAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
I do not understand that last post.  Are you asking something?
0
 
anumosesAuthor Commented:
yes I want to add a where clause to drill down the results to a date from qc_task_results table
0
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
sending table structure. Will post sample data
table-structure.txt
0
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
On the above I did not find a join condition, It was taking time to run
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
anumosesAuthor Commented:
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
0
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
9/13/2011|1067
 is missing in the results table. That is what I want to list
0
 
sdstuberCommented:
what are the xls files you keep loading?  are those tables or query results or what?
0
 
anumosesAuthor Commented:
I loaded the table structure and data for those tables
0
 
sdstuberCommented:
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
 
sdstuberCommented:
also, need expected results

if you want date sensitive results, then also specify what date(s) you are looking to get results for
0
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
thanks
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 27
  • 18
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now