Databases
StackOverflow2013
Postsdbo
POExtremely ugly query issue
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExtremely ugly query issue
    primarykey
    data
    text
    <p>If anyone can help me on this I would be very excited...I've already spent about 4 hours and cannot find out why T-SQL gives me wrong results and the same query in sql is fine(I mean only @ZipCodeIDS is not null, that's why I put null for the rest of vars in plain sql).</p> <p>T-SQL Query:</p> <pre><code>-- validate the reference number pattern DECLARE @PCodePattern nvarchar(130) IF @PCode = '' SET @PCode = NULL IF @PCode IS NOT NULL BEGIN IF LTRIM(RTRIM(@PCode)) &lt;&gt; '' BEGIN -- filter by pattern SELECT @PCodePattern = @PCode END END -- @PCode if (@strAddress is not null) set @strAddress = '%' + @strAddress + '%' Declare @listofIDS table(zipcodeids int) delete from @listofIDS IF @ZipCodeIDS = '' SET @ZipCodeIDS = NULL IF @ZipCodeIDS IS NOT NULL BEGIN IF CHARINDEX(',', @ZipCodeIDS) = 0 BEGIN insert @listofIDS values(@ZipCodeIDS) END ELSE BEGIN set @ZipCodeIDS = @ZipCodeIDS + ',' WHILE CHARINDEX(',', @ZipCodeIDS) &lt;&gt; 0 BEGIN insert @listofIDS values(Left(@ZipCodeIDS, CHARINDEX(',',@ZipCodeIDS) - 1)) SET @ZipCodeIDS = SUBSTRING(@ZipCodeIDS, CHARINDEX(',',@ZipCodeIDS) + 1, LEN(@ZipCodeIDS) - CHARINDEX(',',@ZipCodeIDS)) END END END -- select the property data INSERT INTO @PropertyDetails (PropertyID, PCode, PropertyStatusID, PropertyStatusName, PropertyTypeID, PropertyTypeName, ResearchStatusID, ResearchStatusName, FullAddress, PartialAddress, Address1, Address2, ZipCodeID, ZipCode, ZipCodeDescription, CityID, CityName, StateID, StateName, StateCode, NumBedrooms, NumBathrooms, LivingSquareFeet, LotSquareFeet, YearBuilt, ZillowLink, AssessorParcelNumber, DateWentREO, DateAppearedOnMLS, IsOnTheMLS, ZPropertyID, LowestPrice, HighestPrice, AskingPrice, DateTimeRecorded, RecordedByPersonID, RecordedByPersonName, AssignedToPersonID, AssignedToPersonName, WatchTag, Latitude, Longitude) SELECT p.PropertyID, p.PCode, p.PropertyStatusID, ps.Name, p.PropertyTypeID, pt.Name, p.ResearchStatusID, rs.Name, dbo.GetAddress(p.PropertyID), dbo.GetPartialAddress(p.PropertyID), p.Address1, p.Address2, p.ZipCodeID, z.Code, z.Description, p.CityID, c.Name, p.StateID, s.Name, s.Code, p.NumBedrooms, p.NumBathrooms, p.LivingSquareFeet, p.LotSquareFeet, p.YearBuilt, p.ZillowLink, p.AssessorParcelNumber, p.DateWentREO, p.DateAppearedOnMLS, p.IsOnTheMLS, p.ZPropertyID, p.LowestPrice, p.HighestPrice, p.AskingPrice, p.DateTimeRecorded, p.RecordedByPersonID, dbo.GetDisplayName(p.RecordedByPersonID), p.AssignedToPersonID, dbo.GetDisplayName(p.AssignedToPersonID), w.WatchTag, p.latitude, p.longitude FROM Properties p JOIN cfgPropertyStatuses ps ON ps.PropertyStatusID = p.PropertyStatusID JOIN cfgPropertyTypes pt ON pt.PropertyTypeID = p.PropertyTypeID JOIN cfgResearchStatuses rs ON rs.ResearchStatusID = p.ResearchStatusID JOIN ZipCodes z ON z.ZipCodeID = p.ZipCodeID JOIN cfgStates s ON s.StateID = p.StateID LEFT JOIN cfgCities c ON c.CityID = p.CityID LEFT JOIN Watches w ON w.PropertyID = p.PropertyID AND w.PersonID = @LoggedInPersonID WHERE /* ******* missing filter ******* this line should filter the risks by @LoggedInPersonID via role ****************************** AND */(@PropertyID IS NULL OR p.PropertyID = @PropertyID) AND (@PCodePattern IS NULL OR p.PCode LIKE @PCodePattern) AND (@ZipCodeIDS IS NULL OR p.ZipCodeID IN (select zipcodeids from @listofIDS)) AND (@NumBedroomsFrom IS NULL OR (p.NumBedrooms &gt;= @NumBedroomsFrom AND @NumBedroomsTo IS NOT NULL AND p.NumBedrooms &lt;= @NumBedroomsTo) OR (p.NumBedrooms = @NumBedroomsFrom AND @NumBedroomsTo IS NULL)) AND (@NumBedroomsTo IS NULL OR (p.NumBedrooms &lt;= @NumBedroomsTo AND (@NumBedroomsTo IS NULL OR p.NumBedrooms &lt;= @NumBedroomsTo))) AND (@LivingSizeFrom IS NULL OR (p.LivingSquareFeet &gt;= @LivingSizeFrom)) AND (@LivingSizeTo IS NULL OR (p.LivingSquareFeet &lt;= @LivingSizeTo)) AND (@LotSizeFrom IS NULL OR (p.LotSquareFeet &gt;= @LotSizeFrom)) AND (@LotSizeTo IS NULL OR (p.LotSquareFeet &lt;= @LotSizeTo)) AND /* if status is null, return all. Or, return only statuses that are passed in */ (@PropertyStatuses IS NULL or ((p.PropertyStatusID=@PropertyStatuses and (p.PropertyStatusID &amp; (32 | 128)) = 0) or @PropertyID is not null or @PCode is not null) or (p.PropertyStatusID = (p.PropertyStatusID &amp; @PropertyStatuses))) /* -- return the property if the specific ID was given otherwise ommit Sold and Archived AND ((p.PropertyStatusID &amp; (32 /*sold*/ | 128 /*archived*/)) = 0 OR @PropertyID IS NOT NULL OR @PCode IS NOT NULL)) OR (p.PropertyStatusID = (p.PropertyStatusID &amp; @PropertyStatuses))) */ AND (@PropertyTypes IS NULL OR (p.PropertyTypeID = (p.PropertyTypeID &amp; @PropertyTypes))) AND (@ResearchStatuses IS NULL OR (p.ResearchStatusID = (p.ResearchStatusID &amp; @ResearchStatuses))) AND (@IsOnTheMLS IS NULL OR p.IsOnTheMLS = @IsOnTheMLS) and (@strAddress is null or (p.Address1 LIKE @strAddress or p.Address2 LIKE @strAddress)) RETURN </code></pre> <p>and the same, translated by me in SQL (which works good):</p> <pre><code> /****** Script for SelectTopNRows command from SSMS ******/ SELECT TOP 1000 [PropertyID] ,[PCode] ,[Address1] ,[Address2] ,[NumBedrooms] ,[NumBathrooms] ,[LivingSquareFeet] ,[LotSquareFeet] ,[YearBuilt] ,[ZillowLink] ,[AssessorParcelNumber] ,[DateWentREO] ,[DateAppearedOnMLS] ,[IsOnTheMLS] ,[ZPropertyID] ,[LowestPrice] ,[HighestPrice] ,[AskingPrice] ,[DateTimeRecorded] ,[RecordedByPersonID] ,[AssignedToPersonID] ,[latitude] ,[longitude] ,[Zestimate] FROM [dev_hotsheetDB].[dbo].[Properties] p JOIN [dev_hotsheetDB].[dbo].cfgPropertyStatuses ps ON ps.PropertyStatusID = p.PropertyStatusID JOIN [dev_hotsheetDB].[dbo].cfgPropertyTypes pt ON pt.PropertyTypeID = p.PropertyTypeID JOIN [dev_hotsheetDB].[dbo].cfgResearchStatuses rs ON rs.ResearchStatusID = p.ResearchStatusID JOIN [dev_hotsheetDB].[dbo].ZipCodes z ON z.ZipCodeID = p.ZipCodeID JOIN [dev_hotsheetDB].[dbo].cfgStates s ON s.StateID = p.StateID LEFT JOIN [dev_hotsheetDB].[dbo].cfgCities c ON c.CityID = p.CityID where (NULL IS NULL OR p.PropertyID = NULL) AND (NULL IS NULL OR p.PCode LIKE NULL) AND ('1' IS NULL OR p.ZipCodeID IN (select zipcodeids from [dev_hotsheetDB].[dbo].[listofIDS])) AND (NULL IS NULL OR (p.NumBedrooms &gt;= NULL AND NULL IS NOT NULL AND p.NumBedrooms &lt;= NULL) OR (p.NumBedrooms = NULL AND NULL IS NULL)) AND (NULL IS NULL OR (p.NumBedrooms &lt;= NULL AND (NULL IS NULL OR p.NumBedrooms &lt;= NULL))) AND (NULL IS NULL OR (p.LivingSquareFeet &gt;= NULL)) AND (NULL IS NULL OR (p.LivingSquareFeet &lt;= NULL)) AND (NULL IS NULL OR (p.LotSquareFeet &gt;= NULL)) AND (NULL IS NULL OR (p.LotSquareFeet &lt;= NULL)) AND /* if status is null, return all. Or, return only statuses that are passed in */ (NULL IS NULL or ((p.PropertyStatusID=NULL and (p.PropertyStatusID &amp; (32 | 128)) = 0) or NULL is not null or NULL is not null) or (p.PropertyStatusID = (p.PropertyStatusID &amp; NULL))) /* -- return the property if the specific ID was given otherwise ommit Sold and Archived AND ((p.PropertyStatusID &amp; (32 /*sold*/ | 128 /*archived*/)) = 0 OR @PropertyID IS NOT NULL OR @PCode IS NOT NULL)) OR (p.PropertyStatusID = (p.PropertyStatusID &amp; @PropertyStatuses))) */ AND (NULL IS NULL OR (p.PropertyTypeID = (p.PropertyTypeID &amp; NULL))) AND (NULL IS NULL OR (p.ResearchStatusID = (p.ResearchStatusID &amp; NULL))) AND (NULL IS NULL OR p.IsOnTheMLS = NULL) and (NULL is null or (p.Address1 LIKE NULL or p.Address2 LIKE NULL)) </code></pre> <p>Please note that the issue is only related to <code>IN</code> statement... When @ZipCodeIDS = '1,2,3' it should return 414 results (plain sql ok) but T-SQL function returns 80..</p> <p><strong>The strange thing I've noticed is that T-SQL only takes in consideration FIRST id from @ZipCodeIDS</strong> (as you see I split these ids and put them into a temp table). So here's the issue - about this first id... (cannot confirm that this is the only issue, because there were times when for the first zipCodeId it shouldn't return anything but it still returned results)</p> <p>Can you give a helping hand please? Ps: for my plain sql query, I've used a real table with those ids, just to mimic the behavior as much as possible...</p> <p><strong>UPDATE</strong>: The splitting of the @ZipCodeIDS and insertion into the temp table works perfectly: <a href="http://data.stackexchange.com/stackoverflow/q/109406/" rel="nofollow">http://data.stackexchange.com/stackoverflow/q/109406/</a></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USCristian Boariu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExtremely ugly query issue
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POExtremely ugly query issue
      1. This table or related slice is empty.
      1. VTDownMod
    1. COdeclare a table var and then delete from it: why?
      singulars
      1. POExtremely ugly query issue
      1. USMitch Wheat
    2. COSorry, that was a test to make sure the table is empty... trust me, that this is very ugly so I've tried to make sure for some other things also even if these are OK in 'real life'
      singulars
      1. POExtremely ugly query issue
      1. USCristian Boariu
    3. CONo argument about the ugly...But your statement is false; you don't need to code for impossibles.
      singulars
      1. POExtremely ugly query issue
      1. USMitch Wheat
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload