To_number(nvl(dlookup_bind_one(col, 'temp', 'id=:a', temp.id),0)) orig, Varchar2, vTable in varchar2, vWhere in varchar2 default NULL,Įxecute immediate vQuery into vVar using vBind Insert into temp_lkup select 2, 'b', 'b*3' from dualĬREATE OR REPLACE FUNCTION DLOOKUP_BIND_ONE (vField in Insert into temp_lkup select 2, 'a', 'a+6' from dual Insert into temp_lkup select 1, 'b', 'b*2' from dual Insert into temp_lkup select 1, 'a', 'a' from dual Per department, per column this is the calculation that each one wants.Ĭreate table temp (id integer, a integer, b integer)Ĭreate table temp_lkup (id integer, col varchar2(16), calc varchar2(16)) Please note that at any time a department may appear or disappear and that each department calculates their values differently.
Pretend that I have given two departments id's 1 and 2 and that the values of a and b are important to a calculation they need. See below for an example of un-pivoting a table that seems more maintainable to me than using static views if your view needs to change frequently. At minimum I thought my small function might be useful for other readers.
PIVOT IN ORACLE 10G HOW TO
I simply wanted to share the function with the readers and see if they had any comments and to see if you might show me how to get rid of the dlookup function by hardcoding the below example into a simple view that can un-pivot the data *and* calculate values per row per deptment. Because of this I have resorted to writting my own domain lookup function that uses bind variables and a lookup table that allows my calculation model to vary by department.Īs I so ineptly responded before, rather than hard-coding a view I wrote a small domain lookup function that allows me flexibility (at the expense of compute time). I have read your examples in your excellent book on Analytic Functions (way cool) but they deal with pivoting a table (rows to columns) not columns to rows. Hard coding these views for a rather fluid calculation system and then to keep versions per department wouldn't be feasible as I would have to have a different version of the view per department and our department structures and calculation models change monthly. These calculations may also vary by department. To be brief I frequently am asked to produce un-pivots of data *and* calculate values. You are correct, un-pivoting a table in the above case doesn't require a cartesian join. With the example above though using an array, how would you get it to actually look like a table? Is there a view you can wrap around the multiset output to make it look like an actual table? This works pretty well, although the cartesian join is very expensive. VQuery := 'select '||vField||' from '||vTable VTable in varchar2, vWhere in varchar2 default NULL) returnĮxecute immediate 'alter session set cursor_sharing=force' It forces auto-binding also.ĬREATE OR REPLACE FUNCTION DLOOKUP (vField in varchar2, I create a lookup table that lists the field names to be retreived and use a cartesian join to do my lookups and then exclude all of the null values. Because of this I wrote a small domain lookup function that uses auto-binding and looks up the column values. I have a simlar problem to the above of pivoting tables where I don't know the domain of the pivot columns (or they fluctuate). Type column x format a40 select deptno,Ģ cast ( multiset( select job, count(*) cntĢ0 MYARRAYTYPE(MYSCALARTYPE('ANALYST', 2), Type create or replace type myArrayType as table of myScalarType We can use object types to pivot create or replace type myScalarType as object What if you wanted JOB to be the column instead and leave deptno in the rows? You might not know of all of the possible jobs, or there might be 100's of them. That works if you know the domain of deptno's. That has pivoted the CNT column by deptno across job. It would look like select job,Ģ max( decode( deptno, 10, cnt, null ) ) dept_10,ģ max( decode( deptno, 20, cnt, null ) ) dept_20,Ĥ max( decode( deptno, 30, cnt, null ) ) dept_30,ĥ max( decode( deptno, 40, cnt, null ) ) dept_40Ħ from ( select job, deptno, count(*) cnt We can make columns dept_10, dept_20, dept_30, dept_40 that have the values that are currently in the count column.
Say you have the following set of select job, deptno, count(*)Īnd you would like to make DEPTNO be a column.
0 kommentar(er)
