Home » Developer & Programmer » Reports & Discoverer » null date
null date [message #672143] Thu, 04 October 2018 05:11 Go to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
i have a table which contains following columns.
Table Name:  Fees
Stuid   Number(5),
Chl_no  number(7),
Fmonth  varchar2(30),
Fdate   date,
fyear   number(4),
if fmonth is "october" and fdate is null then it is showing nothing in report. but when i put date in fdate column . the reports shows data. i want to shows the all report wether fdate is null or having a date.

AND isnull(fdate,date) between :fd AND :td
or 
AND fdate between isnull(:fd,date) AND inull(:td,date) 

[Updated on: Thu, 04 October 2018 05:48]

Report message to a moderator

Re: null date [message #672145 is a reply to message #672143] Thu, 04 October 2018 05:48 Go to previous messageGo to next message
cookiemonster
Messages: 13757
Registered: September 2008
Location: Rainy Manchester
Senior Member
So fix your report.

Reports can handle nulls, if yours isn't then that's because you've do something wrong, but we can't possibly guess what since you've told us absolutely nothing about the report.
Re: null date [message #672146 is a reply to message #672145] Thu, 04 October 2018 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 13757
Registered: September 2008
Location: Rainy Manchester
Senior Member
So now you've added some code.
Isnull isn't an oracle function, unless you've created your own, in which case you need to tell us what it does.
Re: null date [message #672147 is a reply to message #672146] Thu, 04 October 2018 05:54 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
i want to compare fdate with my runtime parameter (:fd and :td. if the fdate falls in the between and the data is showing. but i want to also showing if fdate is blank.
Re: null date [message #672149 is a reply to message #672147] Thu, 04 October 2018 06:31 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
https://livesql.oracle.com/apex/f?p=590:1:4509775553226::NO:::
Re: null date [message #672156 is a reply to message #672149] Thu, 04 October 2018 08:27 Go to previous messageGo to next message
cookiemonster
Messages: 13757
Registered: September 2008
Location: Rainy Manchester
Senior Member
The link doesn't appear to go anywhere useful and that doesn't change the fact that isnull isn't an oracle function.

Sounds like you want:
nvl(fdate, :fd) between :fd and :td

wrapping the parameters in calls to to_Date if necessary.
Re: null date [message #672189 is a reply to message #672156] Sat, 06 October 2018 01:33 Go to previous messageGo to next message
Littlefoot
Messages: 21517
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You should have provided valid credentials to log on to that Apex application, Shahzad.

Is this, actually, an Apex question (related to its interactive or classic report), and not Reports Builder one?
Re: null date [message #672200 is a reply to message #672189] Sun, 07 October 2018 21:37 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SELECT distinct student.stuiD, CLASS, section, NAME, f_NAME,mob,fdate, chl_no chl,annual, nvl(adm,0)+nvl(regist,0)+nvl(secut,0) adm,nvl(fees.transp,0)+nvl(ftution,0) ftut,nvl(other,0)+nvl(arrear,0) arres,fine,nvl(paid,0) paid,nvl(arrear,0)+nvl(tot,0) tot,nvl(adm,0)+nvl(regist,0)+nvl(secut,0)+nvl(fees.transp,0) trnp,nvl(ftution,0)+nvl(other,0)+nvl(arrear,0)+nvl(fine,0)-nvl(paid,0) balk,SUBSTR(FMONTH,1,3)||'-'||SUBSTR(FYEAR,3,4) FMON,status

FROM student,fees
WHERE   nvl(fdate, to_date(:fd, 'dd/mm/yyyy'))
 >= nvl(to_date(:fd,'dd/mm/yyyy'), to_date(:td,'dd/mm/yyyy'))    ------This query not performed.
and student.stuID=fees.stuID
and student.stuid=:cod 
order by chl_no
i want to get the data between "where" condition.but this query is not performed.
Quote:

:fd & :td is the runtime parameter.

[Updated on: Sun, 07 October 2018 21:38]

Report message to a moderator

Re: null date [message #672201 is a reply to message #672200] Sun, 07 October 2018 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
shahzad-ul-hasan wrote on Sun, 07 October 2018 19:37
SELECT distinct student.stuiD, CLASS, section, NAME, f_NAME,mob,fdate, chl_no chl,annual, nvl(adm,0)+nvl(regist,0)+nvl(secut,0) adm,nvl(fees.transp,0)+nvl(ftution,0) ftut,nvl(other,0)+nvl(arrear,0) arres,fine,nvl(paid,0) paid,nvl(arrear,0)+nvl(tot,0) tot,nvl(adm,0)+nvl(regist,0)+nvl(secut,0)+nvl(fees.transp,0) trnp,nvl(ftution,0)+nvl(other,0)+nvl(arrear,0)+nvl(fine,0)-nvl(paid,0) balk,SUBSTR(FMONTH,1,3)||'-'||SUBSTR(FYEAR,3,4) FMON,status

FROM student,fees
WHERE   nvl(fdate, to_date(:fd, 'dd/mm/yyyy'))
 >= nvl(to_date(:fd,'dd/mm/yyyy'), to_date(:td,'dd/mm/yyyy'))    ------This query not performed.
and student.stuID=fees.stuID
and student.stuid=:cod 
order by chl_no
i want to get the data between "where" condition.but this query is not performed.
Quote:

:fd & :td is the runtime parameter.
IMO, invalid syntax as posted above
syntax needs to be similar to below

WHERE MYDATE BETWEEN BEGIN_DATE AND END_DATE
Re: null date [message #672202 is a reply to message #672201] Sun, 07 October 2018 22:40 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
if i use this query its shows those records which falls between & and.i want to show all record which contains null and fdate.
Quote:

where fdate between :fd and :td
Re: null date [message #672203 is a reply to message #672202] Sun, 07 October 2018 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
shahzad-ul-hasan wrote on Sun, 07 October 2018 20:40
if i use this query its shows those records which falls between & and.i want to show all record which contains null and fdate.
Quote:

where fdate between :fd and :td
tables do not have RECORDS; only row(s).

Same row can NOT contain NULL AND FDATE

WHERE ROW_DATE = fdate
or ROW_DATE IS NULL
Re: null date [message #672204 is a reply to message #672203] Sun, 07 October 2018 23:19 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
ora-24323: value not allowed.
SELECT distinct student.stuiD, CLASS, section, NAME, f_NAME,mob,fdate, chl_no chl,annual, nvl(adm,0)+nvl(regist,0)+nvl(secut,0) adm,nvl(fees.transp,0)+nvl(ftution,0) ftut,nvl(other,0)+nvl(arrear,0) arres,fine,nvl(paid,0) paid,nvl(arrear,0)+nvl(tot,0) tot,nvl(adm,0)+nvl(regist,0)+nvl(secut,0)+nvl(fees.transp,0) trnp,nvl(ftution,0)+nvl(other,0)+nvl(arrear,0)+nvl(fine,0)-nvl(paid,0) balk,SUBSTR(FMONTH,1,3)||'-'||SUBSTR(FYEAR,3,4) FMON,status

FROM student,fees
WHERE  fdate between :fd and :td
or fdate is null
and student.stuID=fees.stuID
and student.stuid=:cod 
order by chl_no
Re: null date [message #672246 is a reply to message #672204] Mon, 08 October 2018 06:07 Go to previous messageGo to next message
cookiemonster
Messages: 13757
Registered: September 2008
Location: Rainy Manchester
Senior Member
Run the query in sqlplus to see exactly which bit it's complaining about.
And bracket your OR:
WHERE (fdate between :fd and :td
       or fdate is null)
and student.stuID=fees.stuID
and student.stuid=:cod 
order by chl_no
Re: null date [message #672251 is a reply to message #672246] Mon, 08 October 2018 21:28 Go to previous message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
THX ITS WORKED
Previous Topic: Matrix Report
Next Topic: How to add watermark in pdf
Goto Forum:
  


Current Time: Wed Dec 11 00:27:40 CST 2019