CODEWITHSUNDEEP
sqloracle11g
Tarun Bharti
Tarun Bharti

Reputation: 195

Oracle SQL : Wrong error count per error code

I am trying to run one SQL query to find out the count per error code from the database. I have two table

  1. sw_sms_events where the transaction id and sms that was sent is stored.
  2. sw_events where the transaction id and error reason in case it is failed then it is stored otherwise the reason is always "Successfully Sent TariffText".

Total error count :- select count(*) from sw_sms_events where sms_text like '%Welkom in het buitenland%'

Total error count per error reason :- 

select distinct count(*) over (partition by b.reason) , b.reason 
from sw_sms_events a, sw_events b 
where a.transaction_id= b.transaction_id  
and a.sms_text like '%Welkom in het buitenland%' 
and b.reason !='Successfully Sent TariffText'  
order by (count(*) over (partition by b.reason)) desc

Normally these queries gives same result i.e. sum of individual error count = total number of errors.But in worst case scenarios where the same transaction is retried multiple times the results are not same .i.e. we have multiple rows in table with same transaction id.

below is one of the result in case of worst case :

Name    24-07-2015
Total Number of SMSWelcome Sent 156788
Total Number of Error SMSWelcome    1738
Total Number of SMSWelcome Sent with null Tariffs   286



Error Reason    Error Count

Unknown error received :BEA-380000 , ErrorMessage : BSL-99999   1829
Backend system not available , ErrorMessage : BSL-50002 641
Remote Error    527

NativeQuery.executeQuery failed , ErrorMessage : BSL-11009  41
This service is available only for active products , ErrorMessage : BSL-15024   30

Unknown error received :BEA-382556 , ErrorMessage : BSL-99999   18

Customer information: Not retrieved. This action cannot continue without customer information. Please try later or contact your system administrator. , ErrorMessage : BSL-10004    13

OMS login failure: Problem in OMS UAMS login - Nested Exception/Error: java.net.ConnectException: Tried all: '1' addresses, but could not connect over HTTP to server: '195.233.102.177', port: '40123' ,   12

t3://195.233.102.171:30101: Bootstrap to: 195.233.102.171/195.233.102.171:30101' over: 't3' got an error or timed out , ErrorMessage : BSL-11000    5
getTariffsAndAddOns, status: Failure , ErrorCode : An internal error occured , ErrorMessage : BSL-14005 3

Authorization failed of dealer market restrictions , ErrorMessage : BSL-50005   2

com.amdocs.cih.exception.InvalidUsageException: The input parameter AssignedProductRef is invalid. , ErrorMessage : BSL-10004   1

My question is how i can modify the current sql in such a way that the total count of error should always be equal to sum of individual error count when we have wrost cases where same transaction is multiple times in a table

Upvotes: 3

Views: 669

Answers (2)

Tarun Bharti
Tarun Bharti

Reputation: 195

select distinct count(distinct b.ROWID) over (partition by b.reason) , b.reason 
from sw_sms_events a, sw_events b 
where a.transaction_id= b.transaction_id  
and a.sms_text like '%Welkom in het buitenland%' 
and b.reason !='Successfully Sent TariffText'  
order by (count(distinct b.ROWID) over (partition by b.reason)) desc

Upvotes: 0

Philippe Banwarth
Philippe Banwarth

Reputation: 17755

I don't really understand why you are using an analytical query. Isn't a simpler group by sufficient ? 

select count(*), b.reason 
from sw_sms_events a, sw_events b 
where a.transaction_id= b.transaction_id  
and a.sms_text like '%Welkom in het buitenland%' 
and b.reason !='Successfully Sent TariffText'  
group by b.reason
order by count(*) desc

When you say we have multiple rows in table with same transaction id, do you mean in sw_events table only or in both sw_sms_events and sw_events tables ?

If so, events are counted multiple times because you are doing a cartesian product on all raws with the same transaction_id. You should use a stricter join clause.

You could also do something (quite ugly) like :

select count(distinct b.ROWID), b.reason 
from sw_sms_events a, sw_events b 
where a.transaction_id= b.transaction_id  
and a.sms_text like '%Welkom in het buitenland%' 
and b.reason !='Successfully Sent TariffText'  
group by b.reason
order by count(distinct b.ROWID) desc

to ensure that each event is only counted once.

Upvotes: 1

Related Questions