query related Pivot function of oracle [message #427924] |
Mon, 26 October 2009 07:01  |
sohan
Messages: 11 Registered: October 2009 Location: India
|
Junior Member |
|
|
how to show matrix of 2 dynamic columns?
Using pivot function it has achieved, but column header do not support more than 30 characters.
eg.
Name xxxyyyyzzzerretqweasdsf123345wertuuu
John checkbox
Mary checkbox
Bill checkbox
The result achieved,
Name xxxyyyyzzzerretqweasdsf123345w
John checkbox
Mary checkbox
Bill checkbox
The result to be achieved,
Name xxxyyyyzzzerretqweasdsf123345wertuuu
John checkbox
Mary checkbox
Bill checkbox
|
|
|
|
Re: query related Pivot function of oracle [message #427932 is a reply to message #427924] |
Mon, 26 October 2009 08:02   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> col dummy format a61 heading "this is a column header that contains more than 30 characters"
SQL> select dummy from dual;
this is a column header that contains more than 30 characters
-------------------------------------------------------------
X
1 row selected.
Regards
Michel
[Updated on: Mon, 26 October 2009 08:02] Report message to a moderator
|
|
|
|
|
|
Re: query related Pivot function of oracle [message #428131 is a reply to message #428092] |
Tue, 27 October 2009 05:48   |
sohan
Messages: 11 Registered: October 2009 Location: India
|
Junior Member |
|
|
Thanks for your immediate reply.
I know that we can format column heading as per our requirement.
But in pivot for clause i am passing dynamic string.
The pivot query is returned by the function and that function is called in report of Oracle apex.
declare
v_sql clob;
begin
select plan_service_matrix.pivot_func(1,2) into v_sql from dual;
return v_sql;
end;
This is the code written in report source v_sql contains pivot query .
e.g
Within pivot in clause there is a string ('qwertyuioplkjhgfdsazxcvbnmlkiutr','trial-1',
'qwertyuioplkjhgfdsazxcvbnmlkiuse-2')
Then first and second parameters are same till 30 characters so my report is giving error of "column ambiguously defined".
If parameters are not same then it cuts the heading after 30 characters.
So how to handle this problem?
All the columns are going to be dynamically displayed at runtime.
So how and where to write a command which will format the dynamic column headings as per my requirement.
Thanks
[Updated on: Tue, 27 October 2009 06:13] Report message to a moderator
|
|
|
|
Re: query related Pivot function of oracle [message #428191 is a reply to message #428164] |
Tue, 27 October 2009 08:03   |
sohan
Messages: 11 Registered: October 2009 Location: India
|
Junior Member |
|
|
Thank You so much that you are spending your precious time in solving my problem.Thanx.
Suppose I have 2 tables
1. Name_Info
Name_pk number;
Add_fk number;
Name varchar2(200)
2. Address_Info
Add_pk number;
Add_info varchar2(32767)
Data in Address_Info
Add_pk Add_Info
1 qwertyuioplkjhgfdsazxcvbnmloiuyhgt
2 trial
3 qwertyuioplkjhgfdsazxcvbnmloivxcxzzasa
Data in Name_Info
Name_pk Add_fk Name
1 1 John
2 2 Mary
3 3 Bill
Now I want a matrix with name as row and addresses as column
as described above.
Using pivot query i have achieved it.
Now in Oracle apex in the report region source i am writing the following code.
declare
v_sql clob;
v_add clob;
begin
select concatenate_list_with_alias(CURSOR(select add_info from address_info)) into v_add from dual;
v_sql:='select * from (select name,add_info,add_pk from name_info,address_info order by name) pivot (max(replace(add_info,add_info,apex_item.checkbox(10,add_pk))) for add_info in ('||v_add||'))';
return v_sql;
end;
concatenate_list_with_alias :- Gives me comma seperated string as in my example it gives
'qwertyuioplkjhgfdsazxcvbnmloiuyhgt','trial','qwertyuioplkjhgfdsazxcvbnmloivxcxzzasa'
and it is stored in v_add variable.
But as you see that first and third string are same till 30 characters so it gives me error "column ambiguously defined".
If I remove third string and provide the static string in the v_sql query then,
Name qwertyuioplkjhgfdsazxcvbnmloi trial
Bill checkbox checkbox
John checkbox checkbox
Mary checkbox checkbox
As you can view that it has truncated the second column name till 30 characters.
How can i solve this problem.
I am not able to understand where to write column format command?
Thanks again..
[Updated on: Tue, 27 October 2009 08:26] Report message to a moderator
|
|
|
|