OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » Report Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Report Query [message #170805 is a reply to message #170803]

Fri, 05 May 2006 04:24

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

Report message to a moderator

Re: Report Query [message #170818 is a reply to message #170803]

Fri, 05 May 2006 05:22

Numan
Messages: 14
Registered: November 2003

Junior Member

u can use decode function for this

cheers 2

Report message to a moderator

Re: Report Query [message #171085 is a reply to message #170803]

Mon, 08 May 2006 05:27

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

Report message to a moderator

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

Report message to a moderator

Re: Report Query [message #171233 is a reply to message #171091]

Tue, 09 May 2006 02:20

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

Report message to a moderator

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

Report message to a moderator

Previous Topic:	User can draw the layout itself
Next Topic:	REP-1401: 'cf_sku_barcodeformula': Fatal PL/SQL error occurred.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 12:16:47 CST 2024