1 Reply Latest reply on Oct 3, 2014 4:30 PM by Tim Webb

    Reporting from EPDM using SSRS, SQL query missing records

    Jeremy Ferguson

      Hi

      Within our EPDM system there are three folders in the folder are virtual document that are the users requests for new project folders to be set up.

      Management would like a report listing all project request and there current state. So I did the following?

      Select from “Document In Projects” all document ID where project ID matches one of the three projects.

      Inner Join “Document In Projects” and “Documents” to get the “Current Status ID”

      Inner Join “Status” and “Documents” to get the name of the workflow state.

      At this point all was going will, I then did a number of Inner Join statement on the “Variable Value” table to get the data held on the file card, this is where the problem started on a number of the cards not all of the fields had been filled in when this was the case the document would not show up in return record set.

      I then looked at a number of right join and left join with not effect, as I am self-taught in SQL but as I understand it if a record existed in the “Documents” table but not in the “Variable Value” table then it would have returned a value.

      Any help in fixing this or in improving the overall code would be welcome

      I have attached the full SQL