Forum Stats

  • 3,875,380 Users
  • 2,266,909 Discussions
  • 7,912,186 Comments

Discussions

Return grouped records without a specific line type

20161212
20161212 Member Posts: 18 Blue Ribbon

I have this sample data:

The data is generated via:

with my_data as 
(select 3722 item_id, 'ITEM' item_type from dual union all
select 3722, 'REC_TAX' from dual union all
select 3722, 'NONREC_TAX' from dual union all
select 3722, 'TRV' from dual union all
select 3733, 'ITEM' from dual union all
select 3733, 'REC_TAX' from dual union all
select 3733, 'NONREC_TAX' from dual union all
select 3733, 'TRV' from dual union all
select 123411, 'ITEM' from dual union all
select 123411, 'REC_TAX' from dual union all
select 123411, 'NONREC_TAX' from dual union all
select 123411, 'TRV' from dual union all
select 63391, 'ITEM' from dual union all
select 63391, 'REC_TAX' from dual union all
select 63391, 'NONREC_TAX' from dual union all
select 712005, 'ITEM' from dual union all
select 712005, 'IPV' from dual union all
select 712005, 'REC_TAX' from dual union all
select 712005, 'NONREC_TAX' from dual union all
select 712005, 'TIPV' from dual)
select * from my_data

I am trying to work out how I can only return those records which do not contain a line type value of 'TRV'.

I can't simply do this:

with my_data as 
(select 3722 item_id, 'ITEM' item_type from dual union all
select 3722, 'REC_TAX' from dual union all
select 3722, 'NONREC_TAX' from dual union all
select 3722, 'TRV' from dual union all
select 3733, 'ITEM' from dual union all
select 3733, 'REC_TAX' from dual union all
select 3733, 'NONREC_TAX' from dual union all
select 3733, 'TRV' from dual union all
select 123411, 'ITEM' from dual union all
select 123411, 'REC_TAX' from dual union all
select 123411, 'NONREC_TAX' from dual union all
select 123411, 'TRV' from dual union all
select 63391, 'ITEM' from dual union all
select 63391, 'REC_TAX' from dual union all
select 63391, 'NONREC_TAX' from dual union all
select 712005, 'ITEM' from dual union all
select 712005, 'IPV' from dual union all
select 712005, 'REC_TAX' from dual union all
select 712005, 'NONREC_TAX' from dual union all
select 712005, 'TIPV' from dual)
	select md.item_id
		 , count(*)
	 from my_data md
	 where md.item_type <> 'TRV'
 group by md.item_id
 order by md.item_id

Because that returns this:

As in - the ITEM_ID records which do contain a 'TRV' line_type (IDs 3722, 3733 and 123411) are still returned, but the count does not include the 'TRV' line types.

