SQL Query weird result
-
Ahoi,
i have a subquery that my predecessor made and i honestly don't understand why it produces the result it does.
The query is excluding certain user defined combinations which are not relevant. My problem is it filters something it should not filter in my opinion
SELECT b.SlpCode,b.Country_code
FROM [DSA].[FACT_B1_UMS] as a left outer join
dsa.MD_B1_Kunde as b on a.[U_GUE_CardCode] = b.CardCode
and a.Country_code = b.Country_code
where not (a.U_GUE_CardCode in ('10507','10787') and a.Country_Code = 'TR1')
and not (a.U_GUE_SlpCode = '18' and a.Country_code = 'FR1')
and not (b.SlpCode = '18' and b.Country_code = 'FR1')
and not (a.U_GUE_CardCode in ('20011') and a.Country_code = 'TR2')
and not (a.U_GUE_ItemCode = ' ' and a.Country_code = 'SE1')
and not (b.SlpCode in ('-1','4','6') and b.Country_code = 'BE1')
and not (a.U_GUE_SlpCode in ('-1','4','6') and a.Country_code = 'BE1')
and not (a.U_GUE_CardCode in ('9981500') and a.Country_code = 'JP1')
and not (a.U_GUE_CardCode = '1' and a.Country_code = 'ES1')Now the issue i encounter is the following, there was a value missing for a.U_GUE_DocNum = '910016396'
So i was trying to find the reason why it is not working and found the where lines which were responsible for the rows to be missing.
It is the following (commented).
SELECT b.SlpCode,b.Country_code
FROM [DSA].[FACT_B1_UMS] as a left outer join
dsa.MD_B1_Kunde as b on a.[U_GUE_CardCode] = b.CardCode
and a.Country_code = b.Country_code
where not (a.U_GUE_CardCode in ('10507','10787') and a.Country_Code = 'TR1')
and not (a.U_GUE_SlpCode = '18' and a.Country_code = 'FR1')
--and not (b.SlpCode = '18' and b.Country_code = 'FR1')
and not (a.U_GUE_CardCode in ('20011') and a.Country_code = 'TR2')
and not (a.U_GUE_ItemCode = ' ' and a.Country_code = 'SE1')
--and not (b.SlpCode in ('-1','4','6') and b.Country_code = 'BE1')
---
and not (a.U_GUE_SlpCode in ('-1','4','6') and a.Country_code = 'BE1')
and not (a.U_GUE_CardCode in ('9981500') and a.Country_code = 'JP1')
and not (a.U_GUE_CardCode = '1' and a.Country_code = 'ES1')
and a.U_GUE_DocNum = '910016396'What i dont understand is: the checks in these two lines both refere both to the same column in the same table, but the results without these 2 lines are all NULL.
Why do these 2 lines that are commented in the exclude the rows i am looking for although the value is not does not check for NULL.
EDIT: one of the 2 "and not" is enough for all of the rows to be excluded
- This topic was modified 4 years, 12 months ago by ktflash.
I want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was -
I may be missing the point but...
I notice that your addition to the WHERE clause just targets the left table. You are LEFT OUTER JOINing to the right table, but then only returning columns from the right table. If this is indeed a subquery it does not make sense to me.
Is it possible that it is matching rows from the left table where no match to the right table exists, which is causing the data returned to be NULL?
-
FUCK MY LIFE
- Reason it does not work is the following: This is by SQL (Server) design. NULL is not equal (or unequal) to anything. (good to know)
- Business reason why it did not work, the a.[U_GUE_CardCode] from the the data source was not defined which lead to NULL which lead to 1
I want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was -
Just remember NULL does not evaluate - but try and avoid doing ISNULL(myfield,'')
you end up checking for blank spaces and NULL everywhere.
I've found that default values can help SQL a lot - even if you have to put a value that is "unspecified"
a typical scenario is "gender" - (i'll simplify by not using transgender) - but you can choose male, female or leave it blank - I default to 0 (unspecified) or let them choose 1 or 2 (M/F)
it really avoids ugly queries where null is involved
MVDBA
-
He is right that NULL doesn't evaluate to TRUE, which might be part of your problem. I think the larger problem is the way your JOIN is set up. I'm guessing a little bit here, but the LEFT JOIN looks like an odd choice, given that you don't want anything from the "a" table; you are just filtering by it. Maybe try something more like this below.
SELECT b.SlpCode,b.Country_code
FROM dsa.MD_B1_Kunde as b
WHERE NOT (b.SlpCode = '18' and b.Country_code = 'FR1')
AND NOT (b.SlpCode in ('-1','4','6') and b.Country_code = 'BE1')
AND (b.Country_code NOT IN(a.U_GUE_CardCode in ('10507','10787') and a.Country_Code = 'TR1')
AND NOT (
SELECT a.Country_code
FROM [DSA].[FACT_B1_UMS] a
WHERE (a.U_GUE_SlpCode = '18' and a.Country_code = 'FR1')
or (a.U_GUE_CardCode in ('20011') and a.Country_code = 'TR2')
or (a.U_GUE_ItemCode = ' ' and a.Country_code = 'SE1')
or (a.U_GUE_SlpCode in ('-1','4','6') and a.Country_code = 'BE1')
or (a.U_GUE_CardCode in ('9981500') and a.Country_code = 'JP1')
or (a.U_GUE_CardCode = '1' and a.Country_code = 'ES1')
)
Steven Henderson
The SQL Guy -
Those of us who follow ISO standards use 0=unknown, 1= male, 2= female, 9= lawful person (churches, corporations, etc.) And make the column not null.
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 wrote:
Those of us who follow ISO standards use 0=unknown, 1= male, 2= female, 9= lawful person (churches, corporations, etc.) And make the column not null.
do you have a link to that ISO standard? it might be interesting reading - especially since there are so many new categories of things we collect data about - sexuality, gender, etc
MVDBA
- jcelko212 32090 wrote:
Those of us who follow ISO standards use 0=unknown, 1= male, 2= female, 9= lawful person (churches, corporations, etc.) And make the column not null.
and i'm laughing now because it used to be a bit field called "is_male"
MVDBA
-
https://en.wikipedia.org/wiki/ISO/IEC_5218
Please post DDL and follow ANSI/ISO standards when asking for help.
-
well I've been accidentally getting it correct for the last 20 years
MVDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply