January 23, 2019 at 1:40 pm
WHERE
(B.ELIGIBILITY_FLG <>'Y' or B.ELIGIBILITY_FLG is NULL)
and DM.ELIGIBILITY_FLG <>'Y'
B is Sol Tracker and DM is Duty Manager
I want to see all rows where B is not Y or B is Null.
But I don’t want to see any rows where DM is a Y
Thoughts? thanks
January 23, 2019 at 1:50 pm
jeffshelix - Wednesday, January 23, 2019 1:40 PMWHERE
(B.ELIGIBILITY_FLG <>'Y' or B.ELIGIBILITY_FLG is NULL)
and DM.ELIGIBILITY_FLG <>'Y'
B is Sol Tracker and DM is Duty Manager
I want to see all rows where B is not Y or B is Null.
But I don’t want to see any rows where DM is a Y
Thoughts? thanks
Should work. What are you seeing?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 23, 2019 at 1:52 pm
thanks, I lose the NULL lines in B. I only get the N.
(B is either Y, N or Null) I don't want to see any of the Y's)
Jeff
January 23, 2019 at 2:00 pm
jeffshelix - Wednesday, January 23, 2019 1:52 PMthanks, I lose the NULL lines in B. I only get the N.(B is either Y, N or Null) I don't want to see any of the Y's)
Jeff
What are the possible values in the other table DM.ELIGIBILITY_FLG when those NULL values show up in B?
It's possible those are perfectly lining up.
January 23, 2019 at 2:03 pm
ZZartin - Wednesday, January 23, 2019 2:00 PMjeffshelix - Wednesday, January 23, 2019 1:52 PMthanks, I lose the NULL lines in B. I only get the N.(B is either Y, N or Null) I don't want to see any of the Y's)
Jeff
What are the possible values in the other table DM.ELIGIBILITY_FLG when those NULL values show up in B?
It's possible those are perfectly lining up.
Have you tried
and isnull(DM.ELIGIBILITY_FLG,'N') <>'Y'
?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 23, 2019 at 2:09 pm
You are joining two tables together some how but you haven't shown us the rest of the query.
January 23, 2019 at 2:14 pm
DM table could be NULL, Y or N, independent of B.
But if DM is a Y, i don't care what B is. and Vice Versa.
I only want rows where B is not a Y AND B is not a y.
but i like your idea of ISNULL...then N. that seemed to help
I get A LOT OF rows back, but that is on my side to see if the code is right.
thanks
January 23, 2019 at 2:18 pm
jeffshelix - Wednesday, January 23, 2019 2:14 PMDM table could be NULL, Y or N, independent of B.But if DM is a Y, i don't care what B is. and Vice Versa.
I only want rows where B is not a Y AND B is not a y.
but i like your idea of ISNULL...then N. that seemed to helpI get A LOT OF rows back, but that is on my side to see if the code is right.
thanks
WHERE NOT (DM = 'Y' AND B = 'Y')
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 23, 2019 at 2:31 pm
What if we work with some sample data.
CREATE TABLE SolTracker(
eligibility_flg char(1) NULL
);
CREATE TABLE DutyManager(
eligibility_flg char(1) NULL
);
INSERT INTO SolTracker VALUES('Y'),('N'),(NULL);
INSERT INTO DutyManager VALUES('Y'),('N'),(NULL);
SELECT *
FROM SolTracker B
CROSS JOIN DutyManager DM
WHERE (B.ELIGIBILITY_FLG <>'Y' OR B.ELIGIBILITY_FLG is NULL)
and (DM.ELIGIBILITY_FLG <> 'Y' OR DM.ELIGIBILITY_FLG is NULL)
GO
DROP TABLE SolTracker, DutyManager;
January 23, 2019 at 3:32 pm
Phil Parkin - Wednesday, January 23, 2019 2:18 PMjeffshelix - Wednesday, January 23, 2019 2:14 PMDM table could be NULL, Y or N, independent of B.But if DM is a Y, i don't care what B is. and Vice Versa.
I only want rows where B is not a Y AND B is not a y.
but i like your idea of ISNULL...then N. that seemed to helpI get A LOT OF rows back, but that is on my side to see if the code is right.
thanks
WHERE NOT (DM = 'Y' AND B = 'Y')
This won't work - it will return rows where either one of the values is 'Y' and the other is 'N'. Switching to an OR removes the NULL values...
I think Luis has the solution - which could be rewritten as:
WHERE coalesce(DM, 'N') <> 'Y'
AND coalesce(B, 'N') <> 'Y'
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 24, 2019 at 10:29 am
Jeffrey Williams 3188 - Wednesday, January 23, 2019 3:32 PMPhil Parkin - Wednesday, January 23, 2019 2:18 PMjeffshelix - Wednesday, January 23, 2019 2:14 PMDM table could be NULL, Y or N, independent of B.But if DM is a Y, i don't care what B is. and Vice Versa.
I only want rows where B is not a Y AND B is not a y.
but i like your idea of ISNULL...then N. that seemed to helpI get A LOT OF rows back, but that is on my side to see if the code is right.
thanks
WHERE NOT (DM = 'Y' AND B = 'Y')
This won't work - it will return rows where either one of the values is 'Y' and the other is 'N'. Switching to an OR removes the NULL values...
I think Luis has the solution - which could be rewritten as:
WHERE coalesce(DM, 'N') <> 'Y'
AND coalesce(B, 'N') <> 'Y'
My query was a direct response to this requirement from the OP:
I only want rows where B is not a Y AND B is not a y.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply