SEO

Hi all, I have a stored proc which returns twice the result and I dont
know why. Can someone have a look at the following code?

BTW, I commented the last SELECT/JOIN, cause that one doubled the
result too.

CREATE procedure ent_tasks_per_user_company (
@companyName as varchar(50),
@resourceName as varchar(50)
)
AS

SELECT
tasks.WPROJ_ID as WPROJ_ID, tasks.ENT_ProjectUniqueID as
ProjectUniqueID, tasks.ENT_TaskUniqueID as TaskUniqueID,
tasks.TaskEnterpriseOutlineCode1ID as TaskEnterpriseOutlineCode1ID,
codes.OC_NAME as OC_NAME, codes.OC_DESCRIPTION as OC_DESCRIPTION,
codes.OC_CACHED_FULL_NAME as OC_CACHED_FULL_NAME,
taskStd.TaskName as TaskName, taskStd.TaskResourceNames as
TaskResourceNames, taskStd.TaskPercentComplete as TaskPercentComplete

INTO #myTemp

FROM MSP_VIEW_PROJ_TASKS_ENT as tasks

INNER JOIN MSP_OUTLINE_CODES as codes
ON
(
codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1ID
AND
codes.OC_CACHED_FULL_NAME LIKE @companyName + '.%'
)

INNER JOIN MSP_VIEW_PROJ_TASKS_STD as taskStd
ON
(
taskStd.WPROJ_ID = tasks.WPROJ_ID
AND
taskStd.TaskUniqueID = tasks.ENT_TaskUniqueID
--AND
--taskStd.TaskResourceNames LIKE '%' + @resourceName + '%'
)

WHERE (tasks.TaskEnterpriseOutlineCode1ID <> -1)

/*SELECT #myTemp.*, taskCode.OC_NAME as Department FROM #myTemp

INNER JOIN MSP_OUTLINE_CODES taskCode
ON
(
taskCode.CODE_UID = #myTemp.TaskEnterpriseOutlineCode1ID
)*/

SELECT * FROM #myTemp WHERE #myTemp.TaskResourceNames LIKE '%' +
@resourceName + '%'


Thank you!

Chris

DarkHades wrote:
> Hi all, I have a stored proc which returns twice the result and I dont
> know why. Can someone have a look at the following code?
>
> BTW, I commented the last SELECT/JOIN, cause that one doubled the
> result too.
>
> CREATE procedure ent_tasks_per_user_company (
> @companyName as varchar(50),
> @resourceName as varchar(50)
> )
> AS
>
> SELECT
> tasks.WPROJ_ID as WPROJ_ID, tasks.ENT_ProjectUniqueID as
> ProjectUniqueID, tasks.ENT_TaskUniqueID as TaskUniqueID,
> tasks.TaskEnterpriseOutlineCode1ID as TaskEnterpriseOutlineCode1ID,
> codes.OC_NAME as OC_NAME, codes.OC_DESCRIPTION as OC_DESCRIPTION,
> codes.OC_CACHED_FULL_NAME as OC_CACHED_FULL_NAME,
> taskStd.TaskName as TaskName, taskStd.TaskResourceNames as
> TaskResourceNames, taskStd.TaskPercentComplete as TaskPercentComplete
>
> INTO #myTemp
>
> FROM MSP_VIEW_PROJ_TASKS_ENT as tasks
>
> INNER JOIN MSP_OUTLINE_CODES as codes
> ON
> (
> codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1ID
> AND
> codes.OC_CACHED_FULL_NAME LIKE @companyName + '.%'
> )
>
> INNER JOIN MSP_VIEW_PROJ_TASKS_STD as taskStd
> ON
> (
> taskStd.WPROJ_ID = tasks.WPROJ_ID
> AND
> taskStd.TaskUniqueID = tasks.ENT_TaskUniqueID
> --AND
> --taskStd.TaskResourceNames LIKE '%' + @resourceName + '%'
> )
>
> WHERE (tasks.TaskEnterpriseOutlineCode1ID <> -1)
>
> /*SELECT #myTemp.*, taskCode.OC_NAME as Department FROM #myTemp
>
> INNER JOIN MSP_OUTLINE_CODES taskCode
> ON
> (
> taskCode.CODE_UID = #myTemp.TaskEnterpriseOutlineCode1ID
> )*/
>
> SELECT * FROM #myTemp WHERE #myTemp.TaskResourceNames LIKE '%' +
> @resourceName + '%'
>
>
> Thank you!
>
> Chris

Tough to say, especially since we don't know anything whatsoever about
your tables.

What you need to know?

