oracle - SQL Custom selection -



oracle - SQL Custom selection -

i want write sql gives next result. possible? tried union works 1 record.

col1 | col2 | col3 | col4 --------------------------- | 10 | | | 2 | val1 | val2 | 5 | val3 | val4 | 3 | val5 | val6 b | 11 | | | 3 | val7 | val8 | 5 | val9 | val10 | 3 | val0 | val12

here query :

select a.val1, null val2, a.val3 table1 a.val1 = 'a' union select null val1, b.val2, b.val3 table2 b b.val1 = 'a' ;

maybe create question more clear.

imagine if run query:

select a.val1, null val2, a.val3 table1 union select null val1, b.val2, b.val3 table2 b ;

i want result set figure above.

col1 | col2 | col3 | col4 --------------------------- | 10 | | | 2 | val1 | val2 | 5 | val3 | val4 | 3 | val5 | val6 b | 11 | | | 3 | val7 | val8 | 5 | val9 | val10 | 3 | val0 | val12

either rollup or regular grouping can this, still haven't accounted col2. did come from, in sql snippets, don't have @ all.

sql> create table yourtab(col1, col2, col3, col4) 2 3 select 'a', 2, 'val1', 'val2' dual 4 union select 'a', 5, 'val3', 'val4' dual 5 union select 'a', 3, 'val5', 'val6' dual 6 union select 'b', 3, 'val7', 'val8' dual 7 union select 'b', 5, 'val9', 'val10' dual 8 union select 'b', 3, 'val11', 'val12' dual; table created. sql> sql> select col1, sum(col2) col2, col3, col4 2 yourtab 3 grouping col1, rollup(col3, col4) 4 having (grouping_id(col3), grouping_id(col4)) in ((0,0),(1,1)) 5 order col1, grouping_id(col3) desc; c col2 col3 col4 - ---------- ----- ----- 10 5 val3 val4 3 val5 val6 2 val1 val2 b 11 b 3 val7 val8 b 5 val9 val10 b 3 val11 val12 8 rows selected. sql> sql> select col1, col2, col3, col4 2 (select col1, col2, col3, col4, 2 grp 3 yourtab 4 union 5 select col1, sum(col2), null, null, 1 grp 6 yourtab 7 grouping col1) 8 order col1, grp; c col2 col3 col4 - ---------- ----- ----- 10 5 val3 val4 3 val5 val6 2 val1 val2 b 11 b 3 val11 val12 b 5 val9 val10 b 3 val7 val8 8 rows selected.

sql oracle

Comments

Post a Comment

Popular posts from this blog

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer -

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer - when trying request rest service @ time of response getting exception.. feb 21, 2013 2:34:49 pm com.sun.jersey.spi.container.containerresponse mapmappablecontainerexception severe: exception contained within mappablecontainerexception not mapped response, re-throwing http container java.lang.noclassdeffounderror: not initialize class net.sf.cglib.proxy.enhancer @ org.hibernate.proxy.pojo.cglib.cgliblazyinitializer.getproxyfactory(cgliblazyinitializer.java:117) @ org.hibernate.proxy.pojo.cglib.cglibproxyfactory.postinstantiate(cglibproxyfactory.java:43) @ org.hibernate.tuple.entity.pojoentitytuplizer.buildproxyfactory(pojoentitytuplizer.java:162) @ org.hibernate.tuple.entity.abstractentitytuplizer.<init>(abstractentitytuplizer.java:135) @ org.hibernate.tuple.entity.pojoentitytuplizer.<init>(pojoentitytuplizer.java:55) @ org.hibernate.tuple.e...
Read more

Accessing MATLAB's unicode strings from C -

Accessing MATLAB's unicode strings from C - how can access underlying unicode info of matlab strings through matlab engine or mex c interfaces? here's example. let's set unicode characters in utf-8 encoded file test.txt, read as fid=fopen('test.txt','r','l','utf-8'); s=fscanf(fid, '%s') in matlab. now if first feature('defaultcharacterset', 'utf-8') , c engevalstring(ep, "s") , output text file utf-8. proves matlab stores unicode internally. if mxarraytostring(enggetvariable(ep, "s")) , unicode2native(s, 'latin-1') give me in matlab: non-latin-1 characters replaced character code 26. need getting access underlying unicode info c string in unicode format (utf-8, utf-16, etc.), , preserving non-latin-1 characters. is possible? my platform os x, matlab r2012b. addendum: documentation explicitly states "[mxarraytostring()] supports multibyte encoded characters...
Read more

javascript - mongodb won't find my schema method in nested container -

javascript - mongodb won't find my schema method in nested container - i trying access method of schema stored within mixed container. here situation : have cases model can many different things, have schema each of these things stored in "casecontent" mixed property. var caseschema = mongoose.schema({ casecontent : {}, object : {type:string, default : "null"}, collision : {type : boolean, default : false} }); the casecontent property filled model of 1 of schemas, 1 exemple : var treeschema = new mongoose.schema({ applecount : {type : number, default : 3} }); treeschema.methods.dostuff = function (data) { console.log('hey, listen'); homecoming true; }; then, want utilize method of schema original container : caseschema.methods.dostuff = function (data) { if (this.casecontent.dostuff !== undefined) { this.casecontent.dostuff(); console.log('it worked'); } else { ...
Read more