Urgent: find data which does not exist in db 2

[pmk16]

I am not an Orcle expert and have been trying hard to come up with a solution for this.

For eg: TaskId = 1 has A,B,D in it but not C

whereas taskID = 2 has C,D,E in it

then it shud display ,
taskID = 1 does not have E in it
taskID = 2 does not have A,B in it

and similarly check for all taskIDs.
Could any Oracle expert throw some light on this.
Thanks in advance.

 

[pmk16]

I forgot to mention that Task Names like A,B,C etc may/may not be fixed.

If someone can suggest for fixed number of Task Names = 30, that would also be helpful

 

[Turkbear]

Hi,
Far too little info:

What table structure ( is TaskID a field?)
Can each record have only 1 value for TaskID? )
What exactly are you trying to find out about those ID values.

How can we determine how many task names you need?
( How many tasks are you tracking? How is that field populated?).









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[pmk16]

Hi Tukbear,

Thank you so for your help.
Here is a true scenario:

PROJECT_ID TASK_NAME
[NUMBER (22)] [VARCHAR2 (4)]
--------------------------------------------
111 MP0
111 MP1
111 MP2
222 MP0
222 MP1
222 MP3
333 MP0
333 MP1
444 MP2
444 MP4

Hence, output should be:

PROJECT_ID NOT_TASK_NAME
[NUMBER (22)] [VARCHAR2 (4)]
--------------------------------------------
111 MP3
111 MP4
222 MP2
222 MP4
333 MP2
333 MP3
333 MP4
444 MP0
444 MP1
444 MP3

Yes, each record will only have one value for the Task_Name. Thus, task_name is a Varchar2 type and each project can have number of tasks under it; which will form a unique record as shown above.
To be honest, I have been given fixed set of task_names with which i have to compare all the records ( 30 to be precise). So, if there was a way to compare each project_id with distinct task_names and see if that project does not have the following task_names, is fine and should ideally do for now.

thanks again for your help

 

[datamart]

pmk,

try this out..

select A.project_id PROJECTID, B.task_id TASKID
from
tabProject A,
(select distinct task_id from tabProject) B
where A.task_id <> B.task_id
minus
select C.project_id PROJECTID, C.task_id TASKID from
tabProject C

 

[pmk16]

hey datamart,

you are awesome! I had been breaking my head for so long and you really made me realize that I don't know much about SQL

Your query worked like a charm

Thank you tek-tips for setting up this forum.

 

[pmk16]

Hey guys,

May be I didnt put the question properly.

The current query is matching with all possible task names; whereas I want to match with only 30 fixed existing task names like MP0,Mp1,...Mp29 out of dynamically populated table with more than 5000 tasks.

So, do you think if i modify the existing query to:

select A.project_id PROJECTID, B.task_NAME TASKNAME
from
tabProject A,
(select task_NAME from tabProject where task_name IN('MP0','MP1','MP2','MP3','MP4','MP5')) B
where A.task_name <> B.task_name
minus
select C.project_id PROJECTID, C.task_name TASKNAME from
tabProject C


Do you think this is the correct approach?
Thanks again

 

[pmk16]

Else, i keep getting;
ORA-04031: unable to allocate 8192 bytes of shared memory ("large pool","unknown object","sort subheap","sort key") error

 

[pmk16]

I am sorry, I had misinterpreted the question.

this is the exact scenario:
So basically out of 5000 tasks, 30 are chosen(fixed) as key tasks and the query should list any project that does not contain all key tasks, and list which key tasks are missing

 

[pmk16]

please don't bother. I got this sorted out. Thanks Tek-Tips