The tables belong to MS Project 2003. You can have projects and Web
project. My application uses web project, but the problem is that web
project must be linked to project to uses Outline Codes (user defined
values). I need to retrieve all the tasks (MSP_VIEW_PROJ_TASKS_STD) for
a specific user (passed as @resourceName) which is stored in that table
in column TaskResourceNames (yes, if there's more resource assigned to
a task, they're all there seperate with a comma). I must retrieve these
data for a specific department which is stored is the
MSP_OUTLINE_CODES.

MSP_OUTLINE_CODES is used to hold departments as well as other
information which is also required in the query.

So basically, I need to get all the tasks from MSP_VIEW_PROJ_TASKS_ENT
different from -1. Then I need to filter the result so that only the
task for a specific enterprise is there (passed as @companyName). That
information is stored in the MSP_OUTLINE_CODES too. But there's more.
In that filtered result, I must look if the resource is assigned to it
(@resourceName) and if so, I must query again MSP_OUTLINE_CODES to get
the department. And then, I must return values from every step.

Maybe I just complicated things, let me know if I need to clarify
things!

Chris


ZeldorBlat wrote:
> DarkHades wrote:
> > Hi all, I have a stored proc which returns twice the result and I dont
> > know why. Can someone have a look at the following code?
> >
> > BTW, I commented the last SELECT/JOIN, cause that one doubled the
> > result too.
> >
> > CREATE procedure ent_tasks_per_user_company (
> > @companyName as varchar(50),
> > @resourceName as varchar(50)
> > )
> > AS
> >
> > SELECT
> > tasks.WPROJ_ID as WPROJ_ID, tasks.ENT_ProjectUniqueID as
> > ProjectUniqueID, tasks.ENT_TaskUniqueID as TaskUniqueID,
> > tasks.TaskEnterpriseOutlineCode1ID as TaskEnterpriseOutlineCode1ID,
> > codes.OC_NAME as OC_NAME, codes.OC_DESCRIPTION as OC_DESCRIPTION,
> > codes.OC_CACHED_FULL_NAME as OC_CACHED_FULL_NAME,
> > taskStd.TaskName as TaskName, taskStd.TaskResourceNames as
> > TaskResourceNames, taskStd.TaskPercentComplete as TaskPercentComplete
> >
> > INTO #myTemp
> >
> > FROM MSP_VIEW_PROJ_TASKS_ENT as tasks
> >
> > INNER JOIN MSP_OUTLINE_CODES as codes
> > ON
> > (
> > codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1ID
> > AND
> > codes.OC_CACHED_FULL_NAME LIKE @companyName + '.%'
> > )
> >
> > INNER JOIN MSP_VIEW_PROJ_TASKS_STD as taskStd
> > ON
> > (
> > taskStd.WPROJ_ID = tasks.WPROJ_ID
> > AND
> > taskStd.TaskUniqueID = tasks.ENT_TaskUniqueID
> > --AND
> > --taskStd.TaskResourceNames LIKE '%' + @resourceName + '%'
> > )
> >
> > WHERE (tasks.TaskEnterpriseOutlineCode1ID <> -1)
> >
> > /*SELECT #myTemp.*, taskCode.OC_NAME as Department FROM #myTemp
> >
> > INNER JOIN MSP_OUTLINE_CODES taskCode
> > ON
> > (
> > taskCode.CODE_UID = #myTemp.TaskEnterpriseOutlineCode1ID
> > )*/
> >
> > SELECT * FROM #myTemp WHERE #myTemp.TaskResourceNames LIKE '%' +
> > @resourceName + '%'
> >
> >
> > Thank you!
> >
> > Chris
>
> Tough to say, especially since we don't know anything whatsoever about
> your tables.

On 15 Aug 2006 11:05:42 -0700, "DarkHades" <hades5k@gmail.com> wrote:

>The tables belong to MS Project 2003. You can have projects and Web
>project. My application uses web project, but the problem is that web
>project must be linked to project to uses Outline Codes (user defined
>values). I need to retrieve all the tasks (MSP_VIEW_PROJ_TASKS_STD) for
>a specific user (passed as @resourceName) which is stored in that table
>in column TaskResourceNames (yes, if there's more resource assigned to
>a task, they're all there seperate with a comma).

Find out whether Project will let you normalize this.

>I must retrieve these
>data for a specific department which is stored is the
>MSP_OUTLINE_CODES.
>
>MSP_OUTLINE_CODES is used to hold departments as well as other
>information which is also required in the query.
>
>So basically, I need to get all the tasks from MSP_VIEW_PROJ_TASKS_ENT
>different from -1. Then I need to filter the result so that only the
>task for a specific enterprise is there (passed as @companyName). That
>information is stored in the MSP_OUTLINE_CODES too. But there's more.
>In that filtered result, I must look if the resource is assigned to it
>(@resourceName) and if so, I must query again MSP_OUTLINE_CODES to get
>the department. And then, I must return values from every step.

The core of your code appears to be as follows:

select <stuff>
into <temp table>
from MSP_VIEW_PROJ_TASKS_ENT as tasks
join MSP_OUTLINE_CODES as codes
on codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1ID
join MSP_VIEW_PROJ_TASKS_STD as taskStd
on taskStd.WPROJ_ID = tasks.WPROJ_ID
and taskStd.TaskUniqueID = tasks.ENT_TaskUniqueID
where <stuff>

What are the primary keys of these three tables?

Show a few rows from each of these three tables, then show the data
that ends up in the temp table based on those rows.