Home » Developer & Programmer » Reports & Discoverer » If Condition to Run Report (Report 6i, Windows 7)
If Condition to Run Report [message #662633] Mon, 08 May 2017 06:34 Go to next message
hissam78
Messages: 88
Registered: August 2011
Location: PAKISTAN
Member
We have designed one Form in Forms 6i to Call the report,
it is working fine,
now the user want to see all the records, either Form parameter field is blank or with Selection parameter,

For example
if
Project code = 10
Date = 10-04-2017
Company = null

so the report should run for all the companies having project code 10 and date 10-04-2017,

how we can acheive, somebody can help us?

thanks
Re: If Condition to Run Report [message #662635 is a reply to message #662633] Mon, 08 May 2017 06:59 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you want it to either find rows where company = parameter or not restrict by company if parameter is null?
That's a simple one line where clause using OR. Have a think about it.
Re: If Condition to Run Report [message #662656 is a reply to message #662635] Tue, 09 May 2017 01:00 Go to previous messageGo to next message
hissam78
Messages: 88
Registered: August 2011
Location: PAKISTAN
Member
Thankful for reply Cookiemonster,
will do
Re: If Condition to Run Report [message #663558 is a reply to message #662656] Thu, 08 June 2017 03:25 Go to previous messageGo to next message
Lubna
Messages: 12
Registered: June 2017
Junior Member
You should have to provide data to demonstrate the test example but-
Assume you have the following parameters,

p_project_code - Project code = 10
p_date - Date = 10-04-2017
p_company - Company = All companies or OneCompany

Use below case in where clause condition

where p_project_code = 10
and p_date = to_date('10-Apr-2017')
and case 
when p_company = 0 
then company -- All Companies when parameter 0 will pass
else p_company --or it will display only OneCompany based on parameter pass.
end;
Re: If Condition to Run Report [message #663561 is a reply to message #663558] Thu, 08 June 2017 03:37 Go to previous messageGo to next message
hissam78
Messages: 88
Registered: August 2011
Location: PAKISTAN
Member
Thanks a lot for Reply Dear Lubna,
Got it, Sure it will work,
grateful to you
Re: If Condition to Run Report [message #663640 is a reply to message #663558] Mon, 12 June 2017 03:30 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or you could leave p_company null and have the simpler:
AND (company = p_company or p_company is null)

And that case won't work as you've written it - you need to compare the result to something.
Re: If Condition to Run Report [message #663655 is a reply to message #663640] Tue, 13 June 2017 09:24 Go to previous messageGo to next message
Lubna
Messages: 12
Registered: June 2017
Junior Member
Obviously..
where p_project_code = 10
and p_date = to_date('10-Apr-2017')
and company = case 
when p_company = 0 
then company -- All Companies when parameter 0 will pass
else p_company --or it will display only One Company based on parameter pass in p_company.
end;
Re: If Condition to Run Report [message #663656 is a reply to message #663655] Tue, 13 June 2017 09:58 Go to previous message
Michel Cadot
Messages: 65194
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select to_date('10-Apr-2017') from dual;
select to_date('10-Apr-2017') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Previous Topic: Run Reports In Maximize Mode
Next Topic: Report in Export in Excel Format
Goto Forum:
  


Current Time: Sat Nov 18 13:41:12 CST 2017

Total time taken to generate the page: 0.01200 seconds