5 Replies Latest reply on Nov 2, 2011 10:46 AM by Joy Garon

    How to display text status instead of CurrentStatusID

    Walter Bednarz

      I have this SQL query:

        

      select Filename,CurrentStatusID

      from Documents

      where

      Filename like 'SUM-00%.xml'

      and

      Deleted=0

       

      The results look like this:

      Microsoft SQL Server Management Studio_2011-11-01_13-44-45.jpg

       

      How do I display the text value of the status instead of the numeric id?

        • Re: How to display text status instead of CurrentStatusID
          Wayne Matus

          The status name is in the status table so you have to join the two tables.

           

          SELECT Documents.Filename, Status.Name

          FROM Documents CROSS JOIN Status

          WHERE Filename like 'SUM-004.xml'

          And

          Deleted = 0

            • Re: How to display text status instead of CurrentStatusID
              Walter Bednarz

              Thanks Wayne. That was close enough to get my guided in the right direction. I made some modifications and this seemed to work:

               

              select Documents.Filename as Project,Status.Name as Status

              from Documents

              left join Status

              on Documents.CurrentStatusID=Status.StatusID

              where

              WorkflowID=7

              order by Documents.CurrentStatusID

              • Re: How to display text status instead of CurrentStatusID
                Joy Garon

                Hi Wayne,

                 

                Actually, you don't want to use a cross join here (or in most cases).

                I would suggest using 'Design Query in Editor' and let the system create the joins for you automatically.

                The Documents table contains the current status of the file in the CurrentStatusID column (which is a number).

                This needs to be joined to the StatusID column in the Status table so that you can get the associated Name.

                See attached video to see how it's done.

                 

                Cheers,

                Joy

                  • Re: How to display text status instead of CurrentStatusID
                    Wayne Matus

                    Thank you Joy.

                     

                    I did use Design Query in Editor. I just did not notice that it did not automatically join the two table. I had used it before where two column names matched so it automatically created the INNER JOIN. I should have looked closer at my solution before I posted.

                     

                    Your video did teach me an easier way to create the INNER JOIN. I would just key it in.

                     

                    Thanks,

                    Wayne

                      • Re: How to display text status instead of CurrentStatusID
                        Joy Garon

                        Hi Wayne,

                         

                        Unless there are keys that tie the tables together you won't get a join automatically. Having said that, you also have to be careful because sometimes you get joins that you do not want to use.

                        For example: Let's say you want a report that lists the following for all the documents in the vault:

                        • filename
                        • folder path
                        • current document status (name)

                         

                        This requires the use of 4 tables:

                        • Documents (contains the filename)
                        • Projects (contains the folder path)
                        • DocumentsInProjects (contains the information detailing which documents are in which folders (projects))
                        • Status (contains the status name)

                         

                        When you add the tables you will get a join between the Documents and the Projects table that is not needed (Projects.ProjectID to Document.LockProject) and also between the Projects table and Status table (Projects.StatusID to Status.StatusID). Simply delete the joins you do not need and manually create the required joins.

                         

                        See attached video.

                         

                        Regards,

                        Joy