Home » SQL & PL/SQL » SQL & PL/SQL » How to get a count for this error query
How to get a count for this error query [message #206346] Wed, 29 November 2006 12:59 Go to next message
Messages: 21
Registered: November 2006
Junior Member

Hi i am trying to count of errors resulting from this query.
The query works fine resulting in returning records with errors

Basically I just want to get a count of those records
but i am not sure how to do this.

This is the query trying to get the count on
select to_char(substr(eventdata,instr(eventdata,'<ns0:CustomerSiteID>')+20,instr(eventdata,'</ns0:CustomerSiteID>') - instr(eventdata,'<ns0:CustomerSiteID>')-20)) Site,
                             to_char(substr(eventdata,instr(eventdata,'<ns0:Name>')+10, instr(eventdata,'</ns0:Name>') - instr(eventdata,'<ns0:Name>')-10)) Name,
                             to_char(substr(eventdata,instr(eventdata,'<ns0:PhoneNumber>')+17, instr(eventdata,'</ns0:PhoneNumber>') - instr(eventdata,'<ns0:PhoneNumber>')-17)) PhoneNumber,
                             to_char(substr(eventdata,instr(eventdata,'<ns0:AddressLine1>')+18, instr(eventdata,'</ns0:AddressLine1>') - instr(eventdata,'<ns0:AddressLine1>')-18)) AddressLine1,                      
                             to_char(substr(eventdata,instr(eventdata,'<ns0:AddressLine2>')+18, instr(eventdata,'</ns0:AddressLine2>') - instr(eventdata,'<ns0:AddressLine2>')-18)) AddressLine2,
                             to_char(substr(eventdata,instr(eventdata,'<ns0:AddressLine3>')+18,instr(eventdata,'</ns0:AddressLine3>') - instr(eventdata,'<ns0:AddressLine3>')-18)) AddressLine3,
                             to_char(substr(eventdata,instr(eventdata,'<ns0:AddressLine4>')+18, instr(eventdata,'</ns0:AddressLine4>') - instr(eventdata,'<ns0:AddressLine4>')-18)) AddressLine4,                 
                             to_char(substr(eventdata,instr(eventdata,'<ns0:POBoxNo>')+13,instr(eventdata,'</ns0:POBoxNo>') - instr(eventdata,'<ns0:POBoxNo>')-13)) POBoxNo,
                             to_char(substr(eventdata,instr(eventdata,'<ns0:City>')+10, instr(eventdata,'</ns0:City>') - instr(eventdata,'<ns0:City>')-10)) City,
                             to_char(substr(eventdata,instr(eventdata,'<ns0:State>')+11, instr(eventdata,'</ns0:State>') - instr(eventdata,'<ns0:State>')-11)) State,
                             to_char(substr(eventdata,instr(eventdata,'<ns0:ZipCode>')+13, instr(eventdata,'</ns0:ZipCode>') - instr(eventdata,'<ns0:ZipCode>')-13)) ZipCode,   
                             to_char(substr(eventdata,instr(eventdata,'<ns0:Country>')+13, instr(eventdata,'</ns0:Country>') - instr(eventdata,'<ns0:Country>')-13)) Country,
                             to_char(e.Created_date,'mm/dd/yy hh24:mi:ss') CreateDt, 
                           (ed.ERRORMSG) ErrorMsg
               from exception e, exception_detail ed
               where e.jmsid = ed.jmsid 
                   and ((upper(e.componentname) like '%SITE%') or  (upper(e.componentname) like 'CUSTMAST%'))
                   and (e.timeoccured) between to_date('11/29/06 08:58','mm/dd/yy hh24:mi:ss')
                   and to_date('11/29/06 09:18','mm/dd/yy hh24:mi:ss')
                   and ed.ERRORMSG NOT LIKE '%Integrity constraint%'
                   and ed.ERRORMSG NOT LIKE '%TNS%'
                   and ed.ERRORMSG NOT LIKE '%cancel%' 
                   and ed.ERRORMSG NOT LIKE '%Reply%'      
                   and ed.ERRORMSG NOT LIKE '%Site Update Service.process/Wait fo%'
                   and ed.ERRORMSG NOT LIKE '%Wait fo%' 
                   and ed.ERRORMSG NOT LIKE '%Site Upsert/Site Create Service.process/Wait fo%'    
               ORDER BY 1, 3

Could someone help me with this please

I tried select count(used the query here) from exception

I am new to this so all your help would be appreciated thanks
Re: How to get a count for this error query [message #206350 is a reply to message #206346] Wed, 29 November 2006 13:19 Go to previous message
Messages: 7880
Registered: March 2000
Senior Member
You just want to add the total number of records returned to your column-list?
then add
count(*) over ()

as a column.
If not, please show us the outcome you expect.
Previous Topic: Ordering/Grouping Problem With 3 Joins
Next Topic: xmlconcat slow
Goto Forum:

Current Time: Tue Oct 25 05:23:44 CDT 2016

Total time taken to generate the page: 0.05999 seconds