sql - Oracle generate range between 2 columns of data -

-

good day all,

i have table structured below

f_number    act_date    amount  range_start     range_end ------------------------------------------------------------- t18564      20140702        10  700102180211    700102195210 t18564      20140702        10  700104371331    700104376330 t18564      20140702        20  700250037215    700250077214 t18564      20140702        30  600311039928    600311044927

how generate numbers in between ranges data displayed as,

f_number    act_date    amount  item_id -------------------------------------------- t18564      20140702        10  700102180211                                     ... t18564      20140702        10  700102195210 t18564      20140702        10  700104371331                                 ... t18564      20140702        10  700104376330

and forth. tried using,

select f_number, act_date, amount, to_number(range_start - 1) + level item_id   table connect level <= (to_number(range_end) - to_number(range_start)) + 1

but works ok first range goes wrong after.

*to_number used because ranges stored varchar2

thanks assistance.

i think in order use connect by effectively, need identify each row uniquely. don't see kind of primary key or unique identifier in data above (and storing numbers , dates in varchar2 columns? bad idea). might try this:

with t1 (     select 't18564' f_number, '20140702' act_date          , 10 amount, '700102180211' range_start          , '700102195210' range_end       dual      union     select 't18564', '20140702', 10, '700104371331', '700104376330'       dual      union     select 't18564', '20140702', 20, '700250037215', '700250077214'       dual      union     select 't18564', '20140702', 30, '600311039928', '600311044927'       dual ) select f_number, act_date, amount, range_start - 1 + level   (     select f_number, act_date, amount          , to_number(range_start) range_start          , to_number(range_end) range_end          , row_number() on ( order null ) rn       t1 ) connect range_start - 1 + level <= range_end       , prior rn = rn       , prior dbms_random.value not null;

note i'm using row_number() generate unique row identifier. use sys_guid() if inclined (the order should not matter in case).

i ran above in oracle 10g , returned correct number of rows (in case, 65000 -- sum of range_end - range_start + 1).


Comments

Post a Comment

Popular posts from this blog

How to run C# code using mono without Xamarin in Android? -

-
i want create program uses mono runtime run c# code (binary not application). i copied libmono.so unity app , i'm write code using documentation: http://www.mono-project.com/docs/advanced/embedding/ and it's working have problems references can access classes mscorlib.dll , have problems system.reflection.assembly class throwing exception, when print console see: system.missingmethodexception: method not found: 'system.reflection.assembly.op_inequality' can fix it? can use mono runtime in c binary on android? ps. i'm using android tests. edit: found solution loading libraries, need set environment variable mono_path libraries path. but have still problems assembly class. the system.missingmethodexception error have seems .net profile mismatch error, 1 explained here . can use mono runtime in c binary on android? yes, using ndk guess. there alternatives can use instead of rolling own, such xmadevlab .
Read more

html - grunt SVG to webfont -

-
i'm trying generate webfont svgs using grunt-webfont . the issue need task valid on windows , linux machines. in order achieve need use node engines doesn’t work svg files . i know if there way convert svgs proper svg format supported grunt plugin or alternatively there better grunt plugin/s achieve this i have task able take svgs (created in illustrator) , output webfont files (.eot,.svg,.svg,...) , css file (like icomoon export) thanks. perhaps artboard small. recommend checking have 512pt x 512pt artboard each icon. stumbled upon gem ( https://www.fourdigits.nl/blog/using-a-webfont-to-display-icons ) when grunt-webfont documentation left me alone in forest.
Read more

c# - SharpSsh Command Execution -

-
Image
i trying create ssh client using c#. using tamir.sharpssh library. having issues sending command , getting appropriate response server. the way have been testing sending on ls command, followed cd.. command, followed ls command again -- see whether or not cd.. command being executed properly. however, there no difference between first ls command , second ls command. not entirely sure doing wrong. perhaps using wrong ssh type. have provided code , output getting. have added output expect. using tamir.sharpssh; namespace sshnetexample { class program { static void main(string[] args) { sshexec ssh = null; try { console.write("-connecting..."); ssh = new sshexec(host, username, password); ssh.connect(); console.writeline("ok ({0}/{1})", ssh.cipher, ssh.mac); console.writeline("server version={0}, client versio...
Read more