| null values in parameter [message #301938] |
Fri, 22 February 2008 07:02  |
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  |
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 |
 |
Littlefoot
Messages: 21811
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 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 SQL> /
Enter value for par_comm: null
ENAME JOB COMM
---------- --------- ----------
ALLEN SALESMAN 300
WARD SALESMAN 500
MARTIN SALESMAN 1400
SQL> |
|
|
|
| Re: null values in parameter [message #301948 is a reply to message #301943] |
Fri, 22 February 2008 07:38 |
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 #302437 is a reply to message #301939] |
Mon, 25 February 2008 12:06 |
dude4084
Messages: 222
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 #302576 is a reply to message #302442] |
Tue, 26 February 2008 03:31 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
