why parameter inclusion causes less records

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

Development Tools

Reporting Services Development

why parameter inclusion causes less records

Author

Topic

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2007-07-31 : 17:07:26

My question is why does the inclusion of my parameter, @GL, cause my report to only show records where the LEFT OUTER JOIN to the GRPLDR table is NOT NULL from tblMain.

Basically, with the parameter in the report, I get 6 pages. Without the parameter, I get 10 pages and those records where the GRPLDR.GLPerson is null for the join.

I tried the exact same query is Crystal reports, and get exactly what I needed by the "NULL values set to default" option.

Here's my query:

SELECT field1, field2, GRPLDR.GLPerson
FROM tblMain LEFT OUTER JOIN
(

SELECT Person AS GLPerson, WPIdentifier
FROM SigAuth WHERE (Title = 'Specific Value')

)

AS GRPLDR ON tblMain.WPIdentifier = GRPLDR.WPIdentifier
WHERE (GRPLDR.GLPerson LIKE @GL + '%')

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2007-08-01 : 10:10:00

I have the following for my report but get an incorrect syntax near ELSE and THEN:

IF @GroupLeader = '' THEN

SELECT GRPLDR.GLPerson FROM dbo.Main LEFT OUTER JOIN
(SELECT Person AS GLPerson, WPId FROM SigAuth WHERE Title = 'GL') GRPLDR ON dbo.Main.WPID = GRPLDR.WPID;
ELSE
SELECT GRPLDR.GLPerson FROM dbo.Main LEFT OUTER JOIN
(SELECT Person AS GLPerson, WPId FROM SigAuth WHERE Title = 'GL') GRPLDR ON dbo.Main.WPID = GRPLDR.WPID
WHERE GLPerson LIKE @GroupLeader + '%';

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 10:15:52

[code]SELECT t.field1,
t.field2,
GRPLDR.GLPerson
FROM tblMain AS t
LEFT JOIN (
SELECT Person AS GLPerson,
WPIdentifier
FROM SigAuth
WHERE Title = 'Specific Value'
AND Person LIKE @GL + '%'
) AS GRPLDR ON GRPLDR.WPIdentifier = r.WPIdentifier[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 10:19:20

This

IF @GroupLeader = '' THEN 
	SELECT		GRPLDR.GLPerson
	FROM		dbo.Main
	LEFT JOIN	(
				SELECT	Person AS GLPerson,
					WPId
				FROM	SigAuth
				WHERE	Title = 'GL'
			) AS GRPLDR ON dbo.Main.WPID = GRPLDR.WPID
ELSE
	SELECT		GRPLDR.GLPerson
	FROM		dbo.Main
	LEFT JOIN	(
				SELECT	Person AS GLPerson,
					WPId
				FROM	SigAuth
				WHERE	Title = 'GL'
					AND Person LIKE @GroupLeader + '%'
			) AS GRPLDR ON dbo.Main.WPID = GRPLDR.WPID

But all yuo really need is this

SELECT		GRPLDR.GLPerson
FROM		dbo.Main
LEFT JOIN	(
			SELECT	Person AS GLPerson,
				WPId
			FROM	SigAuth
			WHERE	Title = 'GL'
				AND Person LIKE ISNULL(@GroupLeader, '') + '%'
		) AS GRPLDR ON dbo.Main.WPID = GRPLDR.WPID

because when @GroupLeader is empty string (or null) it will get all records anyway.

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

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2007-08-01 : 14:00:58

Here's the SQL I got to fix the issue:

IF @GroupLeader = ''

SELECT fields FROM tblmain LEFT OUTER JOIN
(SELECT Person AS GLPerson, WPID FROM SigAuth WHERE Title = 'GL') GRPLDR ON tblmain.WPID = GRPLDR.WPID

ELSE

SELECT fields FROM tblmain LEFT OUTER JOIN
(SELECT Person AS GLPerson, WPID FROM SigAuth WHERE Title = 'GL') GRPLDR ON tblmain.WPID = GRPLDR.WPID

WHERE GLPerson LIKE @GroupLeader + '%;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources