why parameter inclusion causes less records

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

  • 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)

  • 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"

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

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

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

  • 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

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

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