AnsweredAssumed Answered

Issue creating a report of checked out files in Private State and others

Question asked by Sandy Scott on Aug 15, 2018
Latest reply on Sep 19, 2018 by Andri Olafsson

I'm trying to create a report which shows the user which files they have checked out, and whether the file has ever been checked in (if not, that's in "Private State").

 

I have a query which works on SQL Server Management Studio (SSMS), but fails to identify the private state or otherwise of certain files when run in the report generator (RG).

 

(Demo with UserID hard coded in the RG version, only difference between the two is the "[SWPDM_XerosUSVault].[dbo]." to identify the tables in the SSMS version.)

 

SSMS:

SELECT

    D.lockpath as [PATH],

    D.filename as [FILE],

    D.LockDate as [CHECKOUT DATE],

    coalesce(PD.PDState,'public') AS [State]

    FROM [SWPDM_XerosUSVault].[dbo].Documents as D

    LEFT JOIN (

        SELECT

            D2.DocumentID,

            'Private State' as PDState

        FROM [SWPDM_XerosUSVault].[dbo].Documents as D2

        WHERE D2.CurrentStatusID = (select InitialStatusID from [SWPDM_XerosUSVault].[dbo].Users as U where U.UserID=24)

        ) AS PD on D.DocumentID = PD.DocumentID

WHERE

        D.[UserID] = 24

 

Results

SSMS results.PNG

 

RG:

SELECT

    D.lockpath as [PATH],

    D.filename as [FILE],

    D.LockDate as [CHECKOUT DATE],

    coalesce(PD.PDState,'public') AS [State]

    FROM Documents as D

    LEFT JOIN (

        SELECT

            D2.DocumentID,

            'Private State' as PDState

        FROM Documents as D2

        WHERE

            D2.CurrentStatusID = (select InitialStatusID from Users as U where U.UserID=24)

        ) AS PD on D.DocumentID = PD.DocumentID

WHERE

        D.[UserID] = 24

RG results.PNG

 

The SSMS version is giving the correct results - only a few of the files are in a Private State. There's something going wrong which means the query isn't working properly for the report generator, possibly an issue with subquerys and NULLs, but I wouldn't have thought it would make any difference which system was passing the query to the database.

 

Any suggestions for how I can fix this? I really want to keep it in 1 query.

Outcomes