Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Not in sql error


Not in sql error

Author
Message
vijayarani87.s
vijayarani87.s
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 137
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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
j.miner
j.miner
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 358
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
vijayarani87.s
vijayarani87.s
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 137
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.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
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;-)
j.miner
j.miner
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 358
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
sgmunson
sgmunson
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2853 Visits: 3765
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)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search