Where clause - 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

General SQL Server Forums

New to SQL Server Programming

Where clause

Author

Topic

Magua1
Starting Member

15 Posts

Posted - 2014-08-14 : 09:36:55

Can anyone explain why this does not work:
I have
CASE WHEN SED_JA1A IS NOT NULL THEN 1 ELSE 0 END = 1
in the where clause but it does not return any records even though there are lots of instances where SED_JA1A contains characters

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 10:02:02

Do you mean something like:


SELECT ...
FROM ...
WHERE CASE WHEN SED_JA1A IS NOT NULL THEN 1 ELSE 0 END = 1

Please post the rest of your query and some of the data.

Magua1
Starting Member

15 Posts

Posted - 2014-08-14 : 10:26:35

Ok here's the query
SELECT DISTINCT
'2013/4' AS Report_Year,
'5036' AS Report_ID,
isnull(sed_scjc,'') AS Student_Code,
isnull(sed_sces,'') AS SCE_Sequence,
'AllACT (SED_ALLx) contains duplicates' AS field_value

FROM
staging..srs_sed_dqi_test
WHERE

-- Records with > 1 entry
CASE WHEN SED_JA1A IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN SED_JA1B IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN SED_JA1C IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN SED_JA2A IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN SED_JA2B IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN SED_JA2C IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN SED_JA3A IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN SED_JA3B IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN SED_JA3C IS NOT NULL THEN 1 ELSE 0 END > 1
AND
-- Determine whether there is a duplicate
SED_JA1A IN( ISNULL(SED_JA1B,''),ISNULL(SED_JA1C,''),
ISNULL(SED_JA2A,''),ISNULL(SED_JA2B,''),ISNULL(SED_JA2C,''),
ISNULL(SED_JA3A,''),ISNULL(SED_JA3B,''),ISNULL(SED_JA3C,''))
OR
SED_JA1B IN(ISNULL(SED_JA1A,''), ISNULL(SED_JA1C,''),
ISNULL(SED_JA2A,''),ISNULL(SED_JA2B,''),ISNULL(SED_JA2C,''),
ISNULL(SED_JA3A,''),ISNULL(SED_JA3B,''),ISNULL(SED_JA3C,''))
OR
SED_JA1C IN(ISNULL(SED_JA1A,''),ISNULL(SED_JA1B,''),
ISNULL(SED_JA2A,''),ISNULL(SED_JA2B,''),ISNULL(SED_JA2C,''),
ISNULL(SED_JA3A,''),ISNULL(SED_JA3B,''),ISNULL(SED_JA3C,''))
OR
SED_JA2A IN(ISNULL(SED_JA1A,''),ISNULL(SED_JA1B,''),ISNULL(SED_JA1C,''),
ISNULL(SED_JA2B,''),ISNULL(SED_JA2C,''),
ISNULL(SED_JA3A,''),ISNULL(SED_JA3B,''),ISNULL(SED_JA3C,''))
OR
SED_JA2B IN(ISNULL(SED_JA1A,''),ISNULL(SED_JA1B,''),ISNULL(SED_JA1C,''),
ISNULL(SED_JA2A,''), ISNULL(SED_JA2C,''),
ISNULL(SED_JA3A,''),ISNULL(SED_JA3B,''),ISNULL(SED_JA3C,''))
OR
SED_JA2C IN(ISNULL(SED_JA1A,''),ISNULL(SED_JA1B,''),ISNULL(SED_JA1C,''),
ISNULL(SED_JA2A,''),ISNULL(SED_JA2B,''),
ISNULL(SED_JA3A,''),ISNULL(SED_JA3B,''),ISNULL(SED_JA3C,''))
OR
SED_JA3A IN(ISNULL(SED_JA1A,''),ISNULL(SED_JA1B,''),ISNULL(SED_JA1C,''),
ISNULL(SED_JA2A,''),ISNULL(SED_JA2B,''),ISNULL(SED_JA2C,''),
ISNULL(SED_JA3B,''),ISNULL(SED_JA3C,''))
OR
SED_JA3B IN(ISNULL(SED_JA1A,''),ISNULL(SED_JA1B,''),ISNULL(SED_JA1C,''),
ISNULL(SED_JA2A,''),ISNULL(SED_JA2B,''),ISNULL(SED_JA2C,''),
ISNULL(SED_JA3A,''), ISNULL(SED_JA3C,''))
OR
SED_JA3C IN(ISNULL(SED_JA1A,''),ISNULL(SED_JA1B,''),ISNULL(SED_JA1C,''),
ISNULL(SED_JA2A,''),ISNULL(SED_JA2B,''),ISNULL(SED_JA2C,''),
ISNULL(SED_JA3A,''),ISNULL(SED_JA3B,''))

The table contains 9 fields JA1A-C, JA2A-C and JA3A-C.
For any particular record, none of the fields can have the same entry
except NULL.
What I want to do is find the number of fields that have non-null entries (per record.)
Where there is more than 1 entry, get the records where the value of the entry is duplicated in another field.

Any help appreciated
TIA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 10:35:28

