|
|
Re: How can I create the dynamic column for the report [message #229199 is a reply to message #229062] |
Thu, 05 April 2007 22:34 |
zhan200012
Messages: 11 Registered: December 2006 Location: China
|
Junior Member |
|
|
For example.
I have three column in the table as A,B,C!
Now I'm using a parameter as P_A that have contacted with the A column!
When I input the "a" to P_A is shown the A column and hiden other column in the report,or "b" is shown the B column etc.
The result must display in the report.
Do you understand?
I from Asia! So my English is poor!
|
|
|
|
|
Re: How can I create the dynamic column for the report [message #229826 is a reply to message #229480] |
Tue, 10 April 2007 02:30 |
|
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
You could do that using such an example: if parameter value = 'a', return department number; if it is 'b', return department name.
SQL> select
2 decode('&p_a', 'a', to_char(deptno),
3 'b', dname
4 ) result
5 from dept;
Enter value for p_a: a
old 2: decode('&p_a', 'a', to_char(deptno),
new 2: decode('a', 'a', to_char(deptno),
RESULT
----------------------------------------
10
20
30
40
SQL> /
Enter value for p_a: b
old 2: decode('&p_a', 'a', to_char(deptno),
new 2: decode('b', 'a', to_char(deptno),
RESULT
----------------------------------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
SQL>
As I don't know what kind of data you are going to return (I mean column A, B and C datatypes), I can only say that you'll have to take care about the fact that the result of the DECODE function has a datatype of the first column you select. In my example, it is DEPTNO - as it is a NUMBER, DECODE expects that the rest of the columns are also numbers. But, as DNAME is a VARCHAR2 column, query would return an INVALID NUMBER error. That's why I used TO_CHAR function with the 'deptno' column - to unify the result datatypes.
Basically, such an approach should work correctly.
|
|
|
|
|
|
Re: How can I create the dynamic column for the report [message #230415 is a reply to message #229062] |
Wed, 11 April 2007 21:27 |
zhan200012
Messages: 11 Registered: December 2006 Location: China
|
Junior Member |
|
|
Oh Sorry!
You misunderstood my point!
I said my English was poor!
My problem is I want to use the PL/SQL to alter the field's source in the trigger in the report.The field's source that changed the A or B or C column is made a choice base on parameter P_A!
A,B,C column is the same datatype as VARCHAR2!
It must use the (If...else...end/Decode/Case) structure!
Do you understand?
|
|
|
|
|
|
|