Home » Developer & Programmer » Reports & Discoverer » assign value run time in reports (Report, 6i, XP)
assign value run time in reports [message #331568] Fri, 04 July 2008 01:09 Go to next message
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 #331603 is a reply to message #331568] Fri, 04 July 2008 03:45 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you use the "claimstatus" parameter for? If you want to fetch ALL of 2000 records (no matter whether "claimstatus" column contains values or not), remove "WHERE claimstatus ..." out of query.

Or, do you want to select records where "claimstatus" is empty, no matter what parameter value is? If so, you might try with something like
select claimstatus
from warr_abroad
where claimstatus = :claimstus
   or claimstatus is null;


If that's not the point, well, I must admit that I don't understand what the problem is.
Re: assign value run time in reports [message #331656 is a reply to message #331568] Fri, 04 July 2008 06:56 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks for the reply i will check your query




Re: assign value run time in reports [message #331717 is a reply to message #331603] Fri, 04 July 2008 23:02 Go to previous messageGo to next message
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 #331731 is a reply to message #331656] Sat, 05 July 2008 02:04 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Thanks it useful to me.
Re: assign value run time in reports [message #331818 is a reply to message #331717] Sat, 05 July 2008 14:26 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you consider use of lexical parameters? Query would then look like
SELECT column_1, column_2, ...
FROM warr_abroad
&lex_param

In the after parameter form trigger, you'd create lexical parameter as
IF :claimstatus IS NULL THEN
  :lex_param := 'WHERE 1 = 1';
ELSE
  :lex_param := 'WHERE claimstatus = ' || :claimstatus;
END IF;

In the first case (:claimstatus is null) you'd select all records; in the second case, you'd select only records whose "claimstatus" column satisfies value entered into the "claimstatus" parameter.
Re: assign value run time in reports [message #331967 is a reply to message #331818] Mon, 07 July 2008 00:35 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

claimstatus=nvl(:claimstus,claimstatus)

this code won't work for null values
so as
where claimstatus=null

the right approach is
where claimstatus is null

if you make it
where 1=1

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 #332014 is a reply to message #331967] Mon, 07 July 2008 04:35 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks for the suggestion,I will try it and let you know.

Thank you for all your help
Re: assign value run time in reports [message #332290 is a reply to message #331967] Tue, 08 July 2008 02:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

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

Re: assign value run time in reports [message #332313 is a reply to message #332302] Tue, 08 July 2008 03:28 Go to previous message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
sorry it was my mistake ,your 2nd query is working perfectly,
Once again thank you for all your help.
Previous Topic: How can i use the parameter in cgicmd.dat file
Next Topic: Grouping Columns In Discoverer
Goto Forum:
  


Current Time: Sat Dec 10 09:05:54 CST 2016

Total time taken to generate the page: 0.08626 seconds