SQL Apprentice Question
I have the following procedure returning twice the result, doubling it,
whatever. I think it's the way I'm using the join, but I have no idea
how to solve my problem. I commented the last select/join as that was
doubling the result too.
Thanks for your patience.
BTW, this is a repost, I changed the title as the other one was not
Here's the code :
CREATE procedure ent_tasks_per_user_company (
@companyName as varchar(50),
@resourceName as varchar(50)
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
FROM MSP_VIEW_PROJ_TASKS_ENT as tasks
INNER JOIN MSP_OUTLINE_CODES as codes
codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1ID
codes.OC_CACHED_FULL_NAME LIKE @companyName + '.%'
INNER JOIN MSP_VIEW_PROJ_TASKS_STD as taskStd
taskStd.WPROJ_ID = tasks.WPROJ_ID
taskStd.TaskUniqueID = tasks.ENT_TaskUniqueID
--taskStd.TaskResourceNames LIKE '%' + @resourceName + '%'
WHERE (tasks.TaskEnterpriseOutlineCode1ID <> -1)
/*SELECT #myTemp.*, taskCode.OC_NAME as Department FROM #myTemp
INNER JOIN MSP_OUTLINE_CODES taskCode
taskCode.CODE_UID = #myTemp.TaskEnterpriseOutlineCode1ID
SELECT * FROM #myTemp WHERE #myTemp.TaskResourceNames LIKE '%' +
@resourceName + '%'
For a particular employee, I should have only 1 result, but I got 2...
and its the exact same thing.
Why did you use VARCHAR(50)? Can you give an example you found in your
research of a company name that long? Why did you use text at all
instead of a DUNS or other industry standard?
Can you give an example of a non-unique id, since by definition an
identifier has to be unique? Why did you alias columns back to their
original names? All I can think of is that you like redundancy.
Why do you have double underscores in a name? That is a horrible
practice in any language. It invites typos, it is hard to see on a
laser printer or screen, etc.
Why did you load a temp table instead of using a query expression as
the body of the procedure? I know that this is to mimic a scratch tape
in a 1950's file system in which you had to materialize all the data.
Welcome to SQL and virtual data.
Why did you violate ISO-11179 and use VIEW in data element names?
Actually, you have some other violations, too.
There is no such thing as a code_id; a data element can be one or the
other, but never both by definition. Can I assume that "_code1" is
a meaningful attribute qualifier in your industry? Or is it a bad name
from a repeating group in the table?
I also see that you have a data element called "taskresourcenames",
but since it is a column, it cannot be plural. They model scalar
Surely you did not violate 1NF and cram a list into a column!! But who
knows, since you never posted DDL or sample data. That could be the
cause of duplication.
Finally, the last LIKE predicate is going to be a bit slow. Here is a
clean up on the code, taking it from OO and file system thinking into
CREATE PROCEDURE Ent_T_Per_User_Company
(@my_company_name AS VARCHAR(50), -- careful research??
@my_resource_name AS VARCHAR(50))
SELECT T.wproj_id, T.ent_project_id,
FROM MSP_Proj_T_Ent AS T,
MSP_Outline_Codes AS C,
MSP_Proj_T_Std AS TS
WHERE C.code = T.task_enterprise_outline_code1
LIKE @my_company_name + '.%'
AND TS.wproj_id = T.wproj_id
AND TS.task_id = T.ent_task_id
AND T.task_enterprise_outline_code1 <> -1)
LIKE '%' +@my_resource_name +'%';
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us