Hi Formspider team First of all, happy new year for all. I wanna create a datasource using pivot statement and I am facing a problem in syntax, but it works in PL/SQL Developer or SQL Developer. The query is: select decode(apl.name,null,'Grand Total',lpad(' ', (level - 1) * 5, ' ') || apl.name) as nome, summ.*, summ."'Jan'" + summ."'Feb'" + summ."'Mar'" + summ."'Apr'" + summ."'May'" + summ."'Jun'" + summ."'Jul'" + summ."'Aug'" + summ."'Sep'" + summ."'Oct'" + summ."'Nov'" + summ."'Dec'" as total, decode(apl.name,null,'LightGrey','White') as background, decode(apl.name,null,'LightGrey','LightGrey') as backtotal from APPLICATIONS_T apl, (select * from ( -- Tratamento de PKEs select dat.id , dat.parent_id , decode(substr(dat.competence,5,2),'01','Jan','02','Feb','03','Mar','04','Apr' ,'05','May','06','Jun','07','Jul','08','Aug' ,'09','Sep','10','Oct','11','Nov','12','Dec') as mes , round(dat.value,0) as quantity from kpi_data_t dat where dat.ticket_type = decode(:id_type,'PKE',util.find_general_by_name('PKE') ,'ALL',util.find_general_by_name('PKE') ,'CM',util.find_general_by_name('PKE')) and dat.type in ('A','G','P') and competence between trim(:competence) || '01' and trim(:competence) || '12' and dat.kpi_id = ( case when :id_type in ('PKE','ALL','CM') and :id_status = 'Opened' then util.find_kpi_by_name('Quantity of PKEs opened in month') when :id_type in ('PKE','ALL','CM') and :id_status = 'Delivered' then util.find_kpi_by_name('Quantity of PKEs closed in month') end)) pivot ( sum(quantity) for (mes) in ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))) summ where summ.id = apl.application_id(+) start with summ.parent_id is null connect by prior apl.application_id = summ.parent_id order siblings by apl.name nulls last Any idea how can I fix it? Regards Ricardo |
Hi Ricardo, I didnt have the time to test this but maybe if you make a view from the query and use the view in the datasource definition it might help? |
Hi Yalim, How are you? The issue is the name of column. I put here just a part of query, I have 3 more blocks similar to "Tratamento de PKEs". I dont know if I can a view for this. Regards Ricardo What's the problem with the column names? SQL developer and PL/SQL developer put aliases automatically. Formspider doesn't and shouldn't. This may force you to give names to several columns which may be ambigously defined.
(10 Jan '14, 07:30)
Yalim Gerger ♦♦
|
Hi Yalim, I fixed the issue. I had to change the name of column to alias - see it below: select decode(apl.name,null,'Grand Total',lpad(' ', (level - 1) * 5, ' ') || apl.name) as nome, summ."'Jan'" as "Jan", summ."'Feb'" as "Feb", summ."'Mar'" as "Mar", summ."'Apr'" as "Apr", summ."'May'" as "May", summ."'Jun'" as "Jun", summ."'Jul'" as "Jul", summ."'Aug'" as "Aug", summ."'Sep'" as "Sep", summ."'Oct'" as "Oct", summ."'Nov'" as "Nov", summ."'Dec'" as "Dec", summ."'Jan'" + summ."'Feb'" + summ."'Mar'" + summ."'Apr'" + summ."'May'" + summ."'Jun'" + summ."'Jul'" + summ."'Aug'" + summ."'Sep'" + summ."'Oct'" + summ."'Nov'" + summ."'Dec'" as total, decode(apl.name,null,'LightGrey','White') as background, decode(apl.name,null,'LightGrey','LightGrey') as backtotal from APPLICATIONS_T apl, ... When you use pivot statement, the name of column have " ' " in the beginning and ending of name, such as 'Jan', 'Feb'.... I was using summ.* to all columns and it was causing the error. When I changed the name ignoring " ' ", it worked. Regards Ricardo |