Home » Developer & Programmer » Reports & Discoverer » null value problem (Report,6i,XP)
null value problem [message #346261] Mon, 08 September 2008 00:01 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

Please help,I have a tables and there names are sv_warr and warr_abroad. the structures are.

sv_warr
wc_id (primary key)
wc_no
wc_dt
deal_cd

warr_abroad
wc_id  foreign key
CLAIM_CDDET


When i execute the following query ,the total no of records display "1353" but when i am trying to use CLAIM_CDDET with blank parameters then the system shows only "460" records because remaining 893 records contain no value means claim_cddet containg no values...
select a.wc_id
from warr_abroad b,sv_warr a
where b.CLAIM_CDDET=nvl(:status,b.CLAIM_CDDET)
and a.wc_id=b.wc_id


my query is that how can i display all records including null if user give no value in parameter, and if user assign some value in parameter then the system will display the required records against assign parameters,Please help.




Re: null value problem [message #346335 is a reply to message #346261] Mon, 08 September 2008 04:34 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You could use a lexical parameter which would get its value depending on an "ordinary" parameter ("status"). Query would look like this:
select a.wc_id
from warr_abroad b,sv_warr a
where a.wc_id=b.wc_id
  &lex_status

Create "lex_status" parameter; its datatype should be CHARACTER, length 100 (should be enough). Set its value in the AFTER PARAMETER FORM trigger, such as
if :status is null then
   :lex_status := 'and 1 = 1';     -- fetch all records
else
   :lex_status := 'and b.claim_cddet = :status';
   -- or, possibly, 'and b.claim_cddet = ' || :status;
end if;
Re: null value problem [message #346551 is a reply to message #346261] Mon, 08 September 2008 18:57 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
gozuhair wrote on Mon, 08 September 2008 10:01
Dear All

Please help,I have a tables and there names are sv_warr and warr_abroad. the structures are.

sv_warr
wc_id (primary key)
wc_no
wc_dt
deal_cd

warr_abroad
wc_id  foreign key
CLAIM_CDDET


When i execute the following query ,the total no of records display "1353" but when i am trying to use CLAIM_CDDET with blank parameters then the system shows only "460" records because remaining 893 records contain no value means claim_cddet containg no values...
select a.wc_id
from warr_abroad b,sv_warr a
where b.CLAIM_CDDET=nvl(:status,b.CLAIM_CDDET)
and a.wc_id=b.wc_id


my query is that how can i display all records including null if user give no value in parameter, and if user assign some value in parameter then the system will display the required records against assign parameters,Please help.








Try this code in where part



where ((:status is null and 1=1) or
       (:status is not null and b.CLAIM_CDDET=:status))


Good Luck

-Dude
Re: null value problem [message #346593 is a reply to message #346261] Tue, 09 September 2008 01:34 Go to previous message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
thanks for your help,both queries are working and giving a desirable result,one thing more,please explain the following query in detail
   :lex_status := 'and 1 = 1';     -- fetch all records


   :lex_status := 'and b.claim_cddet = :status';



Previous Topic: Can' connect to Oracle Application for Discoverer
Next Topic: REFERENCE CURSOR
Goto Forum:
  


Current Time: Sun Dec 04 08:28:06 CST 2016

Total time taken to generate the page: 0.06868 seconds