|
Lhuffst (Programmer) |
2 Aug 12 17:45 |
I think there are a couple of issues going on. I created the sql in oracle then I copied that to crystal using the command area under the database connections.
1. If I try to import the dates as dates (To_Date) reformatted to yyyy-mm-dd instead of the oracle default dd-MON-yyyy, then I get an error in crystal saying that it failed to retrieve from the database. If I use to_Char instead (making the date field a string), then I can paste the sql in the command area under the database connnections.
Ultimately my goal is to get the minimum start date and the maximum end date for all the records. I was trying to sort it just to verify that I was getting the same number of records in crystal that I had in oracle.
CODEselect distinct
b.proj_catg_name,
b.Proj_catg_short_name,
c.wbs_name as cwbs,
d.Task_name,
d.Status_code,
--f.target_cost,
to_Char(D.EARLY_START_DATE, 'YYYY-MM-DD') as EarlyStartDte,
To_Char(D.ACT_START_DATE, 'YYYY-MM-DD') as TaskActStart,
Min(CASE
WHEN d.Status_code = 'TK_Active' THEN To_Date(D.ACT_START_DATE, 'YYYY-MM-DD')
WHEN d.Status_code = 'TK_NotStart' THEN To_Date(D.EARLY_START_DATE, 'YYYY-MM-DD')
WHEN d.Status_code = 'TK_Complete' THEN To_Date(D.ACT_START_DATE, 'YYYY-MM-DD')
END) as NewStartDate ,
--extract(year from d.Early_end_date) as YrEndDate,
To_Char(d.early_end_date, 'YYYY-MM-DD') as EarlyEndDate,
To_Char(d.act_end_date, 'YYYY-MM-DD') as TaskActEnd,
e.proj_Short_Name,
--count(e.proj_short_name) as cntTasks,
--Max(CASE
-- WHEN d.Status_code = 'TK_Complete' THEN to_Char(D.ACT_End_DATE, 'DD-MON-YYYY')
-- WHEN d.Status_code = 'TK_Active' THEN To_Char(D.EARLY_END_DATE, 'DD-MON-YYYY')
-- When d.Status_Code = 'TK_NotStart' THEN To_Char(D.EARLY_END_DATE, 'YYYY-MM-DD')
--END) as NewEndDate
Max(CASE
WHEN d.Status_code = 'TK_Complete' THEN To_Date(D.ACT_End_DATE, 'YYYY-MM-DD')
WHEN d.Status_code = 'TK_Active' THEN To_Date(D.EARLY_END_DATE, 'YYYY-MM-DD')
When d.Status_Code = 'TK_NotStart' THEN To_Date(D.EARLY_END_DATE, 'YYYY-MM-DD')
END) as NewEndDate
from admuser.projpcat a, admuser.pcatval b, admuser.projwbs c,
admuser.task d, admuser.project e, admuser.taskrsrc f
where
b.proj_catg_id = a.Proj_catg_id
and c.proj_id = a.proj_id
and d.wbs_id = c.wbs_id
and d.proj_id = a.proj_id
and E.PROJ_ID = a.proj_id(+)
and f.proj_id = a.proj_id
--and a.proj_id = '9892'
and b.proj_catg_name like 'Beaver%'
and e.proj_short_name like 'BeaverDam-ESA'
--and rownum <= 100
group by d.task_id,
a.proj_id,
a.proj_catg_id,
a.proj_catg_type_id,
b.proj_catg_name,
b.Proj_catg_short_name,
c.proj_id,
c.wbs_name,
c.wbs_id,
c.delete_date,
c.ANTICIP_END_DATE,
d.wbs_id,
D.EARLY_START_DATE,
extract(year from d.Early_end_date),
To_date(d.early_end_date, 'YYYY-MM-DD'),
d.Task_name,
d.Status_code,
D.ACT_START_DATE,
d.act_end_date,
e.proj_Short_Name,
F.TARGET_COST,
D.EARLY_END_DATE |
|
Talk To Other Members
Read More Threads Like This One
Just traded in my old subtlety...
