Nested Or and AND conditions for the same column

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

General SQL Server Forums

New to SQL Server Programming

Nested Or and AND conditions for the same column

Author

Topic

twynsys
Starting Member

2 Posts

Posted - 2007-12-06 : 10:14:50

Hello,

I would like some assistance with some code that I am attempting to write. I have one column (Mgr1FullName) that contains different values. I want it to show me all the people who has a manager name as Unavailable, Null, or blank.

Example Code:
--------------------------------------------------------------------
select uuid_hdr.id,
uuid_hdr.bizrightid,
uuid_hdr.fullname,
uuid_hdr.mgr1fullname,
uuid_dtl.role,
uuid_dtl.application,
uuid_dtl.dateremoved,
uuid_dtl.userid

from uuid_dtl
inner join uuid_hdr
on uuid_dtl.hdr_id = uuid_hdr.id
where uuid_dtl.dateremoved is Null and
uuid_dtl.application = 'SAPRET'and
uuid_hdr.mgr1fullname = 'Unavailable'or
uuid_hdr.mgr1fullname is Null or
uuid_hdr.mgr1fullname = ' ' and
uuid_dtl.userid not like 'prodsup%' and
uuid_hdr.bizrightid not like 'prodsup%' and
uuid_dtl.role not like 'z:m_genuser_vendor'
-------------------------------------------------------------------

Should I use ORs or ANDs? I will face this same problem with the ROLE column.

Thanks for all and any help.

Twyn

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-12-06 : 10:19:29

use OR, but include some brackets to seperate the logic from your other 'where' criteria

quote:

from uuid_dtl
inner join uuid_hdr
on uuid_dtl.hdr_id = uuid_hdr.id
where uuid_dtl.dateremoved is Null and
uuid_dtl.application = 'SAPRET'and
(
uuid_hdr.mgr1fullname = 'Unavailable'or
uuid_hdr.mgr1fullname is Null or
uuid_hdr.mgr1fullname = ' '
)
and
uuid_dtl.userid not like 'prodsup%' and
uuid_hdr.bizrightid not like 'prodsup%' and
etc...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 10:24:38

Also learn to make use of table aliases.

SELECT		h.ID,
		h.BizRightID,
		h.FullName,
		h.Mgr1FullName,
		d.Role,
		d.Application,
		d.DateRemoved,
		d.UserID
FROM		uuid_dtl AS d
INNER JOIN	uuid_hdr AS h ON h.ID = d.Hdr_ID
WHERE		d.DateRemoved IS NULL
		AND d.Application = 'SAPRET'
		AND COALESCE(h.Mgr1FullName, ' ') IN ('Unavailable', ' ')
		AND d.UserID NOT LIKE 'prodsup%'
		AND h.BizRightID NOT LIKE 'prodsup%'
		and d.Role NOT LIKE 'z:m_genuser_vendor'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources