why parameter inclusion causes less records

Question

why parameter inclusion causes less records

sol

SSCrazy

Points: 2294
More actions
July 31, 2007 at 3:06 pm

#179926

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 + '%')

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Because NULL is not like @GL + '%'. You are now filtering your result. If you what all values to meet your filter or where GRPLDR.GLPerson is null, you need to change your filter to this:

WHERE (GRPLDR.GLPerson LIKE @GL + '%') OR (GRPLDR.GLPerson IS NULL)

sol SSCrazy Points: 2294 More actions · Answer 2

Thanks for the reply!

If I do:

WHERE (GRPLDR.GLPerson LIKE @GL + '%') OR (GRPLDR.GLPerson IS NULL)

....then if I run the report for let's say "Clinton" for the person's last name, it will pull in the Clinton data, but also the values that are null.

This will bring back incorrect currency values, since the null shouldn't be included.

I'm not sure how to replicate the Crystal Reports XI option where "NULL is default value"

sol SSCrazy Points: 2294 More actions · Answer 3

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 + '%';

Ray M SSC-Insane Points: 21093 More actions · Answer 4

Then is not needed in a IF statement Remove Text in Red

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 + '%';

sol SSCrazy Points: 2294 More actions · Answer 5

Thanks Ray!

Yeah, that was what 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 + '%;

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 6

Try this:

SELECT tblMain.field1, tblMain.field2, GRPLDR.GLPerson

FROM tblMain

LEFT JOIN (SELECT Person AS GLPerson, WPIdentifier

FROM SigAuth WHERE Title = 'Specific Value') AS GRPLDR

ON tblMain.WPIdentifier = GRPLDR.WPIdentifier

AND GRPLDR.GLPerson LIKE @GL+'%'

Andy