regex - Oracle SQL regexp sum within repeating sequence grouped by -

-

first time here, hope can help

what's oracle sql select stmt xml below stored in oracle clob field following returned (there many 96 intvcoll blocks per day).

basically numeric values in intvcoll blocks between 2nd , 3rd commas need summed , grouped date before first comma , varchar after 3rd comma.

i'm guessing regexp_substr / can't quite there. first record sum of 1st , 2nd intvcoll blocks second record sum of 3rd intvcoll block third record sum of 4th , 5th 

meterchannelid           date         sum     quality   count_of_records 6103044759-40011200-q1   14/03/2016   1,387           2    6103044759-40011200-q1   14/03/2016     694   s         1  6103044759-40011200-q1   15/03/2016   1,433           2    <uploadregdata>     <intervaldatablock>         <setdatetime>16/03/2016-19:30:01</setdatetime>         <intervalminute>15</intervalminute>         <meterchannelid>6103044759-40011200-q1</meterchannelid>         <intvcoll><intvdata>14/03/2016,1,700,a</intvdata></intvcoll>         <intvcoll><intvdata>14/03/2016,2,687,a</intvdata></intvcoll>         <intvcoll><intvdata>14/03/2016,3,694,s</intvdata></intvcoll>         <intvcoll><intvdata>15/03/2016,4,724,a</intvdata></intvcoll>         <intvcoll><intvdata>15/03/2016,5,709,a</intvdata></intvcoll>     </intervaldatablock> </uploadregdata> 

select meterchannelid,        "date",        sum( value ) "sum",        quality,        count(1) count_of_records   (   select meterchannelid,          to_date( substr( data, 1, 10 ), 'dd/mm/yyyy' ) "date",          to_number( substr(                       data,                       instr( data, ',', 1, 2 ),                       length( data ) - instr( data, ',', 1, 2 ) - 2                   ) ) value,          substr( data, -1 ) quality     (     select extractvalue( xml, '/uploadregdata/intervaldatablock/meterchannelid' )              meterchannelid,            extractvalue( d.column_value, '/intvdata' ) data       ( select xmltype( column_name ) xml table_name ) x,            table(              xmlsequence(                extract(                  x.xml,                  '/uploadregdata/intervaldatablock/intvcol1/intvdata'                )              )            ) d   ) ) group meterchannelid, "date", quality order meterchannelid, "date", quality;

Comments

Post a Comment

Popular posts from this blog

javascript - Laravel datatable invalid JSON response -

-
everything work fine on code below fetch user logs: js: <script> $(document).ready(function () { var table =$('#systemlogs').datatable({ responsive: true, processing: true, serverside: true, "language": { "url": "/datatables/media/plug-in/persian.json" }, ajax: '{!! url('/admin/systemlogs/data/systemlogsdatatable') !!}', columns: [ { data: 'name', name: 'name' }, { data: 'message_text', name: 'message_text' }, { data: 'remote_addr', name: 'remote_addr' }, { data: 'log_created_at', name: 'management_logs.created_at' }, { data: 'log_updated_at', name: 'management_logs.updated_at' }, ] }); }); </script> routes: route::get('/systemlogs/data/systemlogsdatatable','systemlogscontrolle...
Read more

java - Exception in thread "main" org.springframework.context.ApplicationContextException: Unable to start embedded container; -

-
i getting error when run spring application: exception in thread "main" org.springframework.context.applicationcontextexception: unable start embedded container; nested exception org.springframework.context.applicationcontextexception: unable start embeddedwebapplicationcontext due missing embeddedservletcontainerfactory bean. @ org.springframework.boot.context.embedded.embeddedwebapplicationcontext.onrefresh(embeddedwebapplicationcontext.java:124) @ org.springframework.context.support.abstractapplicationcontext.refresh(abstractapplicationcontext.java:476) @ org.springframework.boot.context.embedded.embeddedwebapplicationcontext.refresh(embeddedwebapplicationcontext.java:109) @ org.springframework.boot.springapplication.refresh(springapplication.java:691) @ org.springframework.boot.springapplication.run(springapplication.java:320) @ org.springframework.boot.springapplication.run(springapplication.java:952) @ org.springframework.boot.springa...
Read more

sql server 2008 - My Sql Code Get An Error Of Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '8:45 AM' to data type int -

-
msg 245, level 16, state 1, line 1 conversion failed when converting varchar value '8:45 am' data type int. script declare @specimenid bigint = 0, @script varchar(max); declare @starttime int = (select convert(int,substring(start_time,0,charindex(':', start_time)) - 12) appt appt._id=112601) --select @starttime set @script = 'select distinct ptlast_name + '', '' + pt.first_name patient_name, dbo.formatdate(pt.dob,''mm/dd/yyyy'') dob, (convert(varchar, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end)) + ''/'' + convert(varchar, (datediff(m, dateadd(yy, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end), d...
Read more