sql - Oracle REGEXP_LIKE match up to a period (decimal point) or find next sequence in numbers such as Dewey Decimal -

-

i have data such

    oc_id                   parent_oc_id     4.0.1                   4.0              4.0.1.1                 4.0.1       4.0.1.1.1               4.0.1.1     4.0.1.1.2               4.0.1.1     4.0.1.1.3               4.0.1.1     4.0.1.2                 4.0.1     4.0.1.3                 4.0.1     4.0.1.4                 4.0.1     4.0.1.5                 4.0.1     4.0.1.5.1               4.0.1.5     4.0.1.5.2               4.0.1.5     4.0.1.5.3               4.0.1.5     4.0.1.5.4               4.0.1.5     4.0.2                   4.0     4.0.2.1                 4.0.2     4.0.2.2                 4.0.2     4.0.2.2.1               4.0.2.2     4.0.12                  4.2 <--------data anomally!!!

i trying determine next number in hierarchy. example if start parent_oc_id = 4.0 need know next available sibling number (oc_id) within 4.0.? hierarchy 4.0.13

originally using , worked.

 select max(to_number (substr (eb_ocs.oc_id, instr (eb_ocs.oc_id, '.', -1)+1)))+1  eb_ocs eb_ocs.parent_oc_id = '4.0'

but somehow parent siblings got out of sync , where clause missing 4.0.12 sibling , max number worng (this risk of managing keys manually)

so based on data anomaly above 4.0 should return 4.0.13 4.0.3

i'm taking steps final query , trying search using regexp starting string , including doesn't have period (.)

something

select eb_ocs.oc_id eb_ocs regexp_like (eb_ocs.oc_id, '4.0.(.*)^.')

but negative isn't working.

ultimately want next sequence under particular level

4.0 should give 4.0.13 4 should give 4.1 4.0.1.1 should give 4.0.1.1.4

if column parent_ocs_id unreliable, simple ignore , calculate correct child key. rest original approach

 fix_parent  (select oc_id,          substr (eb_ocs.oc_id, 1, instr (eb_ocs.oc_id, '.', -1)-1) parent_oc_id,          to_number (substr (eb_ocs.oc_id, instr (eb_ocs.oc_id, '.', -1)+1)) child_number     tst eb_ocs)  select        parent_oc_id,  max(child_number) +1 next_child_number  fix_parent  parent_oc_id in ('4.0.1.1','4.0','4')  group parent_oc_id  order parent_oc_id;

.

 parent_oc_id next_child_number  ------------ -----------------  4.0                         13   4.0.1.1                      4

to result parent '4' add line

 insert tst values ('4.0','4');

Comments

Post a Comment

Popular posts from this blog

javascript - Chart.js (Radar Chart) different scaleLineColor for each scaleLine -

-
Image
i try , create radar chart using chart.js has various colours each scaleline, or coloured between scalelines. wondering if possible? from: to: i have working graph, though there doesn't seem method change individual scale lines. kind regards leigh you can extend radar chart type this, so chart.types.radar.extend({ name: "radaralt", initialize: function (data) { chart.types.radar.prototype.initialize.apply(this, arguments); var originalscaledraw = this.scale.draw; var ctx = this.chart.ctx; this.scale.draw = function () { var linewidth = this.linewidth; // bypasses line drawing in originalscaledraw this.linewidth = linewidth; originalscaledraw.apply(this, arguments); ctx.linewidth = this.linewidth; var scale = this; // draw chart.helpers.each(scale.ylabels, function (label, index) { // color of ...
Read more

apache - Error with PHP mail(): Multiple or malformed newlines found in additional_header -

-
suddenly have started receiving above error without changes having been made script. host 1and1 (i know...) the script still works fine on different server, , suspicion there must have been server config change has lead this, although hosts plead ignorance. there's no information on above error @ in google can find - have ideas? server running apache if helps. had similar problem. came out of blue. no php code changed. what changed: php upgraded 5.5.25-1 5.5.26. a security risk in php mail() function has been fixed , newlines in additional_headers allowed no more. because newlines mean: starts email message (and surely don't want inject newlines through headers followed evil message). what have worked fine, e.g. having newlines after headers or passing whole message additional_headers , function no more. solution : sanitize headers. no multiple newlines in additional_headers argument. these count "multiple or malformed newlines":...
Read more

java - Android – MapFragment overlay button shadow, just like MyLocation button -

-
Image
i'm struggling add shadow custom overlay button on google map. goal make custom button google's mylocation button. here's how app looks now: layout.xml <relativelayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" xmlns:map="http://schemas.android.com/apk/res-auto" android:layout_height="match_parent" android:layout_width="match_parent" style="@style/apptheme"> <fragment android:layout_width="match_parent" android:layout_height="match_parent" android:id="@+id/map" tools:context=".mapsactivity" android:name="com.google.android.gms.maps.supportmapfragment" android:layout_alignparentend="true" android:layout_alignparentstart="true" android:layout_alignparentbottom="true" ...
Read more