Solved

Using substitution variable in Report Builder

Posted on 2002-03-21
7
1,264 Views
Last Modified: 2012-06-27
Hi there,
    How can one use the substitution variable in Report Builder at runtime so that the end user has the option of using the where clause with any field/column of his choice. In SQL*Plus, the follwoing query works fine:
 
  select empno,ename,&column_name
from emp
where &condition1 and &condition2
order by &order_column;

but when I uses the same syntax in the query builder of report builder, syntax errors are given. Any ideas? How can we use the same query in Report Builder? I have used query in Report Builder having one or more columns defined as bind variable so that their values are given at runtime having the following syntax:

select ename,sal,job
from emp
where empno = :P_1;

I want that the end user should have the option of using any column/field with the where clause at runtime so that he is not restricted to give values of only those columns which have been previously defined in the query.

Thanks in advance.


Hassan
0
Comment
Question by:hayub
7 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 6887891
You can use this syntax:

select ename,sal,job
from emp
where empno = nvl(:P_1,empno);

so a user can leave a parameter blank. With this method, if :P_1 is left blank at runtime the query will evaluate to:

select ename,sal,job from emp where empno = empno;

There can be more parameters added to the parameter form there.
0
 

Author Comment

by:hayub
ID: 6889015
Hi Henka,
    Thanks for you answer. It works but my question is that one should be able to write where clause in the parameter so that one can write statements like where sal >1500 etc. The option which you gave only accepts specific values for the parameters.

 I have used the follwoing query in report builder and it
works fine for only one column:

SELECT ALL EMP.ENAME, EMP.JOB, EMP.HIREDATE, EMP.SAL,emp.deptno
FROM EMP
 &P_1

if I want to add another column what will be the syntax?

The above query accepts the input for any column so that I can give where clause in the following manner for any column

where deptno = 20
where sal >1500

and so on

I hope you undertand my problem now.

Thanks

Hassan
0
 
LVL 6

Expert Comment

by:M-Ali
ID: 6890395
Hi,

Create a USER_PARAMETER in the report designer, lets say P_FILTER. Then in your query use:

SELECT EMP.ENAME, EMP.JOB, EMP.HIREDATE, EMP.SAL,emp.deptno
FROM EMP
WHERE &P_FILTER;

Here your filter can contain as many conditions as u want like:
(i) ename = 'SMITH'
(ii) ename = 'SMITH' and deptno=10

But the filter can not be empty. For example if no conditions have been specified make sure the filter contains somehtin like '1=1'.

Ali
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:venkat_id
ID: 6890780
hi hayub,
u can use leccical parameter.
1.define a user parameter first .say <:def_where>
2.now depending on ur req give the default where in  
   after parameter form
3.in the data model query query give our parameter as
  &def_where
things will work fine.
venkat
0
 

Author Comment

by:hayub
ID: 6892275
Hi Folks,
    Thanks a lot guys for you comments but my problem actuall lies when the quey is a join query between two tables as follows:



 SELECT ALL EMP.ENAME,
EMP.JOB, EMP.SAL, EMP.HIREDATE, EMP.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE (EMP.DEPTNO = DEPT.DEPTNO)
&where_clause

this gives syntax error. If the above quey is without the join WHERE clause then it works fine but in the present case it gives errors. Any ideas how can I solve this?

TIA

Hassan
0
 
LVL 6

Accepted Solution

by:
M-Ali earned 40 total points
ID: 6893234
Since you already have one WHERE condition i.e. (EMP.DEPTNO = DEPT.DEPTNO), make sure your WHERE_CLAUSE starts off with a space and an "AND" statement.

If this is not the problem, I suggest you post your actual query string, sample data entered into the where clause, and the error message it gives. Would help ppl to solve the problem..

Regards
Ali
0
 

Author Comment

by:hayub
ID: 6893361
Thanks a lot Ali, it worked!!!!

regards


Hassan
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now