Report Query [message #170803] |
Fri, 05 May 2006 04:10 |
aslme
Messages: 26 Registered: May 2006 Location: Pakistan
|
Junior Member |
|
|
Report query is
1) select *
from emp
where ((:p_losal is not null and :p_hisal is not null and sal between :p_losal and :p_hisal) or (:p_losal is null or :p_hisal is null)) /* by this between query if I am not entered any amount, report shows all the records and if I entered any value reports shows entered value records. (This query is ok)
2) but when I use another parameter like this
and deptno =:p_eno /* and enter the deptno it show the records of the selected dept only. But I want when I am not entered any dept no in the parameter the reports must be run and shows all the records.(but i am unable to apply this query)
if any one unable to understand please let me know i attach a file for more easy to understand.
Chears
|
|
|
|
|
|
Re: Report Query [message #171091 is a reply to message #171085] |
Mon, 08 May 2006 06:16 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Ok, here's version two:
SELECT employee_id
, last_name
, first_name
, department_id
, job_id
, salary
FROM employees
WHERE salary BETWEEN NVL(:losal, salary) AND NVL(:hisal, salary)
AND NVL(department_id,-1) = COALESCE(:deptno, department_id, -1)
AND NVL(job_id,'X') = COALESCE(upper(:jobid), job_id, 'X')
This query will ignore the parameters jobid/deptno if they are not filled. If you want only the records of those records that have no value for job id, you can change the query like this:
SELECT employee_id
, last_name
, first_name
, department_id
, job_id
, salary
FROM employees
WHERE salary BETWEEN NVL(:losal, salary) AND NVL(:hisal, salary)
AND NVL(department_id,-1) = COALESCE(:deptno, department_id, -1)
AND NVL(job_id,'X') = NVL(upper(:jobid), 'X')
Now, this should get you on the right track.
MHE
|
|
|
|
Re: Report Query [message #171494 is a reply to message #170803] |
Wed, 10 May 2006 04:19 |
Numan
Messages: 14 Registered: November 2003
|
Junior Member |
|
|
Dear Aslme,
here is detail for my suggession
u can do this by using decode function in ur report query. for this u do following steps.
Step 1:
In ur report parameter say "Dept" write following query.
Select Deptno,Dname
From Dept
union
Select 1,'ALL'
From Dual
Check the checkbox hide first column.
Now this will create parameter with "ALL" Dname, suppose for All Depts Report.
Step 2:
In ur Report Query, write this in ur where clause.
Where Deptno = Decode(:Dept,1,Deptno,:Dept)
now when u select ALL from parameter it will show all dept report,else ur selected Dept Report.
Hope u understand,
Best of Luck,
Numan
[Updated on: Wed, 10 May 2006 04:20] Report message to a moderator
|
|
|
Re: Report Query [message #174394 is a reply to message #171091] |
Mon, 29 May 2006 01:56 |
kal_youssef
Messages: 25 Registered: September 2005 Location: canada
|
Junior Member |
|
|
Dear Mr. Maher
My question is similar to the one listed im trying to run oracle reports but pass parameters.
The database im using is sql server but im using oracle reports 10g so how do i pass parameters to oracle reports 10g when i deploy it on the web?
regards
Kal Youssef
|
|
|