OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » null date

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

null date [message #672143]

Thu, 04 October 2018 05:11

shahzad-ul-hasan
Messages: 643
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

cookiemonster
Messages: 13963
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.

Report message to a moderator

Re: null date [message #672146 is a reply to message #672145]

Thu, 04 October 2018 05:50

cookiemonster
Messages: 13963
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.

Report message to a moderator

Re: null date [message #672147 is a reply to message #672146]

Thu, 04 October 2018 05:54

shahzad-ul-hasan
Messages: 643
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.

Report message to a moderator

Re: null date [message #672149 is a reply to message #672147]

Thu, 04 October 2018 06:31

shahzad-ul-hasan
Messages: 643
Registered: August 2002

Senior Member

https://livesql.oracle.com/apex/f?p=590:1:4509775553226::NO:::

Report message to a moderator

Re: null date [message #672156 is a reply to message #672149]

Thu, 04 October 2018 08:27

cookiemonster
Messages: 13963
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.

Report message to a moderator

Re: null date [message #672189 is a reply to message #672156]

Sat, 06 October 2018 01:33

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: null date [message #672200 is a reply to message #672189]

Sun, 07 October 2018 21:37

shahzad-ul-hasan
Messages: 643
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

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: null date [message #672202 is a reply to message #672201]

Sun, 07 October 2018 22:40

shahzad-ul-hasan
Messages: 643
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

Report message to a moderator

Re: null date [message #672203 is a reply to message #672202]

Sun, 07 October 2018 23:13

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: null date [message #672204 is a reply to message #672203]

Sun, 07 October 2018 23:19

shahzad-ul-hasan
Messages: 643
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

Report message to a moderator

Re: null date [message #672246 is a reply to message #672204]

Mon, 08 October 2018 06:07

cookiemonster
Messages: 13963
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

Report message to a moderator

Re: null date [message #672251 is a reply to message #672246]

Mon, 08 October 2018 21:28

shahzad-ul-hasan
Messages: 643
Registered: August 2002

Senior Member

THX ITS WORKED

Report message to a moderator

Previous Topic:	Matrix Report
Next Topic:	How to add watermark in pdf

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 17:30:52 CST 2025