Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not in sql error Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 10:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:40 AM
Points: 52, Visits: 136
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
Post #1395994
Posted Wednesday, December 12, 2012 11:30 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1396001
Posted Sunday, December 16, 2012 9:12 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 11:01 AM
Points: 80, Visits: 347
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
Post #1397071
Posted Sunday, December 16, 2012 10:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:40 AM
Points: 52, Visits: 136
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.
Post #1397090
Posted Sunday, December 16, 2012 11:37 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
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
Post #1397099
Posted Monday, December 17, 2012 1:34 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 11:01 AM
Points: 80, Visits: 347
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
Post #1397409
Posted Monday, December 17, 2012 2:28 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:26 PM
Points: 1,616, Visits: 2,119
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)

Internet ATM Machine
Post #1397432
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse