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