Since the number of different line_types per ID is not always made up of a constant number (e.g. all of the different line types can include a combination of, but not all at once ('ITEM','TRV','TIPV','NONREC_TAX','REC_TAX','IPV') and others that I haven't listed in the sample data), then I can't use a route like this (used for a similar query I once asked):

-- #############################################################################
-- CUSTOMERS - CHECK IF THEY HAVE ONE SITE BUT NOT ANOTHER
-- #############################################################################

		select e.acnum
			 , e.party_name
			 , e.cust_id
			 , e.cust_num
			 , e.party_id
			 , e.cc
		 from 
(select hca.account_number acnum
			 , hp.party_name
			 , hca.cust_account_id cust_id
			 , hca.account_number cust_num
			 , hca.party_id
			 , hca.customer_class_code cc
			 , hcsua.site_use_code site_use
		 from ar.hz_parties hp
		 join ar.hz_party_sites hps on hp.party_id = hps.party_id
		 join ar.hz_cust_accounts hca on hp.party_id = hca.party_id
		 join ar.hz_cust_acct_sites_all hcasa on hcasa.party_site_id = hps.party_site_id
		 join ar.hz_cust_site_uses_all hcsua on hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
		 where 1 = 1
		  and hca.status = 'A'
		  and hcasa.status = 'A'
		  and 1 = 1) e
		 where e.site_use in ('BILL_TO', 'SHIP_TO')
	 group by e.acnum
			 , e.party_name
			 , e.cust_id
			 , e.party_id
			 , e.cc
having count (distinct site_use) = 1;

I wondered how I might be able to return records which don't have a 'TRV' line type in any of their line types?

Sorry for my mistakes, bad code, etc.

Any advice much appreciated.

Thanks

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,778 Red Diamond
    Answer ✓

    Hi, @20161212

    how I can only return those records which do not contain a line type value of 'TRV'.

    What is a "record"? Is it a row? Is it a group of rows? Is it an item_id?

    If you want to get a list of item_ids where none of the rows with that item_id have item_type='TRV', you can do it like this:

    SELECT    item_id
    FROM	  my_data
    GROUP BY  item_id
    HAVING	  MIN ( CASE
    	      	    WHEN  item_type = 'TRV'
    		    THEN  'BAD'
    	      	END
    	      )  IS NULL
    ORDER BY  item_id	-- or whatever you want
    ;
    

    Output:

       ITEM_ID
    ----------
         63391
        712005
    


Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,778 Red Diamond
    Answer ✓

    Hi, @20161212

    how I can only return those records which do not contain a line type value of 'TRV'.

    What is a "record"? Is it a row? Is it a group of rows? Is it an item_id?

    If you want to get a list of item_ids where none of the rows with that item_id have item_type='TRV', you can do it like this:

    SELECT    item_id
    FROM	  my_data
    GROUP BY  item_id
    HAVING	  MIN ( CASE
    	      	    WHEN  item_type = 'TRV'
    		    THEN  'BAD'
    	      	END
    	      )  IS NULL
    ORDER BY  item_id	-- or whatever you want
    ;
    

    Output:

       ITEM_ID
    ----------
         63391
        712005
    


  • Stax
    Stax Member Posts: 184 Silver Badge
    SQL> ed
    Wrote file afiedt.buf
    
    
      1  with my_data as
      2  (select 3722 item_id, 'ITEM' item_type from dual union all
      3  select 3722, 'REC_TAX' from dual union all
      4  select 3722, 'NONREC_TAX' from dual union all
      5  select 3722, 'TRV' from dual union all
      6  select 3733, 'ITEM' from dual union all
      7  select 3733, 'REC_TAX' from dual union all
      8  select 3733, 'NONREC_TAX' from dual union all
      9  select 3733, 'TRV' from dual union all
     10  select 123411, 'ITEM' from dual union all
     11  select 123411, 'REC_TAX' from dual union all
     12  select 123411, 'NONREC_TAX' from dual union all
     13  select 123411, 'TRV' from dual union all
     14  select 63391, 'ITEM' from dual union all
     15  select 63391, 'REC_TAX' from dual union all
     16  select 63391, 'NONREC_TAX' from dual union all
     17  select 712005, 'ITEM' from dual union all
     18  select 712005, 'IPV' from dual union all
     19  select 712005, 'REC_TAX' from dual union all
     20  select 712005, 'NONREC_TAX' from dual union all
     21  select 712005, 'TIPV' from dual)
     22     select md.item_id
     23              , count(*)
     24      from my_data md
     25  --  where md.item_type <> 'TRV'
     26   group by md.item_id
     27   having count(decode(md.item_type,'TRV',1))=0
     28*  order by md.item_id
    SQL> /
    
    
       ITEM_ID   COUNT(*)
    ---------- ----------
         63391          3
        712005          5
    
    
    SQL>
    
    20161212
  • Mohamed Houri
    Mohamed Houri Member Posts: 1,284 Bronze Trophy
    edited Nov 9, 2022 3:55PM
    select
      a.item_id
      ,count(1)
    from
      my_datas a
     where not exists (select null from my_datas b where b.item_id = a.item_id and b.item_type ='TRV')
     group by 
      a.item_id
    order by a.item_id;
     ITEM_ID  COUNT(1)
    ---------- ----------
       63391     3
      712005     5
    

    Best regards

    Mohamed Houri

    20161212