oracle11g - Oracle SQL : Wrong error count per error code -

-

i trying run 1 sql query find out count per error code database. have 2 table

  1. sw_sms_events transaction id , sms sent stored.
  2. sw_events transaction id , error reason in case failed stored otherwise reason "successfully sent tarifftext".

total error count :- select count(*) sw_sms_events sms_text '%welkom in het buitenland%'

total error count per error reason :- 

select distinct count(*) on (partition b.reason) , b.reason  sw_sms_events a, sw_events b  a.transaction_id= b.transaction_id   , a.sms_text '%welkom in het buitenland%'  , b.reason !='successfully sent tarifftext'   order (count(*) on (partition 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 same transaction retried multiple times results not same .i.e. have multiple rows in table same transaction id.

below 1 of 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 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 service available active products , errormessage : bsl-15024   30  unknown error received :bea-382556 , errormessage : bsl-99999   18  customer information: not retrieved. action cannot continue without customer information. please try later or contact system administrator. , errormessage : bsl-10004    13  oms login failure: problem in oms uams login - nested exception/error: java.net.connectexception: tried all: '1' addresses, not connect on http 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 error or timed out , errormessage : bsl-11000    5 gettariffsandaddons, status: failure , errorcode : internal error occured , errormessage : bsl-14005 3  authorization failed of dealer market restrictions , errormessage : bsl-50005   2  com.amdocs.cih.exception.invalidusageexception: input parameter assignedproductref invalid. , errormessage : bsl-10004   1

my question how can modify current sql in such way total count of error should equal sum of individual error count when have wrost cases same transaction multiple times in table

i don't understand why using analytical query. isn't simpler group by sufficient ? 

select count(*), b.reason  sw_sms_events a, sw_events b  a.transaction_id= b.transaction_id   , a.sms_text '%welkom in het buitenland%'  , b.reason !='successfully sent tarifftext'   group b.reason order count(*) desc

when we have multiple rows in table same transaction id, mean in sw_events table or in both sw_sms_events , sw_events tables ?

if so, events counted multiple times because doing cartesian product on raws same transaction_id. should use stricter join clause.

you (quite ugly) :

select count(distinct b.rowid), b.reason  sw_sms_events a, sw_events b  a.transaction_id= b.transaction_id   , a.sms_text '%welkom in het buitenland%'  , b.reason !='successfully sent tarifftext'   group b.reason order count(distinct b.rowid) desc

to ensure each event counted once.


Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

wso2esb - How to concatenate JSON array values in WSO2 ESB? -

-
we have json object below, expected result in: "(001),(011),(089),(120)". can suggest how iterate json array , concat values mention . "(001),(011),(089),(120)" thanks in advance. { "element": { "values": { "agentid": "aaaaa", "transactiondata": [ { "no": "001" }, { "no": "011" }, { "no": "089" }, { "no": "120" } ] } } } you can using iterate mediator, filter mediator , properties operation scope. try solution. @ end have (001),(011),(089),(120) value in concat-data property. have added complete proxy reference. <?xml version="1.0" encoding=...
Read more