using NVL and date fields 1

[Xenophobe74]

Here is a query I write in SQL*Plus that gives me exactly what I need in a report I am creating:

select l.messaging_id,l.name,l2.name,p.pager_id,
o.START_DATE, o.END_DATE, o.REMARK,
from listing l, listing l2, on_call_assignment o,
pager_assignment p
where l.MESSAGING_ID = o.GROUP_ID(+)
and o.MESSAGING_ID = l2.MESSAGING_ID(+)
and l2.messaging_id = p.messaging_id(+)
and (l.name='Behavioral Medicine, Child/Adolescent'
or l.name = 'Behavioral Medicine, Psych.Detox, PA')
AND (NVL(trunc(O.START_DATE),SYSDATE) >=trunc(sysdate) AND NVL(trunc(O.START_DATE),SYSDATE) < trunc(sysdate)+1)


I have everything created in Crystal XI but the I cannot get the date fields to work properly in crystal. Basically, if I can get the following two statements working I will be all set.

AND (NVL(trunc(O.START_DATE),SYSDATE) >=trunc(sysdate) AND NVL(trunc(O.START_DATE),SYSDATE) < trunc(sysdate)+1)

I have been beating my brains out trying to get this to work and am not getting anywhere. Any suggestions?

 

[lbass]

Not sure these functions are available to you, but you could try using:

and {fn ifnull({O.START_DATE},{fn curdate()})} >= {fn curdate()} and
{fn ifnull({O.START_DATE},{fn curdate()})} >= {fn curdate()}

Or you could add the following to your record selection formula and it will pass to the SQL statement:

isnull({O.START_DATE}) or
(
{O.START_DATE} >= currentdate and
{O.START_DATE} < currentdate + 1
)

I think this will work correctly without converting the datetime to a date.

-LB

 

[GJParker]

try somethiong like

(IsNull({O.START_DATE}) OR
{O.START_DATE} >= today and {O.START_DATE} <= today + 1)

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[Xenophobe74]

Thanks Gary and thanks lbass. Both replies were helpful. lbass, I used the second option in your reply and it worked awesome. Thanks again for the help.