|
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 |
