| assign value run time in reports [message #331568] |
Fri, 04 July 2008 01:09  |
gozuhair
Messages: 206
Registered: January 2008
|
Senior Member |
|
|
Dear All
I would be grateful to you if you cound solve my problem,My problem is that I have given a parameter name as "claimstatus",and I have total nos of "2000" records in table that contains claimstatus column. .
When i am going to execute report then the report display only 1500 records and discard 500 records because 500 records contains null value against claimstatus column.
I am trying to assign value at the time running report but the result still same means reports discard 500 records,I have written the following query in report .
select claimstatus
from WARR_ABROAD
where claimstatus=nvl(:claimstus,claimstatus)
|
|
|
|
|
|
|
|
| Re: assign value run time in reports [message #331717 is a reply to message #331603] |
Fri, 04 July 2008 23:02  |
gozuhair
Messages: 206
Registered: January 2008
|
Senior Member |
|
|
Dear All
Problem is still there,My query is that I have 2000 record in my table name as warr_abbroad,
in this table i have a column name claimstatus that contains 3 type of value
- Approve
- Resubmit
- Deniel
In warr_abbroad table there are 500 approve cases,500 resubmit cases, and 500 deniel cases and 500 that types of
cases that contain data but claimstatus column is null so that when i want to display all records and leave blank claimstatuus parameter in my report then the system display only 1500 records and discard 500 records that are null.
In my query where condition is necessary because in some cases I want to display only Approve cases and same thing in deniel and resubmit cases.
I think this problem is occuring due to the null values in claimstatus column ,is there any query through which i can assign some value like a 'N/A' to claimstatus column while running report,pls help
|
|
|
|
|
|
|
|
| Re: assign value run time in reports [message #331967 is a reply to message #331818] |
Mon, 07 July 2008 00:35 |
|
|
claimstatus=nvl(:claimstus,claimstatus)
this code won't work for null values
so as
the right approach is
where claimstatus is null
if you make it
Then all records will be fetched, not the 500 records only.
but the idea is correct. just change it with
:lex_param := 'WHERE claimstatus is null';
Or if you don't want to use lexical, do this
where nvl(claimstatus, 'NULL') = nvl(:claimstatus,'NULL')
[Updated on: Mon, 07 July 2008 00:37] Report message to a moderator |
|
|
|
|
|
| Re: assign value run time in reports [message #332290 is a reply to message #331967] |
Tue, 08 July 2008 02:10 |
gozuhair
Messages: 206
Registered: January 2008
|
Senior Member |
|
|
Dear
I have checked your 2nd query but its not working properly,your query is
SELECT claimstatus
FROM warr_abroad
where nvl(claimstatus, 'NULL') = nvl(:claimstatus,'NULL')
The result is blank if i am using the above query,kindly suggest
|
|
|
|
| Re: assign value run time in reports [message #332302 is a reply to message #332290] |
Tue, 08 July 2008 02:39 |
|
|
if you use the following code is it working?
where claimstatus is null
if yes then maybe your variable(:claimstatus) is not null. check if it is blank or null. did you iniatialize that variable with space or what?
If that variable is null then that code you have used must return the correct result because deffinitely 'NULL'='NULL'.
But the mere fact that this code
select claimstatus
from WARR_ABROAD
where claimstatus=nvl(:claimstus,claimstatus)
returned 1,500 records (not null), I can say that :claimstus is null.
The only problem with my code is that when you put the word 'NULL' to your variable :claimstatus, it will retreive that 500 records without claimstatus. but, that's not what your case.
-Wency-
[Updated on: Tue, 08 July 2008 02:51] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
