Home » Developer & Programmer » Reports & Discoverer » Report Query
Report Query [message #170803] Fri, 05 May 2006 04:10 Go to next message
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 #170805 is a reply to message #170803] Fri, 05 May 2006 04:24 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
aslme wrote on Fri, 05 May 2006 11:10

...(This query is ok)
It is, however, ugly as hell. And what should happen if you only enter an upper boundary (you have no losal but you do have a highsal)?

Try NVL:
SELECT *
FROM   emp
WHERE  sal BETWEEN NVL(:losal, sal) AND NVL(:hisal, sal)
AND    deptno = NVL(:deptno, deptno)
/


That's all there's to it. No complicated expensive OR statements. Just good old NVL...

MHE
Re: Report Query [message #170818 is a reply to message #170803] Fri, 05 May 2006 05:22 Go to previous messageGo to next message
Numan
Messages: 14
Registered: November 2003
Junior Member
u can use decode function for this

cheers 2
Re: Report Query [message #171085 is a reply to message #170803] Mon, 08 May 2006 05:27 Go to previous messageGo to next message
aslme
Messages: 26
Registered: May 2006
Location: Pakistan
Junior Member

Dear Friends i Think you guys did not understand my query is that I have a parameter from in which I select a job title of any employee like a select Manager from the List then all the Employee those Job is Manager are selected as it is when I select Clerk Job all the employee whose job is clerk are selected in a parameter from and then I delete a job of any employee I repeat only job of any employee and then select null value from a parameter from there is no record show but I want the recode of null job employee please if you help me in this I am very very great full to you for this



Chears
Re: Report Query [message #171091 is a reply to message #171085] Mon, 08 May 2006 06:16 Go to previous messageGo to next message
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 #171233 is a reply to message #171091] Tue, 09 May 2006 02:20 Go to previous messageGo to next message
aslme
Messages: 26
Registered: May 2006
Location: Pakistan
Junior Member

Dear Maaher you are the Cheetah (man) you can't believe no one can satisfied me regarding this problem you are the only one who satisfied me i am very great full to you and hope for same in the future thanks again.


Chears
Re: Report Query [message #171494 is a reply to message #170803] Wed, 10 May 2006 04:19 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: User can draw the layout itself
Next Topic: REP-1401: 'cf_sku_barcodeformula': Fatal PL/SQL error occurred.
Goto Forum:
  


Current Time: Fri Nov 08 18:54:54 CST 2024