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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply