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

ruby on rails - Permission denied @ sys_fail2 - (D:/RoR/projects/grp/public/uploads/ -

-
i trying rename upload image permission denied @ sys_fail2 - (d:/ror/projects/grp/public/uploads/gallery/test.jpg, d:/ror/projects/grp/public/uploads/gallery/qinsnvzicc20160509132021.jpg) my code : photo = params[:gallery][:gal_img] name = photo.original_filename directory = "#{rails.root}/public/uploads/gallery/" path = file.join(directory, name) uniq_name = (0...10).map { (65 + rand(26)).chr }.join time_footprint = time.now.to_formatted_s(:number) file.open(path, "wb") |file| new_file_name = uniq_name + time_footprint + file.extname(file) file.write(photo.read) @uniq_path = file.join(directory, new_file_name) file.rename(file, @uniq_path) end i have checked , giving privilege in ruby directory. please let me know why getting permission denied error?
Read more

c++ - nodejs socket.io closes connection before upgrading to websocket -

-
i'm trying write c++ client talk websocket server written using nodejs , socket.io. can't c++ client establish websocket connection; server drops connection right after client requests http connection upgraded websocket. i used tcpdump watch packets going , forth. when use websocket server browser, works great , tcpdump output shows packets going , forth. when try connect server using c++ client, websocket connection never gets established. output tcpdump shows upgrade request being sent server followed server dropping connection. i tried replicating sequence of events using nc . connected server (running on port 8080) , copy-pasted exact set of headers sent browser, captured tcpdump. duplicate of working headers, connection dropped. there no output on server end indicating why connection gets dropped. here headers sent: get /socket.io/?type=player&eio=3&transport=websocket&sid=7mdclht_8ctsd6f4aaac http/1.1 host: localhost:8080 connection: upgrade prag
Read more

java - What is the equivalent of @Value in CDI world? -

-
what way 1 inject property value property placeholder cdi bean? in spring 1 write: @org.springframework.beans.factory.annotation.value("${webservice.user}") private string webserviceuser; what sets webserviceuser field property webservice.user property file/property placeholder. how cdi? i've tried find answer, couldn't find equivalent. however, people write, can use cdi spring substitute on application servers, , use case basic, surely there must easy way, unfortunately i've failed find it. cdi specification dependecy injection , context doesn't have such configuration things out of box. provides powerful extension mechanism allows third party projects add new portable features (i.e works cdi implementation , not tied server). important project providing cdi extensions apache deltaspike , news, provides need. so need add deltaspike-core in project. if use maven, need add dependencies pom.xml <dependency> <group
Read more