Xenophobe74
Technical User
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?
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?