Oracle SQL: wrong error number for error code
I am trying to run one SQL query to find out the number of errors in the database. I have two tables
- sw_sms_events, which stores the transaction ID and sms that were sent.
- sw_events, where the transaction identifier and the reason for the error in case of failure, then it is saved, otherwise the reason is always "successfully sent TariffText".
Total errors: - select counter (*) from sw_sms_events, where sms_text like '% Welkom in het buitenland%'
Total number of errors by mistake: -
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
Usually these queries give the same result, i.e. the sum of the individual number of errors = total number of errors. But in the worst case scenarios where the same transaction is repeated multiple times, the results are not the same. ie we have multiple rows in the table with the same transaction id.
below is one of the worst case results:
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 can I change the current sql so that the total number of errors is always equal to the sum of the individual number of errors when we have wrost cases where the same transaction multiple times in the table
source to share
I don't understand why you are using an analytic query. Is simple enough group by
?
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 that we have multiple rows in a table with the same transaction id, do you mean only the table, sw_events
or in tables sw_sms_events
and sw_events
?
If so, the events are counted multiple times, because you are doing cartesian production in all raw files with the same transaction_id
. You should use stricter join conditions.
You can also do something (rather 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
so that each event is counted only once.
source to share
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
source to share