| How to Pass a Select statement to a Report at Runtime [message #122523] |
Tue, 07 June 2005 00:24  |
rockysh
Messages: 4
Registered: June 2005
|
Junior Member |
|
|
Hello,
I would like to know is it possible to pass a dynamically created select statement to replace the query of the report ?. I tried DATA_PARAMETER in RUN_PRODUCT, but this requires the column/data match with the report query and the record_group query.
I am trying to build a form where the user will be able to select the columns he wants from the displayed data and print using reports.
All help will be greately appreciated.
Cheers
|
|
|
|
|
|
| Re: How to Pass a Select statement to a Report at Runtime [message #123040 is a reply to message #122616] |
Thu, 09 June 2005 11:49  |
rockysh
Messages: 4
Registered: June 2005
|
Junior Member |
|
|
Hi Steve,
Thanks for the reply. I use Forms 6i/Forms developer 6i. i use the host the forms on an application server. I can pass The clauses of Select From and so on using text_paramerter and lexical references. But what i want to do is to pass the entire select statement, for example, "Select Ename From EMP" as a parameter. How do i do this ?.
If i can do this then i can allow the user to select cny displayed field he wants to print and no a pre-defined set of fields.
For known cases, where the report needs to display a common set of columns, with the same datatype, lexical references or a ref cursor works fine.
Any suggestions ?
|
|
|
|
| Re: How to Pass a Select statement to a Report at Runtime [message #123058 is a reply to message #122523] |
Thu, 09 June 2005 13:20 |
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
|
Senior Member |
|
|
Rocky -
You can use a lexical parameter to pass the entire SELECT statement. I would set it up in this fashion:
Create a parameter called SQL_STATEMENT in your front_end (which is... Oracle Forms?). What you can do is when your user runs the process that passes the values, you can set the the SQL_STATEMENT parameter equal to 'SELECT :BLOCK.ITEM FROM :BLOCK.ITEM WHERE...' etc.
the :BLOCK.ITEM references are syntax from Oracle Forms referring to text items, LOV's, etc that the user enters or selects a value from. If you only want the user to choose what columns to select, then the FROM and WHERE and GROUP by clauses of the statement are static and you can pre-define them like this:
SELECT
:BLOCK.ITEM,
:BLOCK.ITEM1,
:BLOCK.ITEM2
FROM
STATIC_TABLE
WHERE
STATIC_JOINS
Then in reports, define a user parameter SQL_STATEMENT. For your sql statement it would be simply
&SQL_STATEMENT
HTH,
Steve
|
|
|
|
| Re: How to Pass a Select statement to a Report at Runtime [message #123059 is a reply to message #123040] |
Thu, 09 June 2005 13:23 |
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
|
Senior Member |
|
|
| rockysh wrote on Thu, 09 June 2005 12:49 |
For known cases, where the report needs to display a common set of columns, with the same datatype, lexical references or a ref cursor works fine.
|
The datatype should not factor in for the columns. You are passing a string to reports (the entire select is a string or varchar2 or if its really long a CLOB or LONGRAW). I am not entirely certain on the limitations of the length of the SELECT.
If your query is huge, you can just pass in one column at a time as a text parameter and have lexicals for each column selected in the form. This isn't entirely efficient, but you have to work within your confines.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
