December 12, 2012 at 10:59 pm
Hi guys plz help
I am getting below error when i include not in (6,8) in my sql statement.
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1003.DetectByProcessID" could not be bound.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col1627'.
If i remove that not in(6,8) line querry is executing succesfully. This issue is not occuring everytime i am getting this issue sometime only. May i know the reason and solution for the same.
SELECT defect.i_project_id AS projectid
, NULL AS phaseid
, MasVerificationRecordUI.detectbyprocessid AS processdisciplineid
, effort.f_effort AS actual
FROM CUSCONFIG.[TrnDefectDetailsUI] AS defect
INNER JOIN CUSCONFIG.MasVerificationRecordUI MasVerificationRecordUI
ON defect.i_verification_record_id = MasVerificationRecordUI.i_verification_record_id AND
defect.i_project_id = MasVerificationRecordUI.i_project_id
AND defect.i_defect_status_id NOT IN ( 6 , 8 ) -----> This not in statement is a problem.
INNER JOIN SCHEMABFS.lean_package_projectdetails proj
ON proj.projectid = defect.i_project_id
INNER JOIN cusconfig.[TrnDefectEffortUI] effort
ON effort.i_defect_detail_ref = defect.i_defect_detail_ref
AND effort.d_entry_date<= proj.ENDDATE
INNER JOIN [CUSCONFIG].[TrnServiceTrackingSystemUI] ServiceTracking
ON DEFECT.I_PROJECT_ID =ServiceTracking.PROJECT_ID and isnull(DWTENABLED,0)=0
INNER JOIN cusconfig.[pcprocesspackagetasksUI] AS task
ON task.taskid = MasVerificationRecordUI.detectbytaskid
INNER JOIN cusconfig.[PCPROCESSPACKAGEWORKFLOWSUI] AS workflow
ON task.workflowid = workflow.workflowid
INNER JOIN cusconfig.[PCPROCESSPACKAGEPHASESUI] AS phase
ON workflow.phaseid = phase.phaseid
December 12, 2012 at 11:30 pm
Shouldnt the NOT IN be in the Where clause as the column you are filtering on is not directly related to the Join.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 16, 2012 at 9:12 pm
Hi vijayarani
I took the liberty of changing the table aliases to a two character abreviation to make the code simple.
I reformatted the select statement. The condition of the table join should be in the ON clause.
Any other filtering, should be in the WHERE clause.
I think there are a total of three conditions that should be in the WHERE clause.
Since no ER Diagram is attached, DWTENABLED should be prefixed with the table alias for cleaner code.
Good luck
-- TSQL Code --
SELECT
d1.i_project_id AS projectid,
NULL AS phaseid,
m1.detectbyprocessid AS processdisciplineid
e1.f_effort AS actual
FROM
CUSCONFIG.[TrnDefectDetailsUI] AS d1
INNER JOIN CUSCONFIG.MasVerificationRecordUI m1
ON d1.i_verification_record_id = m1.i_verification_record_id
AND d1.i_project_id = m1.i_project_id
INNER JOIN SCHEMABFS.lean_package_projectdetails as p1
ON p1.projectid = d1.i_project_id
INNER JOIN cusconfig.[TrnDefectEffortUI] as e1
ON e1.i_defect_detail_ref = d1.i_defect_detail_ref
INNER JOIN [CUSCONFIG].[TrnServiceTrackingSystemUI] as s1
ON d1.I_PROJECT_ID = s1.PROJECT_ID
INNER JOIN cusconfig.[pcprocesspackagetasksUI] AS t1
ON t1.taskid = m1.detectbytaskid
INNER JOIN cusconfig.[PCPROCESSPACKAGEWORKFLOWSUI] AS w1
ON t1.workflowid = w1.workflowid
INNER JOIN cusconfig.[PCPROCESSPACKAGEPHASESUI] AS p1
ON w1.phaseid = h1.phaseid
WHERE
d1.i_defect_status_id NOT IN ( 6 , 8 ) AND
e1.d_entry_date <= p1.ENDDATE AND
isnull(DWTENABLED,0) = 0
John Miner
Crafty DBA
www.craftydba.com
December 16, 2012 at 10:41 pm
Hi John,
Thank you very much, but one more point it is a known issue in SQL.When we use linked server we may get this error.
December 16, 2012 at 11:37 pm
vijayarani87.s (12/16/2012)
Thank you very much, but one more point it is a known issue in SQL.When we use linked server we may get this error.
any reference for this ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 17, 2012 at 1:34 pm
It all depends upon the target database server. http://msdn.microsoft.com/en-us/library/ms188279.aspx
This link has a nice diagram for linked servers. As you can see, the provider can be anything. With each ODBC or OLE DB provider, the syntax is a little different.
I try to stick with ANSI standard SQL when possible to make the query portable.
As for errors, I pointed out three conditions that I think should be move to the WHERE clause.
If you are having issues with a linked server, trace what is going on at the server.
For ODBC, enable logging.
For SQL Server or Oracle, start a trace.
To wrap this up, if it is sporadic issue with target server, look at the server options page. Make sure you understand the options.
http://msdn.microsoft.com/en-us/library/ms186839(v=sql.105).aspx
Also, make sure MSDTC is enabled if you are doing distributed transactions.
http://support.microsoft.com/kb/2027550
Like any normal debugging session, look at your logs. Both Windows & SQL Server during the time when the issue occurs.
Without any more detail such as an error message or id, I am only guessing at what might be the issue.
John
John Miner
Crafty DBA
www.craftydba.com
December 17, 2012 at 2:28 pm
From a query perspective, one should not get in the habit of simply moving a JOIN condition to the WHERE clause. It will only be a matter of time before you move one where it will actually cause a problem. I would suggest using the WITH clause to create CTE's that will use those conditions in a WHERE clause, and then the main join in your query is to the CTE. See the following formatted SQL:
;WITH TRN_DEFECT AS (
SELECT *
FROM CUSCONFIG.[TrnDefectDetailsUI]
WHERE i_defect_status_id NOT IN (6, 8)
),
TRN_SVC_TRACKING AS (
SELECT *
FROM [CUSCONFIG].[TrnServiceTrackingSystemUI]
WHERE DWTENABLED IS NULL
OR DWTENABLED = 0
)
SELECT defect.i_project_id AS projectid,
NULL AS phaseid,
MasVerificationRecordUI.detectbyprocessid AS processdisciplineid,
effort.f_effort AS actual
FROM TRN_DEFECT AS defect
INNER JOIN CUSCONFIG.MasVerificationRecordUI AS MasVerificationRecordUI
ON defect.i_verification_record_id = MasVerificationRecordUI.i_verification_record_id
AND defect.i_project_id = MasVerificationRecordUI.i_project_id
INNER JOIN SCHEMABFS.lean_package_projectdetails AS proj
ON proj.projectid = defect.i_project_id
INNER JOIN cusconfig.[TrnDefectEffortUI] effort
ON effort.i_defect_detail_ref = defect.i_defect_detail_ref
AND effort.d_entry_date <= proj.ENDDATE
INNER JOIN TRN_SVC_TRACKING AS ServiceTracking
ON defect.I_PROJECT_ID =ServiceTracking.PROJECT_ID
INNER JOIN cusconfig.[pcprocesspackagetasksUI] AS task
ON task.taskid = MasVerificationRecordUI.detectbytaskid
INNER JOIN cusconfig.[PCPROCESSPACKAGEWORKFLOWSUI] AS workflow
ON task.workflowid = workflow.workflowid
INNER JOIN cusconfig.[PCPROCESSPACKAGEPHASESUI] AS phase
ON workflow.phaseid = phase.phaseid
This will at least segregate the conditions where they belong and give SQL Server a better opportunity to optimize the query. Also, formatting your queries this way makes it MUCH EASIER to read and understand. Just continuing each line at the beginning of a line makes it hard to see what's going on.
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy