Report Question 1

[sanjaysaxena]

Hi all,
The application on which I'm working on, has some typical database design.
Since the html forms may have more than 1200 fields, and also the names of the fields will be changing every quarter of the year, also number of fields may vary every quarter, we do not have regular database design. Our database then is a vertical database, in the sense, every field and its value is a new record with important column names as field_name, field_value and form_record_id. I'm working on the report part and not able to understand as how will I generate reports. Since I'm not a hardcore oracle developer I need help on this issue as what can be done.. Like we create tables/view on the fly or write queries for the existing tables ??
Any clue will be much appreciated.
Thanks a lot in advance.

_Sanjay

 

[nagornyi]

You may use lexical parameters in the select statements of your report queries. In the "After Parameter Form" trigger you will have to read the current names of the fields and assign them to lexical parameters.

 

[sanjaysaxena]

Hi nagornyi,
Thank you very much for answering my question. I have one more question...
Suppose I have a table something like below...

NAME VALUE SOME_ID
First John 101
Last Smith 101
First Sanjay 102
Last Saxena 102
First Andy 103
Last Robert 103

Now I want to create a view which will look like..
SOME_ID FIRST LAST
101 John Smith
102 Sanjay Saxena
103 Andy Robert

My question is, if it is possible to create a view like above.

Thanks.
Sanjay

 

[nagornyi]

select 
  t.some_id
, (select value from table where name=[b]'First'[/b] and some_id=t.some_id) first
, (select value from table where name=[b]'Last'[/b] and some_id=t.some_id) last
from table t

 

[sanjaysaxena]

Thanks a lot. I'll try this.