Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Return grouped records without a specific line type

20161212Nov 9 2022

I have this sample data:
image.pngThe 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:
image.pngAs 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

This post has been answered by Frank Kulash on Nov 9 2022
Jump to Answer
Comments
Post Details
Added on Nov 9 2022
3 comments
69 views