Home » Developer & Programmer » Reports & Discoverer » null values in parameter (Reports, 6i,XP)
null values in parameter [message #301938] Fri, 22 February 2008 07:02 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

I have found one problem in mr report,I am using "nvl" like this.

select a.warr_cd,b.prod_nm
from sv_warr a,sl_prod b
where a.prod_cd=b.prod_cd
and claimstatus=nvl(:status,claimstatus)

The problem is that there are some null values in a claimstatus
and when i run report and no value ionput at claimstatus parameter then the reports discard all records that contains claimstatus values null ,kindly help me how can i get rid of this problem,

Can i assign run time value of claimstatus if yes then how can i do this

Regards


zuhair
Re: null values in parameter [message #301939 is a reply to message #301938] Fri, 22 February 2008 07:06 Go to previous messageGo to next message
benoitchabot
Messages: 15
Registered: October 2005
Location: Quebec , Canada
Junior Member
select a.warr_cd,b.prod_nm
from sv_warr a,sl_prod b
where a.prod_cd=b.prod_cd
and ((claimstatus is null) or (claimstatus is not null and claimstatus = :status))
Re: null values in parameter [message #301943 is a reply to message #301938] Fri, 22 February 2008 07:21 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What would you want to select as a result (at the end of the story)?

It is quite obvious that, when you enter some value into the 'status' parameter, you'd get records where 'claimstatus = :status'.

If you leave the 'status' empty (i.e. NULL), then you'll get all records where 'claimstatus' is not null.

As you've said, it doesn't satisfy your needs. But, what should be the result in such a case?

Here's an example: this is what we have now:
SQL> select ename, job, comm from emp where deptno = 30;

ENAME      JOB             COMM
---------- --------- ----------
ALLEN      SALESMAN         300
WARD       SALESMAN         500
MARTIN     SALESMAN        1400
BLAKE      MANAGER
TURNER     SALESMAN           
JAMES      CLERK
Let's test different cases: a parameter is NOT NULL (no problem with that):
SQL> select ename, job, comm
  2  from emp where deptno = 30
  3  and comm = nvl(&par_comm, comm);
Enter value for par_comm: 300

ENAME      JOB             COMM
---------- --------- ----------
ALLEN      SALESMAN         300
And now, the parameter IS NULL:
SQL> /
Enter value for par_comm: null

ENAME      JOB             COMM
---------- --------- ----------
ALLEN      SALESMAN         300
WARD       SALESMAN         500
MARTIN     SALESMAN        1400

SQL>
What do you expect as a result when the parameter IS NULL?
Re: null values in parameter [message #301948 is a reply to message #301943] Fri, 22 February 2008 07:38 Go to previous messageGo to next message
benoitchabot
Messages: 15
Registered: October 2005
Location: Quebec , Canada
Junior Member
I don't realy understand what you want to have in result.
Can you explain me what you want to have when your parameter is not null and what you want to have when your parameter is null.
Re: null values in parameter [message #301951 is a reply to message #301948] Fri, 22 February 2008 07:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, that's why we've written the OraFAQ Forum Guide. If people who ask questions did that initial effort, things would be much more clear.

"Posting guidelines" section (especially "Practice" subsection) clearly says that it is a good idea to provide sample data (CREATE TABLE and INSERT INTO statements), along with the desired result (not exact query; if he knew it, he wouldn't need to ask for help).

If @gozuhair had done that, it would be easier to answer his question.
Re: null values in parameter [message #302437 is a reply to message #301939] Mon, 25 February 2008 12:06 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
benoitchabot wrote on Fri, 22 February 2008 18:06
select a.warr_cd,b.prod_nm
from sv_warr a,sl_prod b
where a.prod_cd=b.prod_cd
and ((claimstatus is null) or (claimstatus is not null and claimstatus = :status))




Hi
The above seems to be required solution.

-Dude
Re: null values in parameter [message #302442 is a reply to message #302437] Mon, 25 February 2008 12:21 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Maybe, from your point of view. However, as @gozuhair didn't return to answer some questions and clear a few doubts, we can't know for sure.
Re: null values in parameter [message #302576 is a reply to message #302442] Tue, 26 February 2008 03:31 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks people for your reply,sorry for late acknowledgement because my phone line was dead and was unable to connect internet.My problem is solved and i am using the following query that your people send me.

benoitchabot wrote on Fri, 22 February 2008 18:06
select a.warr_cd,b.prod_nm
from sv_warr a,sl_prod b
where a.prod_cd=b.prod_cd
and ((claimstatus is null) or (claimstatus is not null and claimstatus = :status))


Thanks for your continuous supports
Re: null values in parameter [message #302586 is a reply to message #302576] Tue, 26 February 2008 04:27 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Allright! NOW we know!

Thank you for the feedback!
Previous Topic: 6i Migration to 10g
Next Topic: ORA-01576
Goto Forum:
  


Current Time: Fri Dec 09 01:44:57 CST 2016

Total time taken to generate the page: 0.09340 seconds