?
Solved

Using substitution variable in Report Builder

Posted on 2002-03-21
7
Medium Priority
?
1,321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 160 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses

765 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