ORA/ CR subquery fail to return results

[Brenj68]

Hi, I'm using ORA 10 as base to develop Business Objects/ CR2008 reports, developed by a friend but the subquery is driving me.. I can see the theory of what I would like to see that 'Project_BU' should show the full name and not its id (as this means putting in place Case statements - which are not great)

What am I missing - any suggestions would be big help

B

SELECT SRM_RESOURCES.UNIQUE_NAME AS RES_ID,
SRM_RESOURCES.last_name,
SRM_RESOURCES.first_name,
SRM_RESOURCES.FULL_NAME,
SRM_RESOURCES.USER_ID,
ODF_CA_RESOURCE.DH_REG AS Res_BU,
ODF_CA_Resource.dh_sect AS Res_Sector,
ODF_CA_RESOURCE.DH_SER_LINE AS Res_Dept,
ODF_CA_PROJECT.DH_SECTOR AS Project_Sector,
ODF_CA_PROJECT.DH_PREGION AS Project_BU,
ODF_CA_PROJECT.DH_PSLINE AS Project_Dept,
ODF_CA_PROJECT.DH_MANAGER_ID AS Proj_Manager,
ODF_CA_PROJECT.DH_SPONSOR AS Prog_Manager,
PRJ_BLB_SLICES.SLICE_DATE,
PRJ_BLB_SLICES.SLICE,
PRTASK.PRPROJECTID,
PRTIMESHEET.PRID,
PRTASK.PRID AS TASK_ID,
SRM_WORK_PROJECTS_V.UNIQUE_NAME AS Project_Ref,
SRM_WORK_PROJECTS_V.NAME,
PRTASK.PRNAME,
PRTIMEENTRY.PRASSIGNMENTID,
PRTIMEPERIOD.PRSTART,
PRTIMEPERIOD.PRFINISH,
PRTASK.PRISMILESTONE,
PRTASK.PRISTASK,
ODF_CA_RESOURCE.DH_GRADE,
ODF_CA_RESOURCE.DH_CD_OFF_LOC AS Res_Office,
ODF_CA_RESOURCE.DH_CD_GLOBAL_LOC AS Res_Country
FROM SRM_RESOURCES,
PRJ_BLB_SLICES,
PRTASK,
PRTIMESHEET,
PRJ_BLB_SLICEREQUESTS,
SRM_WORK_PROJECTS_V,
PRTIMEENTRY,
PRTIMEPERIOD,
ODF_CA_RESOURCE,
--right join
--(SELECT lookup_code, name
--FROM cmn_lookups_v
--WHERE lookup_type = 'DH_REGION'
--AND language_code = 'en')
--Project_BU,
ODF_CA_PROJECT,
PRASSIGNMENT
WHERE PRTIMEPERIOD.PRID =PRTIMESHEET.PRTIMEPERIODID
AND SRM_RESOURCES.ID =ODF_CA_RESOURCE.ID
AND PRTIMEENTRY.PRTIMESHEETID=PRTIMESHEET.PRID
AND PRASSIGNMENT.PRID =PRTIMEENTRY.PRASSIGNMENTID
AND PRASSIGNMENT.PRTASKID =PRTASK.PRID
AND PRJ_BLB_SLICEREQUESTS.ID =PRJ_BLB_SLICES.SLICE_REQUEST_ID
AND PRTIMEENTRY.PRID =PRJ_BLB_SLICES.PRJ_OBJECT_ID
AND SRM_RESOURCES.USER_ID =PRTIMEENTRY.PRMODBY
AND SRM_WORK_PROJECTS_V.ID =PRTASK.PRPROJECTID
AND ODF_CA_PROJECT.ID =SRM_WORK_PROJECTS_V.ID
AND (PRJ_BLB_SLICES.SLICE_DATE BETWEEN to_date ('01/03/2011','mm/dd/yyyy') and to_date ('01/03/2012','mm/dd/yyyy'))

 

[carp]

What were you trying to right join your dynamic view (project_bu) on? It looks like you are trying to mix table list techniques here. Recommend you use "inner join", "right join", or "left join" throughout your entire query. If you do, you will need to specify an "ON" clause for each join.

 

[Dagon]

If your joining correctly to project_Bu, it should be possible to select any data item from it. You also seem to be using project_bu as the alias for one of your columns:

ODF_CA_PROJECT.DH_PREGION AS Project_BU

It seems a bit strange to be trying to use the same name for two completely different purposes.