June 13, 2019 at 12:22 am
Hi all,
I am attempting to return only values that do not exist by comparing a view to a table.
SELECT
*
FROM V_MyView Q
WHERE NOT EXISTS (
SELECT 1
FROM Tbl_MyTable T
WHERE
T.[CUstomerID] = Q.ID AND
T.[ActivityID] = 58 AND
T.[RelatedReferenceID] = Q.ReferenceID AND
-- T.[RelatedReferenceType] = Q.ArrangementType AND -- Does not work
T.[RelatedReferenceType] = 'TypeA' OR T.[RelatedReferenceType] = 'TypeB' AND -- This works instead
T.[WorkItemType] = 'Account' AND
CONVERT(VARCHAR(10), T.[Due Date], 111) = CONVERT(VARCHAR(10), Q.Enddate, 111) -- Does not work
)
The query runs, but it's always returning the wrong result. It should return 0, or possibly 5 records at most, but it's returning results like 300-400 results.
I don't understand why I need to physically specify the string and can't use the name
-- T.[RelatedReferenceType] = Q.ArrangementType AND -- Does not work
T.[RelatedReferenceType] = 'TypeA' OR T.[RelatedReferenceType] = 'TypeB' AND -- This works instead
Also, this is causing a lot of problems as well. I made the mistake of building the field in the table as a Datatime, but now I can't change it because many views and SP's rely on it. And using the regular field name is not working either.
CONVERT(VARCHAR(10), T.[Due Date], 111) = CONVERT(VARCHAR(10), Q.Enddate, 111) -- Does not work
I've tried using Cast, but no joy there.
In fact, testing every line to find the fault has show the query does the complete opposite of what I want.
Any help appreciated.
thanks
June 13, 2019 at 10:39 am
I can't say why T.[RelatedReferenceType] = Q.ArrangementType AND does not work for you because, I know nothing about your data, tables and view.
However, I can answer why
T.[RelatedReferenceType] = 'TypeA' OR T.[RelatedReferenceType] = 'TypeB'
gives a different result. This is because now you are radically changing the logic. AND binds tighter than OR, so you have something quite different. Permit me to rearrange the WHERE clause to clarify:
WHERE T.[CUstomerID] = Q.ID
AND T.[ActivityID] = 58
AND T.[RelatedReferenceID] = Q.ReferenceID
AND T.[RelatedReferenceType] = 'TypeA'
OR
T.[RelatedReferenceType] = 'TypeB'
AND T.[WorkItemType] = 'Account'
AND CONVERT(VARCHAR(10), T.[Due Date], 111) = CONVERT(VARCHAR(10), Q.Enddate, 111)
You would need to have parentheses:
(T.[RelatedReferenceType] = 'TypeA' OR T.[RelatedReferenceType] = 'TypeB')
To get something resemblent of the original.
When it comes to the date, my recommendation is that you change the columns to be date instead. Yeah, this means that you will have to go back and change things, but if you don't not fix this, you will only be digging deeper and deeper into this hole.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 13, 2019 at 2:01 pm
Please be more specific on "Does not work" for Q.ArrangementType. In general you should have no problem referencing columns in the view in a NOT EXISTS, so some other error is happening. Most likely the column name changed in the view from what it normally was in the original tables used to create the view(?).
As to the datetime, don't convert the datatime in the lookup table, check for a range of datetime instead. That's important because it makes the [Due Date] directly searchable for by SQL ("sargable"), but if you perform any function on the column, the direct search possibility is destroyed.
SELECT
*
FROM V_MyView Q
WHERE NOT EXISTS (
SELECT 1
FROM Tbl_MyTable T
WHERE
T.[CUstomerID] = Q.ID AND
T.[ActivityID] = 58 AND
T.[RelatedReferenceID] = Q.ReferenceID AND
-- T.[RelatedReferenceType] = Q.ArrangementType AND -- Does not work
T.[RelatedReferenceType] IN ('TypeA', 'TypeB') AND -- This works instead /*Changed!*/
T.[WorkItemType] = 'Account' AND
T.[Due Date] >= CAST(Q.Enddate AS date) AND /*Changed!*/
T.[Due Date] < DATEADD(DAY, 1, CAST(Q.Enddate AS date)) /*Changed!*/
)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 17, 2019 at 12:05 am
Hi all, And thanks so far for all your help. It is much appreciated.
However, nothing I try works to produce a result. Clearly the issue lies in my not understanding how this is supposed to work.
SELECT
T.[CustomerID],
T.[RelatedReferenceID],
T.[RelatedReferenceType],
T.[Due Date]
FROM [MyDB].[dbo].Tbl_MyTable T
Where
T.[CustomerID] = 12345
AND T.[RelatedReferenceID] = 67891
The above works and produces a single record.
SELECT
V.[ID],
V.[ReferenceID],
V.[ReferenceType],
V.[Enddate],
V.[Client Name]
FROM [MyDB].[dbo].[V_MyView] V
Where
V.[ID] = 12345
AND V.[ArrangementID] = 67891
The above works to produce Nothing
SELECT
T.[CustomerID],
T.[RelatedReferenceID],
T.[RelatedReferenceType],
T.[Due Date]
FROM [MyDB].[dbo].Tbl_MyTable T
WHERE Not EXISTS (
SELECT
V.[ID],
V.[ReferenceID],
V.[ReferenceType],
V.[Enddate],
FROM [MyDB].[dbo].[V_MyView] V
WHERE
V.[ID] = T.[CustomerID]
AND V.[ReferenceID] = T.[RelatedReferenceID]
AND V.[ReferenceType] = T.[RelatedReferenceType]
AND V.[Enddate] = T.[Due Date]
)
AND T.[ClientId] = 12345
AND T.[RelatedReferenceID] = 67891
The above even works to produce the expected criteria.
But that is no good! I need it to produce the result without the criteria. I need it to find the result.
There should only be one record produced. But Instead I'm getting hundreds.
What is the correct way to do this?
Thanks again.
June 17, 2019 at 11:13 am
The correct way is to explain your problem in a way so that outsiders can understand it. I am sorry, but I am not able to understand what you are trying to achieve. And nor do I know your table or your view.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 17, 2019 at 12:52 pm
-- Work with what you can see.
-- Compare the columns from both sides, add to the matching columns list when you've figured out what's going on.
SELECT
T.[CustomerID],
T.[RelatedReferenceID],
T.[RelatedReferenceType],
T.[Due Date],
'#' '#', -- columns to the left of this are from T, columns to the right are from v.
v.*
FROM [MyDB].[dbo].Tbl_MyTable T
LEFT JOIN [MyDB].[dbo].[V_MyView] V
ON V.[ID] = T.[CustomerID] -- 12345
AND V.[ArrangementID] = T.[RelatedReferenceID] -- 67891
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 17, 2019 at 8:04 pm
Try this one
SELECT
V.[ID],
V.[ReferenceID],
V.[ReferenceType],
V.[Enddate]
FROM [MyDB].[dbo].[V_MyView] V
except
SELECT
T.[CustomerID],
T.[RelatedReferenceID],
T.[RelatedReferenceType],
T.[Due Date]
FROM [MyDB].[dbo].Tbl_MyTable T
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 18, 2019 at 12:50 am
Matt - Thanks for this tip. It appears to offer similar results to alternative methods and is a lot simpler to use.
I'm curious about how to add additional information into the query not included in the Except.
It throw an error if I add additional feilds.
So i want to see the exception, but also additional feilds in the View. Is that possible?
ChrisM@Work - I gave your solution a bit of a whirl and it seems awesome. Except, how can I show where one table does not contain a match? I.e NULL | NULL | NULL, etc...
Cheers
June 19, 2019 at 2:37 pm
The except syntax direct compares matching sets of columns. That said - the output of the EXCEPT is a recordset, so you could make it a subquery then join back into your table to pull the extract columns. As with other sub-query techniques, that can start to consue the optimizer so use it sparingly or pop it into a temp table if performance starts to suffer.
Example
select Tbl_MyTable.extracolumns,
mismatches. *
from (
SELECT
V.[ID],
V.[ReferenceID],
V.[ReferenceType],
V.[Enddate]
FROM [MyDB].[dbo].[V_MyView] V
except
SELECT
T.[CustomerID],
T.[RelatedReferenceID],
T.[RelatedReferenceType],
T.[Due Date]
FROM [MyDB].[dbo].Tbl_MyTable T) mismatches
join Tbl_MyTable on mismatches.id=Tbl_MyTable.id
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 19, 2019 at 10:56 pm
Matt - Thanks you for this. It's brilliant. It's a nice and simple solution!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply