why parameter inclusion causes less records 1

[codecomm]

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

 

[xlbo]

Your WHERE clause needs to be in GRPLDR does it not ?

otehrwise you do the join, return all records associated and then filter out anything where GRPLDR.GLPerson LIKE @GL + '%') is not true

SELECT field1, field2, GRPLDR.GLPerson
FROM tblMain LEFT OUTER JOIN
(SELECT Person AS GLPerson, WPIdentifier
FROM SigAuth
WHERE (Title = 'Specific Value'
AND GLPerson LIKE @GL + '%')

) AS GRPLDR ON tblMain.WPIdentifier = GRPLDR.WPIdentifier

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[codecomm]

xlbo,

Thanks for the reply!

If I do that:
FROM tblMain LEFT OUTER JOIN
(SELECT Person AS GLPerson, WPIdentifier
FROM SigAuth
WHERE (Title = 'Specific Value'
AND GLPerson LIKE @GL + '%')

) AS GRPLDR ON tblMain.WPIdentifier = GRPLDR.WPIdentifier

, and say enter "Clinton" for the parameter, I get the Clinton data, but the NULLs as well. Since the report pulls back budget, and a person may only want to see the Clinton data, they'll get the NULLs too, and a much higher budget value.

 

[xlbo]

sorry - thought that was what you wanted as you were saying it doesn't bring back enough data with the WHERE clause where it was...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AlexCuse]

All you are missing is a where clause to filter out nulls on the right side of your join. Something like this (at the very bottom of your query)

where GRPLDR.WPIdentifier is null

Hope this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[codecomm]

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

 

[AlexCuse]

You could do this in one query:

[COLOR=blue]SELECT[/color]     fields [COLOR=blue]FROM[/color]  tblmain [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color]
([COLOR=blue]SELECT[/color]     Person [COLOR=blue]AS[/color] GLPerson, WPID [COLOR=blue]FROM[/color]  SigAuth [COLOR=blue]WHERE[/color] Title = [COLOR=red]'GL'[/color]) GRPLDR
 [COLOR=blue]ON[/color] tblmain.WPID = GRPLDR.WPID
[COLOR=blue]WHERE[/color]    (@GroupLeader = [COLOR=red]''[/color] or GLPerson LIKE @GroupLeader + [COLOR=red]'%'[/color])

When dealing with queries or sp's that expect parameters, that style of where clause will save you a lot of if's

where (@param is null or SomeCol = @param)

Hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom