Oracle Query – How to Retrieve Missing Values of an ID from a List

oraclequerysubquery

have a data structure that is value/tag inside a table on Oracle sql
Lets say I have this structore

exTable

ID	TAG	VALUE
1	1	x
1	4	y
2	2	x
2	3	y
2	4	z

I want to retrieve the missing tags for a list of values I have, for example [1,2,3,4,5]

In this case Im searching for this as an output if I look for (1,2)

ID	MISSINGTAG
1	2
1	3
1	5
2	1
2	5

Been trying with temp tables and joins but im still missing something because when i try to join I either dont get anything or just get the ones that do exists, I achieved want I want for one id using a minus

select column_value as COL_NAME
from table(sys.dbms_debug_vc2coll('1',
'2',
'3',
'4',
'5'))
MINUS
SELECT TAG FROM exTable WHERE ID='1';

But for doing it for a list of IDS im have not had luck

If im duplicating the question let me know

Best Answer

One way is the create the cartesian product between your tags and existing id:s. From there you can subtract your existing table either by MINUS as in your example, or by EXISTS:

with d (tag, id) as (
    select tag, id 
    from table(sys.dbms_debug_vc2coll('1', '2', '3', '4', '5')) t 
    cross join ( 
        select distinct id from extable 
    )
) 
select * from d
where not exists (
    select 1 from extable x where x.id = d.id and x.tag = d.tag
);

Related Solutions

How to group results in batches of consecutive rows with the same ‘module’

The question is which of the pdates for a module do you want to use for ordering purposes?

If you are happy to use the largest, you could use:

select module from logging order by max(pdate);

--- EDIT

I think I get it now - you probably want something like this:

select module
from( select module, 
             sum(module_step) over (order by pdate rows unbounded preceding) 
                 as batch_number, 
             pdate
      from( select module, 
                   case lag(module) over (order by pdate) when module then 0 
                        else 1 end as module_step, 
                   pdate from logging) )
group by module, batch_number
order by batch_number;

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

Best Answer

Related Solutions

How to group results in batches of consecutive rows with the same ‘module’

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

Related Question