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

asked 08 Jan '14, 11:19

Ricardo's gravatar image

Ricardo
11120
accept rate: 0%


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?

link

answered 10 Jan '14, 01:29

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
1.8k5
accept rate: 15%

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

link

answered 10 Jan '14, 07:04

Ricardo's gravatar image

Ricardo
11120
accept rate: 0%

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

link

answered 10 Jan '14, 12:15

Ricardo's gravatar image

Ricardo
11120
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×64
×1

Asked: 08 Jan '14, 11:19

Seen: 2,134 times

Last updated: 10 Jan '14, 12:15


© Copyright Gerger 2017