|
|
You're in the
sql server programming
group:EXISTS should be same as IN but isn't
sql server programming:
rows with a non-null clnGUID. The second WHERE clause may or may not be true, depending on whether or not the 66 rows returned contain the clnGUID from the main query. I would expect you'll get the same results if you remove TOP from both queries or if the table contains 66 or fewer rows. Note that TOP is meaningless with EXISTS. Furthermore, the rows returned with TOP are arbitrary unless ORDER BY is also specified. -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "DC Gringo" <dcgringo@visiontechnology.net> wrote in message news:%23gEPiEPqEHA.1668@TK2MSFTNGP14.phx.gbl... > These two queries should yield the same results but they don't...can > someone > tell me why? > > > SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP > FROM vwSHAs v1 > WHERE EXISTS > (select top 66 clnGUID > FROM vwSHAs v2 > WHERE v1.clnGUID = v2.clnGUID > ) > > > > SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP > FROM vwSHAs > WHERE clnGUID in > (select top 66 clnGUID > FROM vwSHAs > > ) > > > > > -- > _____ > DC G > >
These two queries should yield the same results but they don't...can someone
tell me why? SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP FROM vwSHAs v1 WHERE EXISTS (select top 66 clnGUID FROM vwSHAs v2 WHERE v1.clnGUID = v2.clnGUID ) SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP FROM vwSHAs WHERE clnGUID in (select top 66 clnGUID FROM vwSHAs ) -- _____ DC G
Can you please post DDL (CREATE TABLE) and sample data (INSERT statements)
that demonstrate the problem? It seems to me the EXISTS is not needed here: SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP FROM vwSHAs WHERE DistanceFromCRP >= 0 AND ( Marking IN ('OfficialSigns') OR Marking is NULL ) AND ( OtherWaterUses Like '%Yes%' ) -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "DC Gringo" <dcgringo@visiontechnology.net> wrote in message news:Ok4X7fPqEHA.556@tk2msftngp13.phx.gbl... > Dan, I was following instructions from Help. I'm actually not using the > TOP > 66, I'm using other criteria and just chose that as an example. > > Here are my really queries stripped down... > > I need to use EXISTS rather than IN because the query within the WHERE > clause is being dynamically built using a variable in my code that is > SELECTing more than one column. There are two queries below, can you help > me get the first one to return the same as the second one would? > > Query 1 using EXISTS > ------------------------ > SELECT distinct clnGUID = '', > Sum(DistanceFromCRP) as DistanceFromCRP > FROM vwSHAs > WHERE EXISTS ( > SELECT * > FROM vwSHAs > WHERE DistanceFromCRP >= 0 > AND ( Marking IN ('OfficialSigns') OR Marking is NULL ) > AND ( OtherWaterUses Like '%Yes%' ) > AND clnGUID = vwSHAs.clnGUID) > > > Query 2 using IN > ------------------------- > SELECT distinct clnGUID = '', > Sum(DistanceFromCRP) as DistanceFromCRP > FROM vwSHAs > WHERE clnGUID IN ( > SELECT clnGUID > FROM vwSHAs > WHERE DistanceFromCRP >= 0 > AND ( Marking IN ('OfficialSigns') OR Marking is NULL ) > AND ( OtherWaterUses Like '%Yes%' ) ) > > > _____ > DC G > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:eFacxSPqEHA.3848@TK2MSFTNGP14.phx.gbl... >> These queries are not the same. The first WHERE clause will qualify all >> rows with a non-null clnGUID. The second WHERE clause may or may not be >> true, depending on whether or not the 66 rows returned contain the >> clnGUID >> from the main query. I would expect you'll get the same results if you >> remove TOP from both queries or if the table contains 66 or fewer rows. >> >> Note that TOP is meaningless with EXISTS. Furthermore, the rows returned >> with TOP are arbitrary unless ORDER BY is also specified. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "DC Gringo" <dcgringo@visiontechnology.net> wrote in message >> news:%23gEPiEPqEHA.1668@TK2MSFTNGP14.phx.gbl... >> > These two queries should yield the same results but they don't...can >> > someone >> > tell me why? >> > >> > >> > SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP >> > FROM vwSHAs v1 >> > WHERE EXISTS >> > (select top 66 clnGUID >> > FROM vwSHAs v2 >> > WHERE v1.clnGUID = v2.clnGUID >> > ) >> > >> > >> > >> > SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP >> > FROM vwSHAs >> > WHERE clnGUID in >> > (select top 66 clnGUID >> > FROM vwSHAs >> > >> > ) >> > >> > >> > >> > >> > -- >> > _____ >> > DC G >> > >> > >> >> > >
Dan, I was following instructions from Help. I'm actually not using the TOP
66, I'm using other criteria and just chose that as an example. Here are my really queries stripped down... I need to use EXISTS rather than IN because the query within the WHERE clause is being dynamically built using a variable in my code that is SELECTing more than one column. There are two queries below, can you help me get the first one to return the same as the second one would? Query 1 using EXISTS ------------------------ SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP FROM vwSHAs WHERE EXISTS ( SELECT * FROM vwSHAs WHERE DistanceFromCRP >= 0 AND ( Marking IN ('OfficialSigns') OR Marking is NULL ) AND ( OtherWaterUses Like '%Yes%' ) AND clnGUID = vwSHAs.clnGUID) Query 2 using IN ------------------------- SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP FROM vwSHAs WHERE clnGUID IN ( SELECT clnGUID FROM vwSHAs WHERE DistanceFromCRP >= 0 AND ( Marking IN ('OfficialSigns') OR Marking is NULL ) AND ( OtherWaterUses Like '%Yes%' ) ) _____ DC G [quoted text, click to view] "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:eFacxSPqEHA.3848@TK2MSFTNGP14.phx.gbl... > These queries are not the same. The first WHERE clause will qualify all > rows with a non-null clnGUID. The second WHERE clause may or may not be > true, depending on whether or not the 66 rows returned contain the clnGUID > from the main query. I would expect you'll get the same results if you > remove TOP from both queries or if the table contains 66 or fewer rows. > > Note that TOP is meaningless with EXISTS. Furthermore, the rows returned > with TOP are arbitrary unless ORDER BY is also specified. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "DC Gringo" <dcgringo@visiontechnology.net> wrote in message > news:%23gEPiEPqEHA.1668@TK2MSFTNGP14.phx.gbl... > > These two queries should yield the same results but they don't...can > > someone > > tell me why? > > > > > > SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP > > FROM vwSHAs v1 > > WHERE EXISTS > > (select top 66 clnGUID > > FROM vwSHAs v2 > > WHERE v1.clnGUID = v2.clnGUID > > ) > > > > > > > > SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP > > FROM vwSHAs > > WHERE clnGUID in > > (select top 66 clnGUID > > FROM vwSHAs > > > > ) > > > > > > > > > > -- > > _____ > > DC G > > > > > >
When I try to run the queries you last provided against these tables and
view, I get the following errors: Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'DistanceFromCRP'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'DistanceFromCRP'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'Marking'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'Marking'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'OtherWaterUses'. These errors aside, I would expect that both those queries would return the same results. Now that I've got the schema, can you provide the actual queries and sample data that repro your problem? A tool to help you generate an insert script is available at http://vyaskn.tripod.com/code/generate_inserts.txt. You can obfuscate the data, if desired. -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "DC Gringo" <dcgringo@visiontechnology.net> wrote in message
news:%230OZo3PqEHA.2900@TK2MSFTNGP12.phx.gbl... > It's needed because I'm building a huge dynamic query and needing to use > the > where within the exists statement such as: > > _sqlStmtLbSHAsSum = "SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as > DistanceFromCRP FROM vwSHAs WHERE EXISTS (" & _sqlStmtLbSHAs & " AND > clnGUID = vwSHAs.clnGUID)" > > The _sqlStmtLbSHAs variable has the "huge" multi-column select statement. > It's "huge" only in the amount of code that goes into building it. > > The vwSHAs that you see there is a view that looks like this: > > SELECT > v.clnGUID, > v.clnID, > v.clnSexTC as Sex, > v.clnAgeTC as Age, > v.clnActivitySubTC as Activity, > v.clnOccupationTC as Occupation, > CASE WHEN v.clnWoundAmputation = 1 > Then '<STRONG>Yes</STRONG>' > WHEN v.clnWoundAmputation = 0 > Then 'No' > Else 'No' > END AS WoundAmputation, > CASE WHEN v.clnWoundLossOfSight = 1 > Then '<STRONG>Yes</STRONG>' > WHEN v.clnWoundLossOfSight = 0 > Then 'No' > Else 'No' > END as SightLoss, > CASE WHEN v.clnWoundOther = 1 > Then '<STRONG>Yes</STRONG>' > WHEN v.clnWoundOther = 0 > Then 'No' > Else 'No' > END AS WoundOther, > CASE WHEN v.clnWoundFatal = 1 > Then '<STRONG>Yes</STRONG>' > WHEN v.clnWoundFatal = 0 > Then 'No' > Else 'No' > END as WoundFatal, > > > tblSurvey1MinedArea.clnSurveyGUID > > FROM tblSurvey1MinedAreaVictim v > INNER JOIN tblSurvey1MinedArea ON v.clnMinedAreaGUID = > tblSurvey1MinedArea.clnGUID > > > > Here are the underlying tables: > > CREATE TABLE [tblSurvey1MinedAreaVictim] ( > [clnGUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnMAC_GUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnID] [int] NULL , > [clnMinedAreaGUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnDetonationDate] [smalldatetime] NULL , > [clnName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnFirstName1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > [clnFirstName2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > [clnKnownDangerousArea] [bit] NOT NULL , > [clnSexTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnAgeTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnOccupationTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL > , > [clnOccupationOther] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnActivityMainTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnActivitySubTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnWoundFatal] [bit] NOT NULL , > [clnWoundAmputation] [bit] NOT NULL , > [clnWoundLossOfSight] [bit] NOT NULL , > [clnWoundOther] [bit] NOT NULL , > [clnWoundUnknown] [bit] NOT NULL , > [clnCareEmergency] [bit] NOT NULL , > [clnCareRehab] [bit] NOT NULL , > [clnCareVocational] [bit] NOT NULL , > [clnCareOther] [bit] NOT NULL , > [clnCareNone] [bit] NOT NULL , > [clnCareFatal] [bit] NOT NULL , > [clnCareUnknown] [bit] NOT NULL , > [clnMV_UserDefined1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined5] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnMV_UserDefined6] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined7] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined8] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined9] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined10] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined11] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined12] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined13] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined14] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined15] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined16] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined17] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined18] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined19] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnMV_UserDefined20] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnUserName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnTimeStamp] [smalldatetime] NULL , > [clnActivity] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnOwnerMAC_GUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [clnOwnerChanged] [bit] NOT NULL , > [clnDestinationMAC_GUID] [nvarchar] (38) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > GO > > > > > CREATE TABLE [tblSurvey1MinedArea] ( > [clnGUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnMAC_GUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnID] [int] NULL , > [clnSurveyGUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > [clnIdentifier] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > [clnName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnMapCoordinateSystemTC] [nvarchar] (25) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [clnMeasuredFromViewPoint] [bit] NOT NULL , > [clnViewPointLongitude] [float] NULL ,
It's needed because I'm building a huge dynamic query and needing to use the
where within the exists statement such as: _sqlStmtLbSHAsSum = "SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP FROM vwSHAs WHERE EXISTS (" & _sqlStmtLbSHAs & " AND clnGUID = vwSHAs.clnGUID)" The _sqlStmtLbSHAs variable has the "huge" multi-column select statement. It's "huge" only in the amount of code that goes into building it. The vwSHAs that you see there is a view that looks like this: SELECT v.clnGUID, v.clnID, v.clnSexTC as Sex, v.clnAgeTC as Age, v.clnActivitySubTC as Activity, v.clnOccupationTC as Occupation, CASE WHEN v.clnWoundAmputation = 1 Then '<STRONG>Yes</STRONG>' WHEN v.clnWoundAmputation = 0 Then 'No' Else 'No' END AS WoundAmputation, CASE WHEN v.clnWoundLossOfSight = 1 Then '<STRONG>Yes</STRONG>' WHEN v.clnWoundLossOfSight = 0 Then 'No' Else 'No' END as SightLoss, CASE WHEN v.clnWoundOther = 1 Then '<STRONG>Yes</STRONG>' WHEN v.clnWoundOther = 0 Then 'No' Else 'No' END AS WoundOther, CASE WHEN v.clnWoundFatal = 1 Then '<STRONG>Yes</STRONG>' WHEN v.clnWoundFatal = 0 Then 'No' Else 'No' END as WoundFatal, tblSurvey1MinedArea.clnSurveyGUID FROM tblSurvey1MinedAreaVictim v INNER JOIN tblSurvey1MinedArea ON v.clnMinedAreaGUID = tblSurvey1MinedArea.clnGUID Here are the underlying tables: CREATE TABLE [tblSurvey1MinedAreaVictim] ( [clnGUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMAC_GUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnID] [int] NULL , [clnMinedAreaGUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnDetonationDate] [smalldatetime] NULL , [clnName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnFirstName1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnFirstName2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnKnownDangerousArea] [bit] NOT NULL , [clnSexTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnAgeTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnOccupationTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnOccupationOther] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnActivityMainTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnActivitySubTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnWoundFatal] [bit] NOT NULL , [clnWoundAmputation] [bit] NOT NULL , [clnWoundLossOfSight] [bit] NOT NULL , [clnWoundOther] [bit] NOT NULL , [clnWoundUnknown] [bit] NOT NULL , [clnCareEmergency] [bit] NOT NULL , [clnCareRehab] [bit] NOT NULL , [clnCareVocational] [bit] NOT NULL , [clnCareOther] [bit] NOT NULL , [clnCareNone] [bit] NOT NULL , [clnCareFatal] [bit] NOT NULL , [clnCareUnknown] [bit] NOT NULL , [clnMV_UserDefined1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined5] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined6] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined7] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined8] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined9] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined10] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined11] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined12] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined13] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined14] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined15] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined16] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined17] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined18] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined19] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMV_UserDefined20] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnUserName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnTimeStamp] [smalldatetime] NULL , [clnActivity] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnOwnerMAC_GUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnOwnerChanged] [bit] NOT NULL , [clnDestinationMAC_GUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [tblSurvey1MinedArea] ( [clnGUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMAC_GUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnID] [int] NULL , [clnSurveyGUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnIdentifier] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMapCoordinateSystemTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMeasuredFromViewPoint] [bit] NOT NULL , [clnViewPointLongitude] [float] NULL , [clnViewPointLatitude] [float] NULL , [clnBearing] [real] NULL , [clnDistance] [real] NULL , [clnStartPointLongitude] [float] NULL , [clnStartPointLatitude] [float] NULL , [clnCoordinatesVisuallyVerified] [bit] NOT NULL , [clnWalkTime] [real] NULL , [clnDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnMarkingTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnEstimatedArea] [real] NULL , [clnWasFightingTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [clnFieldCroptype] [bit] NOT NULL , [clnFieldPasturetype] [bit] NOT NULL , [clnFieldIrrigated] [bit] NOT NULL , [clnFieldRainfed] [bit] NOT NULL , [clnFieldGrain] [bit] NOT NULL , [clnFieldFruit] [bit] NOT NULL , [clnFieldVegetable] [bit] NOT NULL , [clnFieldOther] [bit] NOT NULL , [clnFieldUnknown] [bit] NOT NULL , [clnFieldPastureFixed] [bit] NOT NULL , [clnFieldPastureMigratory] [bit] NOT NULL , [clnPastureCattleCamel] [bit] NOT NULL , [clnPastureGoatSheep] [bit] NOT NULL , [clnPastureOther] [bit] NOT NULL , [clnPastureUnknown] [bit] NOT NULL , [clnWaterIrrigation] [bit] NOT NULL , [clnWaterFishing] [bit] NOT NULL , [clnWaterAnimals] [bit] NOT NULL , [clnWaterBathing] [bit] NOT NULL ,
Huge dynamic queries are no surprise as a source of problems. Have you
looked at the query strings you're executing? If I had to guess, I'd say your "huge" variable is a query whose filter contains OR in it, so that your two queries are something like this: SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP FROM vwSHAs v1 WHERE EXISTS ( select * FROM vwSHAs where condition1 or condition2 and v1.clnGUID = v2.clnGUID ) -- i.e., where (condition1) or (condition2 and v1.clnGUID = v2.clnGUID) SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as DistanceFromCRP FROM vwSHAs WHERE clnGUID in ( select clnGUID FROM vwSHAs where condition1 or condition2 ) The first of these will return a sum that includes rows whose clnGUID value is not in vwSHAs, or not in a row meeting condition1 or condition2. The second of these will return a sum that does not include such rows. Whether or not this is what's going on, my advice is first to think carefully before using dynamic SQL. In many of the rare cases where it can't be avoided, a design flaw is the reason it can't be avoided. (Your underlying tables are very badly flawed, but you may have no recourse there.) When you must use dynamic SQL, print the queries before you execute them so you can see if they are what you intend. And always be aware of the risks, particularly when user input (either direct or through the inclusion of names of system objects users created) is incorporated ino the query. If you can pass user input and object names as parameters to sp_executesql instead of execute(), do so. See http://www.sommarskog.se/dynamic_sql.html. Steve Kass Drew University [quoted text, click to view] DC Gringo wrote:
>It's needed because I'm building a huge dynamic query and needing to use the >where within the exists statement such as: > >_sqlStmtLbSHAsSum = "SELECT distinct clnGUID = '', Sum(DistanceFromCRP) as >DistanceFromCRP FROM vwSHAs WHERE EXISTS (" & _sqlStmtLbSHAs & " AND >clnGUID = vwSHAs.clnGUID)" > >The _sqlStmtLbSHAs variable has the "huge" multi-column select statement. >It's "huge" only in the amount of code that goes into building it. > >The vwSHAs that you see there is a view that looks like this: > >SELECT > v.clnGUID, > v.clnID, > v.clnSexTC as Sex, > v.clnAgeTC as Age, > v.clnActivitySubTC as Activity, > v.clnOccupationTC as Occupation, > CASE WHEN v.clnWoundAmputation = 1 > Then '<STRONG>Yes</STRONG>' > WHEN v.clnWoundAmputation = 0 > Then 'No' > Else 'No' > END AS WoundAmputation, > CASE WHEN v.clnWoundLossOfSight = 1 > Then '<STRONG>Yes</STRONG>' > WHEN v.clnWoundLossOfSight = 0 > Then 'No' > Else 'No' > END as SightLoss, > CASE WHEN v.clnWoundOther = 1 > Then '<STRONG>Yes</STRONG>' > WHEN v.clnWoundOther = 0 > Then 'No' > Else 'No' > END AS WoundOther, > CASE WHEN v.clnWoundFatal = 1 > Then '<STRONG>Yes</STRONG>' > WHEN v.clnWoundFatal = 0 > Then 'No' > Else 'No' > END as WoundFatal, > > > tblSurvey1MinedArea.clnSurveyGUID > >FROM tblSurvey1MinedAreaVictim v > INNER JOIN tblSurvey1MinedArea ON v.clnMinedAreaGUID = >tblSurvey1MinedArea.clnGUID > > > >Here are the underlying tables: > >CREATE TABLE [tblSurvey1MinedAreaVictim] ( > [clnGUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnMAC_GUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnID] [int] NULL , > [clnMinedAreaGUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnDetonationDate] [smalldatetime] NULL , > [clnName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnFirstName1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnFirstName2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnKnownDangerousArea] [bit] NOT NULL , > [clnSexTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnAgeTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnOccupationTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL >, > [clnOccupationOther] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnActivityMainTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnActivitySubTC] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnWoundFatal] [bit] NOT NULL , > [clnWoundAmputation] [bit] NOT NULL , > [clnWoundLossOfSight] [bit] NOT NULL , > [clnWoundOther] [bit] NOT NULL , > [clnWoundUnknown] [bit] NOT NULL , > [clnCareEmergency] [bit] NOT NULL , > [clnCareRehab] [bit] NOT NULL , > [clnCareVocational] [bit] NOT NULL , > [clnCareOther] [bit] NOT NULL , > [clnCareNone] [bit] NOT NULL , > [clnCareFatal] [bit] NOT NULL , > [clnCareUnknown] [bit] NOT NULL , > [clnMV_UserDefined1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined5] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnMV_UserDefined6] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined7] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined8] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined9] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined10] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined11] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined12] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined13] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined14] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined15] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined16] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined17] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined18] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined19] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnMV_UserDefined20] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnUserName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnTimeStamp] [smalldatetime] NULL , > [clnActivity] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [clnOwnerMAC_GUID] [nvarchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS >NULL , > [clnOwnerChanged] [bit] NOT NULL , > [clnDestinationMAC_GUID] [nvarchar] (38) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL
[quoted text, click to view]
On Sat, 2 Oct 2004 23:20:44 -0400, DC Gringo wrote: >Query 1 using EXISTS >------------------------ >SELECT distinct clnGUID = '', >Sum(DistanceFromCRP) as DistanceFromCRP >FROM vwSHAs >WHERE EXISTS ( > SELECT * > FROM vwSHAs > WHERE DistanceFromCRP >= 0 > AND ( Marking IN ('OfficialSigns') OR Marking is NULL ) > AND ( OtherWaterUses Like '%Yes%' ) > AND clnGUID = vwSHAs.clnGUID) Hi DC, It seems you are missing a table alias in this query. The last line: "AND clnGUID - vwSHAs.clnGUID will always be true, as both the unprefixed clnGUID and the prefixed vwSHAs.clnGUID refer to the version of vwSHAs in the subquery - I think you want one of them to refer back to the outer queries version of vwSHAs. This view will return the sum of DistanceFromCRP of *all* rows in vwSHAs in at least one row in vwSHAs matches the criteria in the subquery (DistanceFromCRP >= 0 AND (Marking = 'OfficialSigns' OR Marking IS NULL) AND OtherWaterUses like '%Yes%'). It will return NULL if no row matches this condition. [quoted text, click to view] >Query 2 using IN >------------------------- >SELECT distinct clnGUID = '', >Sum(DistanceFromCRP) as DistanceFromCRP >FROM vwSHAs >WHERE clnGUID IN ( > SELECT clnGUID > FROM vwSHAs > WHERE DistanceFromCRP >= 0 > AND ( Marking IN ('OfficialSigns') OR Marking is NULL ) > AND ( OtherWaterUses Like '%Yes%' ) ) You should get the same result from the EXISTS version if you start using table aliases to ensure that the subquery references the outer query. Best, Hugo --
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |