AND Logic help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

AND Logic help

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-06-05 : 16:17:31

Not sure why I can't figure this out, need some help understanding this logic.

I have three status fields for each base_id.. They can be any combination of the values in my insert below.

If the value is null, I need the select to output a '?'.. and if all three status fields are 'C', I don't want the row selected. Here is some data to work with.


CREATE TABLE #TEST (P_STATUS VARCHAR(1), J_STATUS VARCHAR(1), D_STATUS VARCHAR(1), BASE_ID VARCHAR(10))

INSERT INTO #TEST SELECT 'C','C','C','E-1000'
INSERT INTO #TEST SELECT 'C','R','R','E-1001'
INSERT INTO #TEST SELECT 'C','C','R','E-1002'
INSERT INTO #TEST SELECT 'R','C','C','E-2000'
INSERT INTO #TEST SELECT 'C','?','C','E-2001'
INSERT INTO #TEST SELECT 'R', NULL,'U','E-2002'
INSERT INTO #TEST SELECT 'C','F','F','E-2003'
INSERT INTO #TEST SELECT 'C','F','R','E-3000'
INSERT INTO #TEST SELECT 'R','C','C','E-3002'
INSERT INTO #TEST SELECT 'R','C','C','E-3009'
INSERT INTO #TEST SELECT 'C', NULL,'C','E-5000'
INSERT INTO #TEST SELECT 'C','F','C','E-6000'

SELECT 
* 
FROM #TEST
--WHERE
--	ISNULL(P_STATUS, '?') <> 'C' AND
--	ISNULL(J_STATUS, '?') <> 'C' AND	
--	ISNULL(D_STATUS, '?') <> 'C'

DROP TABLE #TEST

My expected output would be


C	R	R	E-1001
C	C	R	E-1002
R	C	C	E-2000
C	?	C	E-2001
R	?	U	E-2002
C	F	F	E-2003
C	F	R	E-3000
R	C	C	E-3002
R	C	C	E-3009
C	?	C	E-5000
C	F	C	E-6000

The only row dropped is E-1000.

Thanks for any help you're willing to throw down!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-05 : 16:36:46

You can do this in a couple of ways:


SELECT COALESCE(P_Status,'?'),  COALESCE(J_Status,'?'),  COALESCE(D_Status,'?'), BASE_ID 
FROM #TEST
WHERE NOT(P_STATUS = 'C' AND J_STATUS = 'C' AND D_STATUS = 'C')


SELECT COALESCE(P_Status,'?'),  COALESCE(J_Status,'?'),  COALESCE(D_Status,'?'), BASE_ID 
FROM #TEST
WHERE BASE_ID NOT IN(SELECT BASE_ID FROM #Test WHERE P_STATUS = 'C' AND J_STATUS = 'C' AND D_STATUS = 'C')

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-05 : 16:37:21

The second query is assuming BASE_ID is unique within the table.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-06-05 : 16:46:46

I'm pretty sure it will be unique yes. Thanks for the quick reply, I'll have to add COALESCE to my skillset!

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-06-05 : 17:02:31

Your first query drops E-5000 - if I do

WHERE NOT(ISNULL(P_STATUS, '?') = 'C' AND ISNULL(J_STATUS, '?') = 'C' AND ISNULL(D_STATUS, '?') = 'C')

is that safe?

Reason is, I'm not so sure the base_id will be 100% unique, I'm also selecting about 50 other fields in my real query

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-05 : 17:30:52

I thought you only want to eliminate rows that have all three status'es as 'C'. Why are you using ISNULL's in your WHERE?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-06 : 01:10:15

Yes. it is safe, if comparing and compared values are null,then comparison will result false

like NULL = NULL is false...because null is unknown value...

--------------------------------------------------
S.Ahamed

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-06-06 : 07:45:28

quote:
Originally posted by dinakar

I thought you only want to eliminate rows that have all three status'es as 'C'. Why are you using ISNULL's in your WHERE?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Yes status of C C C = I don't want to select it
but status of C NULL C = I want to select it.. so that someone can go can fix the status of that order or job to set it to closed

Without the ISNULL's, then both cases are not selected. Sorry if my first post wasn't clear on that!

I want to select it, and I also want it to read C ? C, our combined query does that now though :) thanks,

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 11:49:09

aight... looks like the query is working fine..
not sure what you mean by "safe"?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-06-06 : 13:51:30

quote:
Originally posted by dinakar

not sure what you mean by "safe"?

Oh I ment, "will that work all the time?", or "is that a good way of going about the problem?"

It seems to be working with all of my tests so far ! Thx for your help

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 13:56:14

I would think so. Assuming, this is a backend process and is not really expected to "perform well". It could be a little slow if the number of rows is into millions.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources