OLD QUERY

In Old report, all the data was coming from one table Date_info

1 Formula used: Include:

IIF ({date_info.date_app_entered} <> #1/1/1900# and {date_info.date_app_recvd} <> #1/1/1900# and
    {date_info.date_qt_mailed} = #1/1/1900# and {date_info.date_gen_letter} = #1/1/1900# and
    {date_info.date_declined} = #1/1/1900# , "YES" ,  "NO")

-----
1 Record selection formula

{@Include} = "YES" and
{date_info.lifecycle_status} in ["QUOTE", "RENEWAL", "SUBMISSION"] and
{date_info.date_app_entered} >= Date (2008, 01, 01)


-----------------------------------------------------------

NEW QUERY

In New Report,

I need to JOIN 3 Tables and use all the old report Criteria to get the data.
BUt My New Query is failing on second last line please help.

/*+ PARALLEL(dateinfo,4)*/

select
d.ISN,
d.name_ins,
d.lifecycle_status,
d.date_app_entered,
p.date_app_recvd,
d.date_qt_mailed,
d.date_gen_letter,
d.date_declined,
d.uwinits,
d.bornum

from DateInfo D
left outer join policy p on d.lifecycle_status = p.lifecycle_status and
d.OBJECT_NUM  = p.POLICY_NUM and
d.object_ver = p.policy_ver

left outer join quote q on d.lifecycle_status =
q.lifecycle_status and
d.OBJECT_NUM  = q.quote_NUM and
d.object_ver = q.quote_ver
where
d.lifecycle_status in ('submission','quote','renewals')and
d.date_app_entered > '2008/01/01' and
p.date_app_recvd <> '1/1/1900' and
d.date_qt_mailed  =  '1/1/1900' and
d.date_gen_letter  = '1/1/1900' and
d.date_declined = datetime '1/1/1900' , "YES" ,  "NO")
order by d.date_app_entered
----------------------------------------

My Query Below Works, But in Crystal format editor my
Field p.Date_app_recvd is not showing date time, I need to modify it to Date only.
Any Idea how to do that or if I can do that on my query it will be great.

In database field Date_app_recvd  data type is DATETIME



select
d.object_num,
d.object_ver,
q.quote_num,
q.quote_ver,
p.policy_num,
p.policy_ver,
d.ISN,
d.name_ins,
d.lifecycle_status,
d.date_app_entered,
q.date_app_recvd,
p.date_app_recvd,
d.date_qt_mailed,
d.date_gen_letter,
d.date_declined,
d.uwinits,
d.bornum
from dateinfo d
left outer join quote q
ON  d.object_num=q.quote_num
and d.object_ver=q.quote_ver
left outer join policy p
on d.object_num=p.policy_num
and d.object_ver=p.policy_ver
where
(d.lifecycle_status ='renewal' or
d.lifecycle_status = 'quote'  or
d.lifecycle_status = 'submission') and
(p.date_app_recvd <> '1/1/1900' or

 q.date_app_recvd <> '1/1/1900') and
d.date_app_entered > '2008/01/01' and

d.date_qt_mailed  =  '1/1/1900' and
d.date_gen_letter  = '1/1/1900' and
d.date_declined   = '1/1/1900'
order by d.date_app_entered

Edited by vpriyanka2001 - 10 Sep 2009 at 9:09am