So what happens if the first column (SED_JA1A) is NULL? The first line of your WHERE:


CASE WHEN SED_JA1A IS NOT NULL THEN 1 ELSE 0 END +

returns 0. but then in the second section we have:


AND 
-- Determine whether there is a duplicate
SED_JA1A IN( ISNULL(SED_JA1B,''),ISNULL(SED_JA1C,''),
ISNULL(SED_JA2A,''),ISNULL(SED_JA2B,''),ISNULL(SED_JA2C,''),
ISNULL(SED_JA3A,''),ISNULL(SED_JA3B,''),ISNULL(SED_JA3C,''))

If SED_JA1A is NULL, this expression will return FALSE (is that what you want?) Also, the OR'd expressions should probably be surrounded with parentheses. Otherwise it might not be evaluated the way you want.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 10:42:09

FWIW I just whipped up a small test case. Perhaps this will help you:


declare @t table (a int , b int, c int)
insert into @t (a, b, c) values
	(1,2,3),
	(1,1,2),
	(1,1,1),
	(null,2,3),
	(null,null,3),
	(null,null,null),
	(1,1,null)

select * from @t
where a is not null and a in (b,c)
   or b is not null and b in (a,c)
   or c is not null and c in (a,b)

yields:


a	b	c
1	1	2
1	1	1
1	1	NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-14 : 10:43:58

Something like this?

SELECT DISTINCT	'2013/4' AS Report_Year,
		'5036' AS Report_ID,
		ISNULL(sed_scjc, '') AS Student_Code,
		ISNULL(sed_sces, '') AS SCE_Sequence,
		'AllACT (SED_ALLx) contains duplicates' AS field_value	
FROM		staging..srs_sed_dqi_test AS x
CROSS APPLY	(
			SELECT	COUNT(d.code)
			FROM	(
					VALUES	(x.SED_JA1A),
						(x.SED_JA1B),
						(x.SED_JA1C),
						(x.SED_JA2A),
						(x.SED_JA2B),
						(x.SED_JA2C),
						(x.SED_JA3A),
						(x.SED_JA3B),
						(x.SED_JA3C)
				) AS d(code)
			HAVING	COUNT(d.code) = COUNT(DISTINCT d.code)
		) AS f(nonnullcolumns);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Magua1
Starting Member

15 Posts

Posted - 2014-08-14 : 10:51:38

Thanks for the quick replies but have to be somewhere else now. I will try your suggestions tomorrow.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 10:56:37

SwePeso:

I think you mean:


HAVING	COUNT(d.code) <> COUNT(DISTINCT d.code)

Otherwise, quite clever, though the execution plan sucks :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-14 : 11:36:16

gbritton, it depends if you want to read this line

quote:
For any particular record, none of the fields can have the same entry except NULL.

or this line

quote:
Where there is more than 1 entry, get the records where the value of the entry is duplicated in another field.

You can get a better plan if you get rid of the DISTINCT part and wrap a second GROUP BY on top of the existing.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 11:51:20

SwePeso,

I read both lines as the same, since NULL <> NULL

Magua1
Starting Member

15 Posts

Posted - 2014-08-15 : 04:54:31

Hi SwePeso
Copied your code into SSMS and getting Incorrect syntax near the keyword 'VALUES'
Should this work in SQL Server 2005?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-15 : 05:44:55

quote:
Originally posted by Magua1

Hi SwePeso
Copied your code into SSMS and getting Incorrect syntax near the keyword 'VALUES'
Should this work in SQL Server 2005?

That syntax will not work in 2005. We assume you are using current technologies when the version isn't mentioned in the topic...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-15 : 07:33:09

[code]SELECT DISTINCT '2013/4' AS Report_Year,
'5036' AS Report_ID,
ISNULL(sed_scjc, '') AS Student_Code,
ISNULL(sed_sces, '') AS SCE_Sequence,
'AllACT (SED_ALLx) contains duplicates' AS field_value
FROM staging..srs_sed_dqi_test AS x
CROSS APPLY (
SELECT COUNT(d.code)
FROM (
SELECT x.SED_JA1A AS Code UNION ALL
SELECT x.SED_JA1B UNION ALL
SELECT x.SED_JA1C UNION ALL
SELECT x.SED_JA2A UNION ALL
SELECT x.SED_JA2B UNION ALL
SELECT x.SED_JA2C UNION ALL
SELECT x.SED_JA3A UNION ALL
SELECT x.SED_JA3B UNION ALL
SELECT x.SED_JA3C
) AS d(code)
HAVING COUNT(d.code) = COUNT(DISTINCT d.code)
) AS f(NonNullColumnCount);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Magua1
Starting Member

15 Posts

Posted - 2014-08-15 : 07:56:02

Thank gbritton, I took your advise and modified the code to:
SED_JA1A IS NOT NULL AND
SED_JA1A IN(SED_JA1B,SED_JA1C,SED_JA2A,SED_JA2B,
SED_JA2C,SED_JA3A,SED_JA3B,SED_JA3C)
etc.

This does exactly what I need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources